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

 

Tables and Views for Auditing SQL Server Logins


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

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

I have been tasked with auditing security on my SQL Server. However, this needs to be a somewhat automated process as I don't want to have to rely on taking screenshots every month to satisfy our auditors. What tables and/or views should I be using and what's the best way to extract the information out of them?

Solution

The first thing you'll want to audit is the login information to your SQL Server so that's what we'll focus on for this tip. There is one particular table if we're talking about SQL Server 2000 and there are two catalog views (views that contain information like logins, permissions, etc.) within SQL Server 2005 and 2008 that we'll target. First, let's start with SQL Server 2000.


Using syslogins (SQL Server 2000):

The syslogins table contains information on every login that has the potential to access the SQL Server in question. There are a few pieces of information we're always interested in:

  • The name of the login
  • When the login was created
  • When the login was last modified (either the password was changed or one of the settings on the login was)
  • Whether or not the login is a Windows-based user account
  • Whether or not the login is a Windows-based security group

A simple query like the one below will get this information:

SELECT
    
[name]
  
createdate
  
updatedate
  
isntuser
  
isntgroup
FROM syslogins;

Now keep in mind that I did not include when the login was last used. SQL Server does not keep this information stored. If you're auditing for successful logins you'll find it in the SQL Server log or the Application event log for the operating system. That means you'll need to extract it. We see this question fairly frequently and unfortunately, SQL Server, regardless of version, doesn't keep track of it. You'll also see nothing about password enforcement. This wasn't added as a feature to SQL Server until SQL Server 2005. Therefore, a login can attempt to connect to SQL Server as many times as it wants without being locked out. You won't notice something is up unless you are at least logging for audit failures and checking the logs periodically.

There is one other bit of information we're interested in and that's the server roles a particular login may be a member of.  Again, we can go back to syslogins or we can use the stored procedure sp_helpsrvrolemember. An example of both is given to detect who are members of the sysadmin fixed server role.

SELECT
    
[name]
  
sysadmin
FROM syslogins
WHERE sysadmin 1;

And the use of sp_helpsrvrolemember:

EXEC sp_helpsrvrolemember 'sysadmin';

If you need to audit more than just for sysadmin role members, then both syslogins and sp_helpsrvrolemember will do. They will provide information on all fixed server roles.


Using sys.server_principals and sys.sql_logins (SQL Server 2005/2008):

With SQL Server 2005 came better dynamic management and catalog views which provide more information (in general) than in previous versions. In the place of syslogins, we can use sys.server_principals or sys.sql_logins. There is a difference between the two, so let's talk about that. The catalog view sys.server_principals roughly corresponds to syslogins from SQL Server 2000. The catalog view sys.sql_logins provides more information for SQL Server specific logins because of new features in SQL Server 2005 (also included in 2008) such as password expiration and password lockout. I won't go into details here about what those features are, as they were covered in a previous tip.

If we want to return the same information as we did from syslogins, we can by querying sys.server_principals like so:

SELECT 
    
[name]
  
create_date
  
modify_date
  
CASE [type]
      
WHEN 'U' THEN 1
      
ELSE 0
    
END AS 'isntuser'
  
CASE [type]
      
WHEN 'G' THEN 1
      
ELSE 0
    
END AS 'isntgroup'
FROM sys.server_principals;
 

Because SQL Server 2005/2008 support password policy enforcement, there is more information to be gained from sys.sql_logins. A follow-on query from a previous tip is this:

SELECT  
    
[name] 
  
is_policy_checked 
  
is_expiration_checked 
FROM sys.sql_logins
 

Now, with respect to determining server role membership, we can still use sp_helpsrvrolemember, like with SQL Server 2000, but we can't just use sys.server_principals like we could use just syslogins with SQL Server 2000. We'll also have to add in sys.server_role_members. The sp_helpsrvrolemember syntax is the same, so let's just look at the new syntax due to the catalog views:

SELECT 
    
sp1.[name] AS 'login'
  
sp2.[name] AS 'role'
FROM  sys.server_principals sp1
  
JOIN sys.server_role_members srm
    
ON sp1.principal_id srm.member_principal_id
  
JOIN sys.server_principals sp2
    
ON srm.role_principal_id sp2.principal_id
WHERE sp2.[name] 'sysadmin';

In a follow-on tip we'll look at how to take this a step further and look in each database to determine which ones a particular login has access to.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources





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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, December 01, 2014 - 11:19:58 AM - Marian Back To Top

This is great! How about an update for SQL 2012?


Thursday, February 02, 2012 - 6:13:46 AM - Paul Williams Back To Top

Hello,

 

I have you got a link to the follow up artical regarding the databases? I'd love to read it. Thank you, kind regards, Paul.


Sunday, June 13, 2010 - 12:48:58 AM - Barry Back To Top

 Awesome. Thanks for the tip.


Thursday, June 10, 2010 - 2:56:41 PM - K. Brian Kelley Back To Top

[quote user="jesc100"]

The link you suggested does indeed help in that it shows there is way to get to the data I'm interested in! Unfortunately, as often seems to be the way with SQL Server, in this case it provides me with too much information to be very helpful.[/quote]

