Database ownership is one of the most important and overlooked configuration items when creating or restoring databases. The account that owns the database is mapped to the dbo user in that database giving the user full permissions to the database - this includes permission to drop the database!
The reason it gets overlooked is that once a database is setup we often don't review the database settings. When we provide access to the database we do it via the logins or security pages. Also the creator of the database is set to the owner by default. This is also the case for restoring databases. Other problems can occur if the original creator of the database leaves the organisation. In this case they may still have full permissions to the database long after they've left the organisation.
It's best to create a standard for your databases including setting a standard database owner. Typically I set the database owner to the 'sa' account. To change the owner of a database run the TSQL below (changing the name of the 'username' to the desired owner of the database.
EXEC sp_changedbowner 'username'
As an additional best practice it's a good idea to make sure the owner of your model database is set to 'sa'. This will ensure that all databases create (but not restored) are created with the owner set to 'sa'.
No comments:
Post a Comment