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
