Get List of Users with 'sa' Priveleges

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Tested

Credits:

Author: Unknown

Date: 9 Jun 2020

Description

Get a list of users with 'sa' priveleges.

Code

set xact_abort off;set nocount on;declare @principals table(primary key ( principal_type, principal_name, member_name ),principal_type varchar(180) not null,principal_name varchar(180) not null,member_name varchar(180) not null,create_date datetime null,modify_date datetime null,admin_role_desc varchar(180) null,logininfo_note varchar(8000) null);-- insert all accounts and groups into result: insert into @principalsselecttype_desc,name,'-' as member_name,create_date,modify_date,(case is_srvrolemember('sysadmin',name) when 1 then 'sysadmin|' else null end+ case is_srvrolemember('securityadmin',name) when 1 then 'securityadmin|' else null end+ case is_srvrolemember('serveradmin',name) when 1 then 'serveradmin|' else null end+ case is_srvrolemember('setupadmin',name) when 1 then 'setupadmin|' else null end+ case is_srvrolemember('processadmin',name) when 1 then 'processadmin|' else null end+ case is_srvrolemember('diskadmin',name) when 1 then 'diskadmin|' else null end+ case is_srvrolemember('dbcreator',name) when 1 then 'dbcreator|' else null end+ case is_srvrolemember('bulkadmin',name) when 1 then 'bulkadmin|' else null end) as admin_role_desc,null as logininfo_notefrom sys.server_principals;declare @admin_groups table(primary key ( group_type, group_name ),group_type varchar(180) not null,group_name varchar(180) not null);declare @logininfo table(primary key ( account_name, permission_path ),account_name varchar(180) not null,type varchar(180) null,privilege varchar(180) null,mapped_login_name varchar(180) null,permission_path varchar(180) not null);-- For each domain group with admin privilages, -- insert one record for each of it's member accounts into the result: declare @group_type varchar(180), @group_name varchar(180);select @group_type = '*', @group_name = '*';while @group_name is not nullbeginselect @group_type = null, @group_name = null;select top 1 @group_type = principal_type, @group_name = principal_namefrom @principalswhere principal_type in ('windows_group')and member_name = '-'and admin_role_desc is not nulland principal_name not in (select group_name from @admin_groups);if @group_name is not nullbegin-- Call xp_logininfo to return all domain accounts belonging to group: insert @admin_groups values (@group_type, @group_name);begin trydelete from @logininfo;insert into @logininfoexec master..xp_logininfo @group_name,'members';-- Update number of members for group to logininfo_note: update @principalsset logininfo_note = 'xp_logininfo returned '+cast(@@rowcount as varchar(9))+' members.'where principal_type in ('windows_group')and principal_name = @group_nameand member_name = '-';end trybegin catch-- If an error occurred, then update it to logininfo_note, and then continue: update @principalsset logininfo_note = 'xp_logininfo returned error '+cast(error_number() as varchar(9))where principal_type in ('windows_group')and principal_name = @group_nameand member_name = '-';end catch-- For each group member, insert a record into the result: insert into @principalsselect @group_type as principal_type, @group_name as principal_name,account_name as member_name,null as create_date,null as modify_date,(select admin_role_descfrom @principalswhere principal_type = @group_typeand principal_name = @group_nameand member_name = '-') as admin_role_desc,null as logininfo_notefrom @logininfo;-- For each group member that is a group, -- insert a record of type 'WINDOWS_GROUP' into the result: insert into @principalsselect'WINDOWS_GROUP' as principal_type,account_name as principal_name,'-' as member_name,null as create_date,null as modify_date,(select admin_role_descfrom @principalswhere principal_type = @group_typeand principal_name = @group_nameand member_name = '-') as admin_role_desc,null as logininfo_notefrom @logininfowhere type = 'group'and not exists(select 1from @principalswhere principal_type = 'WINDOWS_GROUP' and principal_name = account_name and member_name = '-');end;end;-- Return result of only those accounts, groups, and members who have an admin role: select principal_type, principal_name, logininfo_note, member_name, create_date, modify_date, admin_role_descfrom @principalswhere admin_role_desc is not null -- only get users with admin role granted order by principal_type, principal_name, member_name;