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 – what permissions are granted to a sql server database owner
sql server
Related Topic
- Windows – No permission to access SQL server perfmon counters
- Sql-server – Error 207 – invalid column name ‘msrepl_tran_version’ with Sql Server Replication
- Sql-server – Best way to backup a SQL Server database nightly
- Sql-server – SQL Server Database Access fails when User Granted Acess via Domain Group
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/