8

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

5
  • Have you tried setting up a trace to see what query SSMS is trying to execute prior to displaying this error? Sep 19, 2017 at 19:13
  • Did you deleted the owner ( the login ) of this database?
    – Racer SQL
    Sep 19, 2017 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 Sep 19, 2017 at 19:15
  • @RacerSQL, No, the log in is the main login (the account I use to login into the server) Sep 19, 2017 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. Sep 19, 2017 at 23:32

1 Answer 1

10

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.

18
  • 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 Sep 19, 2017 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, 2017 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. Sep 19, 2017 at 19:23
  • (In fact, I would remove the sp_changeobjectowner version. Let's not perpetuate deprecated features.) Sep 19, 2017 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 Sep 19, 2017 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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