Home / server / Questions / 405725
Accepted
Charles Hepner
Asked: 2012-07-08 13:04:35 +0800 CST

what permissions are granted to a sql server database owner?

  • 772

I have been trying to find out what permissions are granted to the owner of a database in SQL Server 2005 or higher. I have seen best practices questions like this one: What is the best practice for the database owner in SQL Server 2005? but I haven't been able to find anything specifically addressing what the purpose of having a database owner in SQL Server is and what permissions are granted as a result of making a given login a database owner. If the owner of the database is disabled, what would stop working?

sql-server

1 Answers

  1. Best Answer

    Essentially setting a database owner allows for full privileges in that database by mapping that login to the dbo user in that database. This includes the ability to select, insert, update, delete and execute. In addition the db owner can alter objects, grant permissions and change the configuration of the database. If it can be done within the database the db owner can do it.

    As per microsoft: Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.

    http://msdn.microsoft.com/en-us/library/ms189121.aspx

    The more up to date alternative to the sp_changedbowner stored procedure is the syntax: ALTER AUTHORIZATION ON database:: TO "".

    Finally, it doesn't seem like anything will break if the owner of a database is disabled. It is common to set the owner of all user databases to 'sa' and this login is commonly disabled. I tested myself and the results seem to bear that statement out. See here: http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/a0519b62-a509-45c9-b2cb-d9a2e6861ace/

    • 3