Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Query accounts, domain groups, and members who have admin membership.

By Eric Russell,

Ideally only members within your DBA team are members of the SQL Server sysadmin role. If we see an account belonging to an application, 3rd party tool, or user, then we must consider whether or not full admin permissions are really needed. For example, SQL Server version 2005 and earlier would by default add the local Windows group BUILTIN\Administrators to the sysadmin role, and we will see this occur on a lot of older or migrated installations. However, an administrator on the server may not be responsible for administrating the SQL Server instance hosted on the server.

The following script returns accounts, domain groups, and domain group members who have been granted or inherited membership in a SQL Server fixed admin role. The system view master.sys.server_principals won’t reveal what domain accounts are members of a domain group, so this script leverages the system stored procedure xp_logininfo to expand the result.
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 @principals
select
 type_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_note
from 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 null
begin
 select @group_type = null, @group_name = null;
 select top 1 @group_type = principal_type, @group_name = principal_name
  from @principals
   where principal_type in ('windows_group')
    and member_name = '-'      
    and admin_role_desc is not null
    and principal_name not in (select group_name from @admin_groups);
 if @group_name is not null
 begin
  -- Call xp_logininfo to return all domain accounts belonging to group:
  insert @admin_groups values (@group_type, @group_name);
  begin try
   delete from @logininfo;
   insert into @logininfo
    exec master..xp_logininfo @group_name,'members';
   -- Update number of members for group to logininfo_note:
     update @principals
    set logininfo_note = 'xp_logininfo returned '+cast(@@rowcount as varchar(9))+' members.'
     where principal_type in ('windows_group')
      and principal_name = @group_name
      and member_name = '-';   
  end try
  begin catch
   -- If an error occurred, then update it to logininfo_note, and then continue:
     update @principals
    set logininfo_note = 'xp_logininfo returned error '+cast(error_number() as varchar(9))
     where principal_type in ('windows_group')
      and principal_name = @group_name
      and member_name = '-';
  end catch
  -- For each group member, insert a record into the result:
  insert into @principals
  select
   @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_desc
    from @principals
     where principal_type = @group_type
     and principal_name = @group_name
     and member_name = '-') as admin_role_desc,
   null as logininfo_note
  from @logininfo;
  -- For each group member that is a group,
  -- insert a record of type 'WINDOWS_GROUP' into the result:
  insert into @principals
  select
   'WINDOWS_GROUP' as principal_type,
   account_name as principal_name,
   '-' as member_name,
   null as create_date,
   null as modify_date,
   (select admin_role_desc
    from @principals
     where principal_type = @group_type
     and principal_name = @group_name
     and member_name = '-') as admin_role_desc,
   null as logininfo_note
  from @logininfo
   where type = 'group'
   and not exists
    (select 1
     from @principals
      where 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_desc
 from @principals
  where admin_role_desc is not null
   order by principal_type, principal_name, member_name;

The example below illustrates what type of accounts may commonly be seen.

principal_type principal_name member_name
SQL_LOGIN sa -
WINDOWS_LOGIN NT AUTHORITY\SYSTEM -
WINDOWS_GROUP BUILTIN\Administrators -
WINDOWS_GROUP BUILTIN\Administrators MYCORP\ServerGirl_Gail
WINDOWS_GROUP BUILTIN\Administrators MYCORP\HelpDesk_Harry
WINDOWS_GROUP BUILTIN\Administrators MYCORP\DomainAdmin_Doug
WINDOWS_GROUP BUILTIN\Administrators MYCORP\DataDude_Joe
WINDOWS_GROUP BUILTIN\Administrators MYCORP\DataGirl_Sue
WINDOWS_GROUP BUILTIN\Administrators MYCORP\DataManager_Debbie
WINDOWS_GROUP BUILTIN\Administrators MYCORP\SQLAgentUser
WINDOWS_GROUP MYCORP\DBA -
WINDOWS_GROUP MYCORP\DBA MYCORP\DataDude_Joe
WINDOWS_GROUP MYCORP\DBA MYCORP\DataGirl_Sue
WINDOWS_GROUP MYCORP\DBA MYCORP\DataManager_Debbie
WINDOWS_LOGIN MYCORP\SQLServerAgent -
WINDOWS_LOGIN MYCORP\BI_Bob -
WINDOWS_LOGIN MYCORP\CIO_Cindy -

If a server was configured to use mixed authentication (both SQL Server and Windows), then we will see a record of type SQL_LOGIN named ‘SA’, and perhaps other additional SQL Server accounts. Records of type WINDOWS_LOGIN identify local or domain accounts, and WINDOWS_GROUP identifies domain group accounts. If a domain group has been granted membership in an admin role, then all domain account members of that group have inherited the admin role. For example, we may see a domain group with a name like ‘MYCORP\DBA’, of which our DBA team are members.

There are a wide variety of built in server or database level roles that grant elevated permissions for tasks such as: selecting from all tables (db_datareader), viewing object definitions (VIEW DEFINITION), or querying execution plans (SHOWPLAN) and dynamic management views (VIEW SERVER STATE). Therefore, as you review what accounts currently have admin role membership, keep in mind that permission to perform the type of tasks mentioned above does not require full admin role privileges.

Total article views: 5553 | Views in the last 30 days: 7
 
Related Articles
FORUM

Default schema windows group

Database acces windows group

FORUM

Block windows groups

How to block windows AD groups from connecting to SQLServer 2005 and Kill existing connections

FORUM

Check if Windows User is an SQL Admin

How to check if a Windows User is an SQL Admin

SCRIPT

Windows Group Membership Checker

A script to reveal which logins have access to your sql server via windows groups.

FORUM

Query accounts, domain groups, and members who have admin membership.

Comments posted to this topic are about the item [B]Query accounts, domain groups, and members who h...

Tags
administration    
roles    
security    
server_principals    
sysadmin    
xp_logininfo    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones