Ошибка 15517 ms sql как исправить

  • Remove From My Forums

  • Question

  • Hello,

    I have an .mdf file that I tried to attach in SSMS.  I am using (LocalDB)\MSSQLLocalDB (SQL Server 12.0.2569) The attach process seems to have completed successfully but when I want to make any changes at all I get the message:

    «Cannot execute as the database principal because the principal «dbo» does not exist, this type of principal cannot be impersonated, or you do not have permission (Microsoft SQL Server, Error: 15517)»

    I can see the database diagram in VS when I create and Entity Framework Model but I am unable to perform any functions in SSMS like being able to establish relationships and the like.

    I would appreciate some guidance on this.

    Thank you.

Answers

  • Is this what you are looking for?

    https://msdn.microsoft.com/en-us/library/ms178630.aspx

    • Marked as answer by

      Saturday, November 17, 2018 6:35 PM

    • Marked as answer by
      Geol4549
      Wednesday, January 24, 2018 3:47 PM
Источник

Newbie DBA here, just getting settled into my first DBA job and trying to figure out how things work outside of a classroom environment. Your patience is appreciated.

I’m trying to create diagrams for all the databases so I can try and get a grasp on how things are laid out here and a problem I’m running into is that when I attempt to create a diagram for one database in particular I’m getting the
following error:

Cannot execute as the database principal because the principal «dbo»
does not exist, this type of principal cannot be impersonated, or you
do not have permission. (Microsoft SQL Server, Error: 15517)

I’ve been searching for more info on this issue and it looks like it usually is caused by the owner of the database getting deleted but this database shows SA as the owner so that shouldn’t be the issue. I’m not even able to create a diagram if I sign in as sa…

Not sure if this may have something to do with it but this particular database was migrated to a new server a few months ago so something may have broken when that was done (although the database is live and in use so it isn’t entirely broken).

Maybe its looking for the sa account from the old server and it just needs to be updated to use the sa account on the current server?

Edit: This DB may not actually have an owner after all. Performing a Right click > Properties > Files on the database shows no owner for the database even though the owner field under general does.

I tried to change it through the GUI and was informed that set owner failed with the following error:

Lock request time out period exceeded. (Microsoft SQL Server, Error:
1222)

I also tried the alter authorization ... statement and it just never completes (or I cancelled it after a few minutes to be more accurate). Am I being to impatient with that statement or is something else going on here?

Источник

Search code, repositories, users, issues, pull requests…

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Sign up

Источник
  • Remove From My Forums

  • Question

  • please need help ..

    this the wrong message shown in my log

    An exception occurred while enqueueing a message in the target queue. Error: 15517<c/> State: 1. Cannot execute as the database principal because the principal «dbo» does not exist<c/> this type of principal cannot be impersonated<c/> or you do not have permission.

    MCP MCSA MCSE MCT

