How to find all sysadmins on a SQL Server Instance using TSQL


Please use below code to get all the users who have SA (Sysadmin) privilege on a SQL Instance.

Kindly note that this code will retrieve all the users accounts even if they are granted access via an Active Directory group.


SELECT
p.name AS [Name],r.type_desc,r.is_disabled,r.create_date ,r.modify_date,r.default_database_name

FROM sys.server_principals r
INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
INNER JOIN sys.server_principals p ON
p.principal_id = m.member_principal_id
WHERE r.type = 'R' and r.name = N'sysadmin'


Note: This is tested on SQL 2008 R2 SP2

Comments

Popular Posts