Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Simplify SQL Server Database Development     ====>    Webcast Registration
 

Auditing Windows Groups from SQL Server


By:   |   Read Comments (4)   |   Related Tips: More > Auditing and Compliance

Problem

Managing database security should be part of every DBAs job.  With SQL Server you have the ability to use either SQL Server and Windows or Windows only security.  The advantage of using Windows security for your SQL Servers is that you can take advantage of the Windows security model and security policies that have been setup on your domain.  Another advantage is that you can manage security at the domain level instead of at the SQL Server instance level. 

In addition you can use use Windows groups to manage the security buckets.  Based on the groups that are setup you can put specific Windows users in these groups and then give SQL Server access to this Windows group instead of having to create logins for every single windows user. 

The disadvantage to this is that the specific people within these groups is masked at the SQL Server level.  In your logins you can see the groups that have access, but you have no idea what users are in what groups.  So how can you get this information from within SQL Server?

Solution

SQL Server offers some insight into this issue with the xp_logininfo extended stored procedure.  This stored procedure is part SQL Server 2000 and all later versions. 

This xp takes the following parameters:

  • @acctname - the windows account name or group
  • @option - information to display
    • 'all' - display information for all permission paths
    • 'members' - display list of members in a group
  • @privelege - this is an output variable from this command and returns 'admin', 'user' or 'null

Following is T-SQL code that loops through your logins and wherever there is a Windows Group the xp_logininfo XP is called to return information about the Windows group.

SQL Server 2000

For this example we are querying from the syslogins table where the isntgroup = 1 and status = 10.

DECLARE @LoginName sysname 
DECLARE @sql NVARCHAR (2000) 

BEGIN 
   DECLARE cur_Loginfetch CURSOR FOR 
    
   SELECT [name] FROM master.dbo.syslogins WHERE isntgroup = 1 AND status = 10  
    
   OPEN cur_Loginfetch 
    
   FETCH NEXT FROM cur_Loginfetch INTO @LoginName 
   WHILE @@FETCH_STATUS = 0 
       BEGIN 
           EXEC xp_logininfo @LoginName , 'members' 
           FETCH NEXT FROM cur_Loginfetch INTO @LoginName 
       END 
   CLOSE cur_Loginfetch 
   DEALLOCATE cur_Loginfetch 
   RETURN 
END

Here is the output from running the above code.  Here we can see the Windows users that are part of the BUILTIN\Administrators group.

account name

SQL Server 2005, 2008, 2008R2, 2014, 2016 and 2017

For this example we are querying from the sys.server_principals catalog view where type = 'G'.

DECLARE @LoginName sysname 
DECLARE @sql NVARCHAR (2000) 

BEGIN 
   DECLARE cur_Loginfetch CURSOR FOR 
    
   SELECT [name] FROM master.sys.server_principals WHERE TYPE = 'G' 
    
   OPEN cur_Loginfetch 
    
   FETCH NEXT FROM cur_Loginfetch INTO @LoginName 
   WHILE @@FETCH_STATUS = 0 
       BEGIN 
           EXEC xp_logininfo @LoginName , 'members' 
           FETCH NEXT FROM cur_Loginfetch INTO @LoginName 
       END 
   CLOSE cur_Loginfetch 
   DEALLOCATE cur_Loginfetch 
   RETURN 
END 

Here is the output from running the above code. Here we can see the Windows users that are part of the BUILTIN\Administrators group.

server

This is a pretty straightforward process to get the information.  On your production servers you should be able to see a lot more information then what is provided in these examples.

Next Steps
  • Use these scripts to get information about your Windows groups that have access to SQL Server.
  • Add these scripts to your auditing procedures to keep on top of any potential security risks
  • Write a process that captures this data on a daily basis and alerts you when there are changes.  You may not need to do this for all groups, but it would be good to monitor any group that has sysadmin rights such as the BUILTIN\Administrators account.
  • Take a look at these other security related tips:
  • Thanks goes out to Amit Watwe from Singapore for this tip idea as well as the code sample.


Last Update:

First Published: 2007-05-25


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, September 24, 2012 - 1:32:22 AM - Akhtar Back To Top

i execute the same script with the cahge for sqlserver 2008 r2 it display query execute successfully but not showing any record please help me


Friday, March 16, 2012 - 4:59:18 AM - Billy Back To Top

Thanks for this command!

I realize this is an old post, just wanted to say that I've tried it and it returns the users from a domain group successfully. However, it does not display nested groups. i.e groups in my group. Get me? It only returns users, not groups in my group. Any way to include those?

Thanks


Wednesday, March 30, 2011 - 2:16:33 AM - Amit Watwe Back To Top

Hello Jeremy,

Thanks a lot for that tweak! Really appreciate :)

Regards,

Amit


Tuesday, February 01, 2011 - 9:25:16 AM - Jeremy Kadlec Back To Top

 

 

 

Amit,

Thank you for this tip.  When I have run this code on SQL Server 2008 R2 instances, I get this error message:

Msg 15404, Level 16, State 5, Procedure xp_logininfo, Line 42
Could not obtain information about Windows NT group/user 'NT SERVICE\MSSQLSERVER', error code 0x8ac.

Msg 15404, Level 16, State 5, Procedure xp_logininfo, Line 42
Could not obtain information about Windows NT group/user 'NT SERVICE\SQLSERVERAGENT', error code 0x8ac.

As a quick fix, I have changed the SELECT statement in the cursor to the following:

 SELECT [name]
 FROM master.sys.server_principals
 WHERE TYPE = 'G'
 AND [name] NOT IN ('NT SERVICE\MSSQLSERVER', 'NT SERVICE\SQLSERVERAGENT')

I hope this helps.

Thank you,
Jeremy Kadlec

 


Learn more about SQL Server tools