All replies

  • Most probably the ownership of the queue’s database is broken somehow. Try to ensure the owner is a valid server login. You may do that by running ALTER AUTHORIZATION on DATABASE::[your_db_name] to [some_valid_login]

    • Proposed as answer by

      Friday, July 15, 2011 7:45 AM

  • Most probably the ownership of the queue’s database is broken somehow. Try to ensure the owner is a valid server login. You may do that by running ALTER AUTHORIZATION on DATABASE::[your_db_name] to [some_valid_login]

    a new error shown

    Cannot show requested dialog.

    Additional information:

    Cannot show requested dialog. (SqlMgmt)

    Property Owner is not available for Database ’[     ]’.  This property may not exist for this object, or may not be retrievable due to insufficient access rights.

    (Microsoft.SqlServer.Smo

    MCP MCSA MCSE MCT

  • {$content}

    The link you provided is very useful, Now I have a more
    clear idea about it.

  • This error is related to an orphaned database owner on your database. This can be verified by either looking
    at object explorer details and identifying the database(s) without an entry in the Owner column or running a select against sys.databases to see the database owner that is no longer valid.

    Once you have the databases then you can run:

    USE [DB Name]
    GO
    EXEC dbo.sp_changedbowner @loginame = N’sa’, @map = false
    GO

    This will produce the result:

    The dependent aliases were dropped.

    And now the issue should be corrected and the errors will stop being generated in the Windows event log and SQL Server Error Log.

    • Proposed as answer by
      Kieran Patrick Wood
      Wednesday, April 11, 2018 6:17 PM

  • Thanks Brown.. It really Helped.

    USE [DB Name]
    GO
    EXEC dbo.sp_changedbowner @loginame = N’sa’, @map = false
    GO

    • Proposed as answer by
      Kieran Patrick Wood
      Wednesday, April 11, 2018 6:17 PM

  • Excelente, muchas gracias por su aporte.

  • Cobrow, it really works. Thanks..

  • Best response to correct this error and it worked perfectly!  I got the error when upgrading from 2014 to 2016.  Thank you very much!

  • I’ve restored a database into a different machine and was having the same issue:

     «Cannot execute as the database principal because the principal «dbo» does not exist, this type of principal cannot be impersonated, or you do not have permission.»

    The command suggested by cobrow worked perfectly for me:

    USE [DB Name]
GO
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO

    Thank you!

    • Proposed as answer by
      Kieran Patrick Wood
      Wednesday, April 11, 2018 6:17 PM

  • But how do you determine which DB Name to use? How did you determine the DB this is happening on?

Last week I had the following error message repeating over and over again in the SQL Server log of one of my servers. It was repeating so much that the logs were growing very large, very fast:

Message
An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal «dbo» does not exist, this type of principal cannot be impersonated, or you do not have permission.

If you do a search for this error, you won’t find a whole lot on it. You’ll find that if you can figure out what database it is, then you should change the owner of the database.

First, check sys.databases:

Query sys.databases joined with sys.server_principals like so:

SELECT d.name AS 'Database', s.name AS 'Owner'

FROM sys.databases d


  LEFT JOIN sys.server_principals s


    ON d.owner_sid = s.sid;

You’re looking for databases owned by logins you know are no longer valid. We had a DBA recently retire and found a few that way.

Second, check to see if sys.database_principals and sys.databases match up:

We were still getting the errors and by looking at sys.databases, nothing was showing up as being wrong. However, that error means there is a mismatch with dbo matching up to a login. That means you have to take it a step further and query sys.database_principals and see how dbo matches up. For a particular DB, say Example, here’s the type of query you’d run:

SELECT sp.name AS 'dbo_login', o.name AS 'sysdb_login'

FROM Example.sys.database_principals dp


  LEFT JOIN master.sys.server_principals sp


    ON dp.sid = sp.sid


  LEFT JOIN master.sys.databases d


    ON DB_ID('Example') = d.database_id


  LEFT JOIN master.sys.server_principals o


    ON d.owner_sid = o.sid


WHERE dp.name = 'dbo';

Obviously, if you wanted to run it for all DBs, you’d do something like:

EXEC sp_MSForEachDB

'SELECT ''?'' AS ''DBName'', sp.name AS ''dbo_login'', o.name AS ''sysdb_login''





  



                       
  






FROM ?.sys.database_principals dp


  LEFT JOIN master.sys.server_principals sp


    ON dp.sid = sp.sid


  LEFT JOIN master.sys.databases d


    ON DB_ID(''?'') = d.database_id


  LEFT JOIN master.sys.server_principals o


    ON d.owner_sid = o.sid


WHERE dp.name = ''dbo'';';

By doing this, I found several databases that sys.databases said had an owner. However, when I checked it from the database’s sys.database_principals, the SID didn’t match up for dbo. The column I had for dbo_login came back NULL. That was a clear sign of the issue. There is also the possibility you will see a mismatch between dbo_login and sysdb_login. It appears that as long as dbo_login matches a legitimate login, the error is not generated. I found that on some DBs on one of my servers. While it’s not causing a problem now, I’ll be looking to correct the mismatch.

Correcting the Error:

The easiest way to correct the error is to use ALTER AUTHORIZATION on the databases which have the NULL login match for dbo. It’s as simple as:

ALTER AUTHORIZATION ON DATABASE::Example TO sa;

Could not delete publication ‘XXX’. Microsoft SQL Server, Error: 15517

Hey friends, I am filling good to write my 1st post in 2015. This is related to error in replication which i faced in 2014 he he.

Last month i was facing an error during roll backing Transnational replication as below..

——————————
Could not delete publication ‘XXX’. Microsoft SQL Server, Error: 15517

TITLE: Microsoft.SqlServer.ConnectionInfo

——————————
SQL Server could not disable publishing and distribution on ‘SUMAN-29’.

Cannot execute as the database principal because the principal «dbo» does not exist, this type of principal cannot be impersonated, or you do not have permission.
Changed database context to ‘master’. (Microsoft SQL Server, Error: 15517)

I resolved through below :-

1st run below query in SSMS.

ALTER AUTHORIZATION ON DATABASE::[DBNAME] TO [sa]

2nd Then Right click On publication and choose delete

3rd Then disable the distributor..

Now just refresh instance  

Popular posts from this blog

For encrypting and decrypting , we must use the bytea data type on the column which we implement. Bcoz bytea will use the pgcrypto method by default. However, you will need to create the pgcrypto extension to enable these functions as they are not pre-defined in PostgreSQL/PPAS. Example CREATE EXTENSION pgcrypto; CREATE TABLE userinfo (username varchar(20), password bytea); >>    Inserting the data in an encrypted format INSERT INTO userinfo VALUES(‘ suman ‘,encrypt(‘111222′,’password’,’aes’)); select * from userinfo ; >>    Retrieving the data as decrypted format SELECT decrypt(password,decode(‘password’,’escape’::text),’aes’::text) FROM userinfo; Thanks for reading Plz dont forget to like Facebook Page.. https://www.facebook.com/pages/Sql-DBAcoin/523110684456757

 It was Monday 9 th Jun 47 degr. temperature of Delhi-NCR. Temperature was like boiling me and database. When I reached my office( @ 8.45 am) got an alert from one of Server. “MSDB is in suspected mode” At the same time comes in my mind, this issue will boil me today.. I just tried to cool my self through cold drink then connected server from my local system using windows authentication mode..

Last week I got this error from one of developer who was trying to deploy his project from Testing server to SQL Azure QA server. He was using “Deploy Database to SQL Azure” option from SSMS Tool-Task option. After connecting to SQL Azure portal when operation started to deployment below errors occurs. Validation of the schema model for data package failed. Error SQL71562: Error validating element xx.xxx.xx:function .dbo.xxx has an unresolved refrence to object xx.dbo.xxxx external refrences are not supported when creating a package from this platform . Reason: The reason of the this error was; some functions of project was dependent on master database and only single database was being deploy to SQL Azure. DACFx must block Export when object definitions (views, procedures, etc.) contain external references, as Azure SQL Database does not allow cross-database external references So, this error was coming. Solution : I suggested him to create those function to locally

Источник

RRS feed

  • Remove From My Forums

  • Вопрос

  • please need help ..

    this the wrong message shown in my log

    An exception occurred while enqueueing a message in the target queue. Error: 15517<c/> State: 1. Cannot execute as the database principal because the principal «dbo» does not exist<c/> this type of principal cannot be impersonated<c/> or you do not have permission.

    MCP MCSA MCSE MCT

Все ответы

  • Most probably the ownership of the queue’s database is broken somehow. Try to ensure the owner is a valid server login. You may do that by running ALTER AUTHORIZATION on DATABASE::[your_db_name] to [some_valid_login]

    • Предложено в качестве ответа

      15 июля 2011 г. 7:45

  • Most probably the ownership of the queue’s database is broken somehow. Try to ensure the owner is a valid server login. You may do that by running ALTER AUTHORIZATION on DATABASE::[your_db_name] to [some_valid_login]

    a new error shown

    Cannot show requested dialog.

    Additional information:

    Cannot show requested dialog. (SqlMgmt)

    Property Owner is not available for Database ’[     ]’.  This property may not exist for this object, or may not be retrievable due to insufficient access rights.

    (Microsoft.SqlServer.Smo

    MCP MCSA MCSE MCT

  • {$content}

    The link you provided is very useful, Now I have a more
    clear idea about it.

  • This error is related to an orphaned database owner on your database. This can be verified by either looking
    at object explorer details and identifying the database(s) without an entry in the Owner column or running a select against sys.databases to see the database owner that is no longer valid.

    Once you have the databases then you can run:

    USE [DB Name]
    GO
    EXEC dbo.sp_changedbowner @loginame = N’sa’, @map = false
    GO

    This will produce the result:

    The dependent aliases were dropped.

    And now the issue should be corrected and the errors will stop being generated in the Windows event log and SQL Server Error Log.

    • Предложено в качестве ответа
      Kieran Patrick Wood
      11 апреля 2018 г. 18:17

  • Thanks Brown.. It really Helped.

    USE [DB Name]
    GO
    EXEC dbo.sp_changedbowner @loginame = N’sa’, @map = false
    GO

    • Предложено в качестве ответа
      Kieran Patrick Wood
      11 апреля 2018 г. 18:17

  • Excelente, muchas gracias por su aporte.

  • Cobrow, it really works. Thanks..

  • Best response to correct this error and it worked perfectly!  I got the error when upgrading from 2014 to 2016.  Thank you very much!

  • I’ve restored a database into a different machine and was having the same issue:

     «Cannot execute as the database principal because the principal «dbo» does not exist, this type of principal cannot be impersonated, or you do not have permission.»

    The command suggested by cobrow worked perfectly for me:

    USE [DB Name]
GO
EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
GO

    Thank you!

    • Предложено в качестве ответа
      Kieran Patrick Wood
      11 апреля 2018 г. 18:17

  • But how do you determine which DB Name to use? How did you determine the DB this is happening on?

Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка 1551 уаз патриот
  • Ошибка 1509 хонда
  • Ошибка 1509 ваз
  • Ошибка 15077 ауди
  • Ошибка 1506 ваз