question

Jason Crider avatar image
Jason Crider asked

Create database with sa as owner failed

I am trying to get the security items fixed to be correct, so I only want to give least permissions.

I want all of the databases to have the owner set to sa instead of the user.

I gave the developer dbcreator.

When they go to create the database it gives them an error, but it actually creates the database with their user as the owner.

The error is: Set owner failed for Database 'namehere'

Cannot Find the principal 'sa', because it does not exist or you do not have permission. Error 15151.

What's the best way to accomplish this doing it the right way?

sql-server-2005
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Matt Whitfield avatar image
Matt Whitfield answered

Assuming you're on 2005+, the statement used is 

ALTER AUTHORIZATION ON DATABASE::<db_name> TO <login_name>

From the MSDN reference for ALTER AUTHORIZATION

Requires TAKE OWNERSHIP permission on the entity. If the new owner is not the user that is executing this statement, also requires either, 1) IMPERSONATE permission on the new owner if it is a user or login; or 2) if the new owner is a role, membership in the role, or ALTER permission on the role; or 3) if the new owner is an application role, ALTER permission on the application role.

So, sounds like the creator of the database should also have IMPERSONATE permission. However, I would be wary of giving users IMPERSONATE on 'sa'. So perhaps the best way around it would be to change the ownership using ALTER AUTHORIZATION after the database is created using the sa login.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered

You may want to hit BOL and look up sp_changedbowner.

sp_changedbowner [ @loginame = ] 'login'
login cannot become the owner of the current database if it already has access to the database through an existing alias or user security account within the database.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nicholson avatar image
Nicholson answered
I'm having the same issue that Jason describes, above. I have found that this default is the case anytime a database is created as well as anytimme it is restored. The database defaults back to the user performing the create/restore. As Jason mentioned above, I would also like to do this prior to a database being created/restored so that the SA account is the default. Is there no way around changing the default owner? Also, does anyone know where I can find a complete list of the triggers for this default?
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.