5

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?

Community
  • 1
Charles Hepner
  • 425
  • 1
  • 3
  • 10

1 Answers1

3

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/

Charles Hepner
  • 425
  • 1
  • 3
  • 10
Ian Chamberland
  • 680
  • 4
  • 8
  • Yes, that's true for the database role db_owner. However, I'm asking about the actual owner of the database. The one you see under the General tab when looking at database properties. The one that can be changed via the sp_changedbowner stored procedure. I'm not asking about the role you are discussing. – Charles Hepner Jul 08 '12 at 23:31
  • IT will be the same permissions. the person assigned as that owner will have DBO privileges applied. – Ian Chamberland Jul 09 '12 at 16:20
  • A better way to say what you are suggesting is that the login you specify as the database owner gets mapped to the dbo user within the database. – Charles Hepner Jul 10 '12 at 01:04