How to get a list of databases and their owner details?

On 17 Apr, 2015

MS SQL DBA

By : Charith Silva

No Comments

Below script can be used to get a list of databases and their owner details for a current SQL Server instance. The quickest and east way to get those details is use sys.databases systems view with SUSER_SNAME () inbuilt security function. Also xp_logininfo systems stored procedure can be included to the script to get more information about Windows users and Windows groups.

USE [master];
GO

DECLARE @UserList TABLE
(
[AccountName] NVARCHAR(128),
[Type] NVARCHAR(64),
[Privilege] NVARCHAR(64),
[Mapped Login Name] NVARCHAR(128),
[Permission Path] NVARCHAR(MAX)
)

INSERT INTO @UserList
EXEC xp_logininfo

SELECT @@SERVERNAME AS [Server Name] ,
d.name AS [Database Name],
state_desc as [Status],
suser_sname(d.owner_sid) AS [Database Owner],
u.[Type],
u.[Privilege],
u.[Mapped Login Name]
FROM sys.databases d
LEFT JOIN @UserList u ON suser_sname(d.owner_sid)=u.[AccountName]
ORDER BY d.name

USE [master];

DECLARE @UserList TABLE

(

[AccountName] NVARCHAR(128),

[Type] NVARCHAR(64),

[Privilege] NVARCHAR(64),

[Mapped Login Name] NVARCHAR(128),

[Permission Path] NVARCHAR(MAX)

)

INSERT INTO @UserList

EXEC xp_logininfo

SELECT @@SERVERNAME AS [Server Name] ,

d.name AS [Database Name],

state_desc as [Status],

suser_sname(d.owner_sid) AS [Database Owner],

u.[Type],

u.[Privilege],

u.[Mapped Login Name]

FROM sys.databases d

LEFT JOIN @UserList u ON suser_sname(d.owner_sid)=u.[AccountName]

ORDER BY d.name

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva