5

My databases have been working fine on my Windows Server 2016 standard. The database management system is Microsoft SQL Server 2017 Express.

Today I added a new table and content, all is fine. I tried to open the database diagram and received an error message:

enter image description here

And in case the image doesn't work, the message is:

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

Nothing has changed on my server - no updates, etc.

I can access all the tables. When I look at the account I log in, I can see I am a the database owner, which other posts suggest to do - but I wasn't previously so unsure as to why this would help.

This issue affects every single database I have.

The only difference is I now create a daily back up from a scheduled task with the following script

BACKUP DATABASE DBName TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Backup\DBName .bak' 
WITH NOFORMAT, INIT,  NAME = N'DBName Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

The result of

SELECT ORIGINAL_LOGIN(), USER_NAME(), SUSER_SNAME();

shows

enter image description here

  • Have you tried setting up a trace to see what query SSMS is trying to execute prior to displaying this error? – John Eisbrener Sep 19 '17 at 19:13
  • Did you deleted the owner ( the login ) of this database? – Racer SQL Sep 19 '17 at 19:15
  • @JohnEisbrener no, I will Google how to do this. I'm not a DBA person, I'm just in charge of it – MyDaftQuestions Sep 19 '17 at 19:15
  • @RacerSQL, No, the log in is the main login (the account I use to login into the server) – MyDaftQuestions Sep 19 '17 at 19:15
  • If that login doesn't exist in sys.server_principals, then you must be obtaining access via a Windows group. Once again, try setting the authorization of the database to sa. – Aaron Bertrand Sep 19 '17 at 23:32
8

This is because the owner of the database ( the login ) was deleted. You should remap the owner of the database, to a login that exists.

ALTER AUTHORIZATION 
ON DATABASE::[DatabaseName]
TO [A Suitable Login];

Dont use sp_changedbowner. it's a deprecated feature.

  • Nothing has been deleted. No login etc. I'm the only one to access to it. I've updated to my post to explain the only change I made – MyDaftQuestions Sep 19 '17 at 19:18
  • But did you try to do these steps? if i'm not mistaken, [sp_changedbowner] is deprecated, so you should test with ALTER AUTHORIZATION. if it didnt solve the problem, just use your own login. you can see the database owner with ` right click on the database > properties > general > owner`. I'm telling you this because I had this same problem a lot of times in the past. – Racer SQL Sep 19 '17 at 19:22
  • 2
    @MyDaftQuestions Nothing was necessarily deleted. It could be this database was restored from another server and the owner sid simply doesn't exist here. What account is the owner of the database? SELECT p.name, p.type_desc FROM sys.databases AS d LEFT OUTER JOIN sys.server_principals AS p ON d.owner_sid = p.sid WHERE d.name = N'name'; - if that returns NULL then alter the authorization of the database to sa. If it doesn't return NULL, then maybe something is jacked with that account. – Aaron Bertrand Sep 19 '17 at 19:23
  • (In fact, I would remove the sp_changeobjectowner version. Let's not perpetuate deprecated features.) – Aaron Bertrand Sep 19 '17 at 19:26
  • @AaronBertrand the results pane from your query shows 2 columns, the headings are name and type_desc but there is no other information. So it doesn't return NULL – MyDaftQuestions Sep 19 '17 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.