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

 

SQL Server Security Auditing to Match Logins And Users


By:   |   Read Comments (6)   |   Related Tips: 1 | 2 | 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

Audtiting logins is important, because it reveals who can connect to the SQL Server and what permissions they have at the server level. However, the data is at the database level and your auditing should include determining what logins can access what database. Just because a person has a login which can connect to SQL Server, that doesn't mean they have the ability to enter a database. They must be specifically granted permission to do so, with two exceptions, both of which are system databases:

  • master
  • tempdb

Everyone has access to these two databases because the guest user is enabled on both of them. This is a required configuration because there is functionality in both databases that potentially all logins will require access to. Therefore, if an auditor inquires about why the guest user is enabled in either of these databases, it is by design.

So that leaves all the other databases to query in order to determine which logins have access and how they map into those databases. This information is important because you'll eventually use it to determine what permissions a given person has in the database. But first steps first. Let's find a list of databases.


In SQL Server 2000, the following query will pull back all databases, with the exception of master and tempdb. I've intentionally included model, because all new databases will be created from a copy of model. Therefore, it's important to check on model as well.

SELECT name 
FROM sysdatabases
WHERE name NOT IN ('master', 'tempdb');

For SQL Server 2005 and above, we use the sys.databases catalog view:

SELECT name 
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb');

In order to determine what databases a login has access to, each database must be queried individually. There's nothing at the server level which provides all of the information we need. However, we do need to determine what the SID, or security identifier, is for each login, because that's how SQL Server maps a login to a database user.

In SQL Server 2000, we can query syslogins to get the name and the SID:

SELECT name, sid
FROM syslogins;

In SQL Server 2005 and above, we'll use the sys.server_principals catalog view:

SELECT name, sid
FROM sys.server_principals;

Now, when we're talking about matching up the SID, we'll either do so by joining to the sysusers table (SQL Server 2000) or sys.database_principals catalog view (SQL Server 2005+). This can actually be done from the master database, using a three part naming convention ([database].[owner].[object] or [database].[schema].[object]) like so.

Here I've got the AdventureWorks2008 database installed and I'm trying to match up logins at the server level to databases users within the AdventureWorks2008 database:

SELECT l.name AS 'login', u.name AS 'user'
FROM syslogins l
INNER JOIN AdventureWorks2008..sysusers u
ON l.sid = u.sid;

or for 2005+:

SELECT s.name AS 'login', d.name AS 'user'
FROM sys.server_principals s
INNER JOIN AdventureWorks2008.sys.database_principals d
ON s.SID = d.sid;

If we need to audit all access at once, we can build a dynamic SQL string and then execute it. This will give us back a single result with all the databases and all the mappings from login to user.

Here's how to do it in SQL Server 2000:

-- We'll need to build a dynamic SQL string 
DECLARE @TSQL NVARCHAR(4000);

-- What we'll return back and the first table we'll use, syslogins SET @TSQL = 'SELECT Database_Name, l.name AS ''login'', d.name COLLATE DATABASE_DEFAULT as ''user'' FROM syslogins l INNER JOIN (';
-- Building the rest of the T-SQL to run a single query SELECT @TSQL = @TSQL + 'SELECT ''' + name + ''' AS Database_Name, sid, name FROM [' + name + ']..sysusers UNION ALL ' FROM sysdatabases WHERE name NOT IN ('master', 'tempdb');
-- Remove the final UNION ALL and completing the join SET @TSQL = LEFT(@TSQL, LEN(@TSQL) - 10) + ') d ON l.sid = d.sid;';
-- Returning the query EXECUTE sp_executesql @TSQL;

and again in SQL Server 2005 and above:

-- We'll need to build a dynamic SQL string 
DECLARE @TSQL NVARCHAR(MAX);

-- What we'll return back and the first table we'll use, syslogins SET @TSQL = 'SELECT Database_Name, s.name AS ''login'', d.name COLLATE DATABASE_DEFAULT as ''user'' FROM sys.server_principals s INNER JOIN (';
-- Building the rest of the T-SQL to run a single query SELECT @TSQL = @TSQL + 'SELECT ''' + name + ''' AS Database_Name, sid, name FROM [' + name + '].sys.database_principals UNION ALL ' FROM sys.databases WHERE name NOT IN ('master', 'tempdb');
-- Remove the final UNION ALL and completing the join SET @TSQL = LEFT(@TSQL, LEN(@TSQL) - 10) + ') d ON s.sid = d.sid;';
-- Returning the query EXECUTE sp_executesql @TSQL;
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





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     



Wednesday, October 20, 2010 - 8:26:20 AM - Mike Casey Back To Top
You might want to consider using the quotename() function to wrap object names.


Wednesday, August 25, 2010 - 12:09:31 PM - philhege Back To Top
Brian, I have the same requirement from Audit, and I thought it strange that they required screen shots.  So I opened a Profiler session and grabbed as much of the SSMS code as I could, wrapped it in MSForEachDB as necessary, and exported the results to a multi-tabbed spreadsheet. I provided Audit with the script so they could send it to administrators along with their request. Now they have a comprehensive administrative data picture, and application DBAs don't have to do annoying screen captures.


Wednesday, August 25, 2010 - 9:39:16 AM - Caine Schneider Back To Top
Should modify the where clause so it doesn't try to include databases that are not online:

WHERE name NOT IN ('master', 'tempdb')
  AND state_desc = 'ONLINE';


Wednesday, August 25, 2010 - 9:35:42 AM - Chad Miller Back To Top
Be careful of finding mismatched users/logins in SQL 2008 and higher. In 2005+ you can create users without logins and Microsoft's own products like SSRS 2008 do this for certificates (a user without a login will own a certificate). The user type will be 'C' and these should be filtered out.

See this blog post for more details http://blogs.msdn.com/b/raulga/archive/2006/07/03/655587.aspx


Sunday, August 15, 2010 - 6:59:16 AM - Serve Technology Back To Top
Sql is very po***r structured query language.

___________________________________________

samual123

Serve Technology


Friday, August 13, 2010 - 6:02:23 AM - Santanu Kumar Raul Back To Top
I have a column where I have words which contains single underscore,double underscores,triple underscore(e.g.abc_ssd_kkt,abc_ppf,abc_dde_hhj_ksd, abc is common for every word).

How can I count the number of underscores present in each word.

Say Single Underscore- Level1,Double Underscore- Level2,Triple Underscore- Level3,Four Underscore- Level4

 

I want count them by group.

 

 


Learn more about SQL Server tools