SQL Server Security Auditing to Match Logins And Users in SQL Server

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;

Source colleted from MSSQLTIPS.COM