Search This Blog

Script to find database owners using T-SQL

 

Database owner information is stored in sys.databases view as owner_sid, in order to get the actual mapped name we need to use suser_sname() function

   1: select name, suser_sname(owner_sid)as 'owner' from sys.databases



   2: select name, suser_sname(sid)'owner' from sysdatabases





Change the database Owner



Microsoft provides system stored procedures for changing the db owner. Keep in mind, a user database should have a db owner associated with it; dont mis interpret this witj db_owner role. I personally prefer setting the db owner to ‘sa’



For SQL Server 2000 you can use the following, make sure you run this one the database whose ownership has to be changed





   1: EXEC sp_changedbowner 'SA'




For SQL 2005 onwards you can use the following





   1: ALTER AUTHORIZATION ON DATABASE::MydatabaseName TO sa 




If you have lot of ownership to change, you can generate the ‘ALTER AUTHORIZATION’ script using the query below



 





   1: select 'ALTER AUTHORIZATION ON DATABASE::'+name+' TO sa ' , * from sys.databases where database_id > 4 




 



Here is another useful script which lists all the database objects and its owners





   1: SELECT NAME AS Object, USER_NAME(uid) AS Owner



   2: FROM sysobjects



   3: WHERE USER_NAME(uid) NOT IN ('sys', 'INFORMATION_SCHEMA')



   4: ORDER BY Owner, Object


1 comment:

Sudhir DBAKings said...

Nice post very helpful

dbakings