In a follow-on article to this I actually deal with this issue with a really simple trick. Here's what you do:

  1. Create a temp table that has a column definition matching the output of the stored procedure. Make sure you cover all the columns coming from the stored procedure's result set since you can't filter the columns.
  2. Use an INSERT statement (the EXEC can be the source) to take the contents of the stored procedure and put it into the temp table.
  3. Write a query against the temporary table returning only what you need.
  4. Drop the temp table

 


Thursday, June 10, 2010 - 2:36:55 PM - admin Back To Top

One option is to copy and paste into Excel and then copy and paste into Word.  This will keep the formatting, but it is an extra step.

You could also do the results to Text, but like you said the column widths may be very wide and when you use the system stored procedures there is not much you can do except roll your own or insert the results into a temporary table and then when you select the data from the temporary table you can change the output width.

Hope this helps.


Thursday, June 10, 2010 - 2:03:52 PM - jesc100 Back To Top

Hi Brian, thanks for the quick response and apologies for the delay getting back to you.

The link you suggested does indeed help in that it shows there is way to get to the data I'm interested in! Unfortunately, as often seems to be the way with SQL Server, in this case it provides me with too much information to be very helpful.

I'm administering a MOSS instance with > 10 databases and >10 users. If each user has access to each database, in order for the output of sp_helplogins to be useful, I need to be able to order and filter it - all users for a given database, all databases a given user has access to etc.

 On a related note, even handling/relaying the output (via Word, Outlook etc) is proving to be a bit of a headache. For example, SQL output from an Oracle database can be formatted as follows:

 

Machine : MPPH09VI                             ======================================

Instance : MPPR95                                          Database Files            

G_FIL9                                         ======================================

 

                          extent  segment cont file      TS size  File               

tablespace                managt  managt  ents  cnt       ( Mb )    id Filename      

------------------------- ------- ------- ---- ---- ------------ ----- ---------------

AMP_DATA                  LOCAL    AUTO   PERM    1           10    11
AMP_INDEX                 LOCAL    AUTO   PERM    1            5    12

DATAS_SYSTEM_D            LOCAL    AUTO   PERM    1          200    13
DATAS_SYSTEM_I            LOCAL    AUTO   PERM    1          200    14
DFWEB_DATA                LOCAL    AUTO   PERM    1        1,024     8

 (Not sure how this will turn out in the forum but, in Word and Outlook, it's all column delimited, making it easy to read and easy on the eye).

Using SQL Server's 'Query results to Grid' option either requires the ability to take and manipulate screen shots while 'Select All' | 'Copy with headers' ends up looking like this when pasted:

LoginName DBName UserName UserOrAlias
MPSO-MOSS-I1-MS-U MossI1_CentralAdminContent RESEARCH\MPSO-MOSS-I1-MS-U User   
MPSO-MOSS-I1-MS-U MossI1_CentralAdminContent WSS_Content_Application_Pools MemberOf
MPSO-MOSS-I1-MS-U MossI1_ConfigDB RESEARCH\MPSO-MOSS-I1-MS-U User   
MPSO-MOSS-I1-MS-U MossI1_ConfigDB WSS_Content_Application_Pools MemberOf

Meanwhile the default column width when using 'Query results to text', normally renders the output unreadable when copied/pasted and, despite following a few of the suggestions I've found online, I haven't been able to format the width of a column output.

e.g. for a sample of output where the largest column width is 10 characters, I'd like to limit it to 10-12 instead of having to accept SQL Server's default of, for example, 60 characters just because this is the maximum width the column allows.

I hope that, at least, some of this make sense - am I being too demanding of SQL Server or am I just ignorant of all the the wonderful features it offers and how to use them! ;-)

Look forwad to your comments, 

Jes

 

 


Tuesday, June 08, 2010 - 11:19:58 AM - K. Brian Kelley Back To Top

I am working on a couple of follow-on articles to cover this. I started at the top, logins, and am working my way down. :)

 


Tuesday, June 08, 2010 - 10:05:40 AM - admin Back To Top

Take a look at this tip to see if this gives you what you are looking for:

 http://www.mssqltips.com/tip.asp?tip=1071


Tuesday, June 08, 2010 - 9:12:15 AM - jesc100 Back To Top

Hi.

Firstly thanks for the time you've taken to provide this tip and for the clear manner in which it's delivered.

I'm an Oracle DBA of 10+ years standing now working on a site which also uses SQL Server (2005/2008) and I am becoming more and more involved with it. I've been asked to verify what access a number of OS groups have to the SQL Server instance and various databases across different Sharepoint farms and am looking to script this in a similar way to the way I would in Oracle - unfortunately, without knowing at the moment where most of the data I'm looking for resides!

For each of a given number of OS groups, I want to know what server roles each has, as well as what rights to which databases - in English that sounds very straightforward and, from Mgmt Studio, I can find it - but not via SQL.

Your tip provides some of the information I need, but I'm unsure about how to get the rest - any suggestions?

Thanks in advance,

Jes


Learn more about SQL Server tools