Find the database owner name from the owner_sid - t-sql and suser_sname

08 February,2013 by Jack Vamvas

Finding the owner name from the sys.databases catalog view  owner_sid column is simple . Sys.databases returns all the databases on the SQL Server Instance , one of the columns is owner_sid – which returns a  varbinary(85) column.  A example is 0x010500000000000515000000323AF1A8V4034CF686BFA5EE0C460000

The owner_sid returns the security identifier of the database. From the sid it is difficult to derive the owner name. Using suser_sname returns the owner name

--for all databases
select suser_sname(owner_sid) as 'owner_name', name from sys.databases

--single database
select suser_sname(owner_sid) as 'owner_name', name from sys.databases where name = DB_NAME

 

 Read More on current user and security policy

 SQL Server – Find current user and sysadmin - SQL Server DBA

SQL Server Security Policy - SQL Server DBA

Powershell sql server security audit - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Find the database owner name from the owner_sid - t-sql and suser_sname


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer