Script to auto generate a security report for your SQL Server instance

By:   |   Comments (25)   |   Related: > Auditing and Compliance


Problem

A few of my clients have been asking me if I have a template for a security report they can send to their security auditors. I always prepare this report manually for them, using system procedures such as sp_helplogin, sp_helpuser, etc, and then format the data in a Word document  (a template I have created for that matter). This is a bit of a tedious process that takes me a few hours to complete if the environment is complex (security wise).

For that reason, I wanted to create a Stored Procedure that will not only fetch the data, but also provide a formatted report so that my clients will be able to run the report themselves and send it directly to the auditors.

Solution

In this tip I provide a Stored Procedure that was tested in SQL Server 2005 and generates all required security information.

The output generates html, so the text output of the Stored Procedure can be saved as an .html file and opened directly with an Internet Browser. No additional processing (such as creating or formatting a document) is required.

The Stored Procedure:

-- Srored Procedure to generate Security Audit report in HTML format:

CREATE PROC spAuditUsersPermissions
AS
SET NOCOUNT ON

DECLARE @sql VARCHAR(MAX)
DECLARE @strHTML VARCHAR(MAX)
DECLARE @i INT
DECLARE @rc INT
DECLARE @dbname VARCHAR(400)

-----------------Print header of the report--------------------

SELECT @strHTML = '<HTML><HEAD><TITLE> Security list for the auditor </TITLE><STYLE>TD.Sub{FONT-WEIGHT:bold;BORDER-BOTTOM: 0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: 0pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} BODY{FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} TABLE{BORDER-BOTTOM: 1pt solid #000000;BORDER-LEFT: 0pt solid #000000;BORDER-RIGHT: 1pt solid #000000;BORDER-TOP: 0pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} TD{BORDER-BOTTOM: 0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: 1pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} TD.Title{FONT-WEIGHT:bold;BORDER-BOTTOM: 0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: 1pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 12pt} A.Index{FONT-WEIGHT:bold;FONT-SIZE:8pt;COLOR:#000099;FONT-FAMILY:Tahoma;TEXT-DECORATION:none} A.Index:HOVER{FONT-WEIGHT:bold;FONT-SIZE:8pt;COLOR:#990000;FONT-FAMILY:Tahoma;TEXT-DECORATION:none}</STYLE></HEAD><BODY><A NAME="_top"></A><BR>'
PRINT @strHTML

-----------------Login information-------------------------------------------------------------

SELECT ROW_NUMBER () OVER (ORDER BY name) AS RowNumber,
name, dbname,language, 
CONVERT(CHAR(10),CASE denylogin WHEN 1 THEN 'X' ELSE '--' END) AS IsDenied,
CONVERT(CHAR(10),CASE isntname WHEN 1 THEN 'X' ELSE '--' END) AS IsWinAuTHENtication,
CONVERT(CHAR(10),CASE isntgroup WHEN 1 THEN 'X' ELSE '--' END) AS IsWinGroup,
createdate,UPDATEdate,
CONVERT(VARCHAR(2000),
CASE sysadmin WHEN 1 THEN 'sysadmin,' ELSE '' END +
CASE securityadmin WHEN 1 THEN 'securityadmin,' ELSE '' END +
CASE serveradmin WHEN 1 THEN 'serveradmin,' ELSE '' END +
CASE setupadmin WHEN 1 THEN 'setupadmin,' ELSE '' END +
CASE processadmin WHEN 1 THEN 'processadmin,' ELSE '' END +
CASE diskadmin WHEN 1 THEN 'diskadmin,' ELSE '' END +
CASE dbcreator WHEN 1 THEN 'dbcreator,' ELSE '' END +
CASE bulkadmin WHEN 1 THEN 'bulkadmin' ELSE '' END ) AS ServerRoles
INTO #syslogins
FROM master..syslogins WITH (nolock)
ORDER BY name

SET @rc = @@rowcount

SELECT @strHTML = '<BR><CENTER><FONT SIZE="5"><B> Server ' + @@servername + '</B></FONT></CENTER><BR>'
PRINT @strHTML
PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="100%">'

-- Query the data only if there are rows:
IF @rc = 0
BEGIN
   PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="1" ALIGN="center"><B><A NAME="_LoginInfomration">Logins information</A></B> </TD></TR>'
   PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>There are no logins on this server</B> </TD></TR>'
END
ELSE
BEGIN
   UPDATE #syslogins
   SET ServerRoles = SUBSTRING(ServerRoles,1,LEN(ServerRoles)-1)
   WHERE SUBSTRING(ServerRoles,LEN(ServerRoles),1) = ','

   UPDATE #syslogins SET ServerRoles = '--'
   WHERE LTRIM(RTRIM(ServerRoles)) = ''

   PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="100%">'
   PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="9" ALIGN="center"><B><A NAME="_LoginInfomration">Logins information</A></B> </TD></TR>'
   PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="50%"><B>Login Name</B> </TD><TD ALIGN="left" WIDTH="50%"><B>Default DB</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Language</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Denied acess?</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Windows Auth?</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Window group?</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Date created</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Date UPDATEd</B> </TD><TD AALIGN="left" WIDTH="770%"><B>Server roles</B> </TD></TR>'

   SET @i = 1
   WHILE @i <= @rc
   BEGIN
       SELECT @strHTML = 
       '<TR><TD><B>' + CONVERT(VARCHAR(50),name) + '</B> </TD>' + 
       '<TD>' + CONVERT(VARCHAR(50),CASE ISNULL(dbname,'--') WHEN '' THEN '--' ELSE ISNULL(dbname,'--') END) + ' </TD>' +
       '<TD>' + CONVERT(VARCHAR(50),ISNULL(language,'--')) + ' </TD>' +
       '<TD>' + CONVERT(VARCHAR(10),ISNULL(IsDenied,'--')) + ' </TD>' +
       '<TD>' + CONVERT(VARCHAR(10),ISNULL(IsWinAuTHENtication,'--')) + ' </TD>' +
       '<TD>' + CONVERT(VARCHAR(10),ISNULL(IsWinGroup,'--')) + ' </TD>' +
       '<TD>' + CONVERT(VARCHAR(30),ISNULL(createdate,'--')) + ' </TD>' +
       '<TD>' + CONVERT(VARCHAR(30),ISNULL(UPDATEdate,'--')) + ' </TD>' +
       '<TD>' + CONVERT(VARCHAR(100),ISNULL(ServerRoles,'--')) + ' </TD>' +
       '</TR>'
       FROM #syslogins
       WHERE RowNumber = @i

       PRINT @strHTML

       SET @i = @i + 1
   END

   PRINT '</TABLE></DIV><BR><A CLASS="Index" HREF="#_top">Back To Top ^</A><BR><BR>'
   PRINT'<BR><CENTER></CENTER><BR>'
END

DROP TABLE #syslogins

---------------Fetch data per database-------------------------------------------------

CREATE TABLE #LoginMap (LoginName VARCHAR(200), UserName VARCHAR(200) NULL)

CREATE TABLE #RoleUser (RoLEName VARCHAR(200), UserName VARCHAR(200) NULL)

CREATE TABLE #ObjectPerms (RowNumber INT IDENTITY, UserName VARCHAR(50), PerType VARCHAR(10),PermName VARCHAR(30), SchemaName VARCHAR(50), 
ObjectName VARCHAR(100), ObjectType VARCHAR(20), ColName VARCHAR(50), IsGrantOption VARCHAR(10))

CREATE TABLE #DatabasePerms (RowNumber INT IDENTITY,UserName VARCHAR(50),PermType VARCHAR(20),PermName VARCHAR(50),IsGrantOption VARCHAR(5))

DECLARE dbs CURSOR FOR SELECT name FROM master..sysdatabases ORDER BY name

OPEN dbs
FETCH NEXT FROM dbs INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
   TRUNCATE TABLE #LoginMap
   TRUNCATE TABLE #RoleUser
   TRUNCATE TABLE #ObjectPerms 
   TRUNCATE TABLE #DatabasePerms
   SELECT @strHTML = '<BR><CENTER><FONT SIZE="5"><B> Database ' + @dbname + '</B></FONT></CENTER><BR>'
   PRINT @strHTML

-----------------Mapping of logins to users------------------
   EXEC('
INSERT INTO #LoginMap
SELECT login.loginname,users.name 
FROM ['+ @dbname+'].dbo.sysusers users 
INNER JOIN [master].[dbo].[syslogins] login 
ON users.[sid] = login.[sid]
WHERE users.uid < 16382 
and users.name not in (''public'',''dbo'',''guest'')
')

   SET @strHTML = ''

   PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%">'

   --Query the data only if there are rows
   IF NOT EXISTS (SELECT 1 FROM #LoginMap)
   BEGIN
       PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="1" ALIGN="center"><B><A NAME="_LoginMapping">Mapping of logins to users</A></B> </TD></TR>'
       PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>There are no mappings in this database</B> </TD></TR>'
   END 
   ELSE
   BEGIN
       PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="2" ALIGN="center"><B><A NAME="_LoginMapping">Mapping of logins to users</A></B> </TD></TR>'
       PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>Login Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>User Name</B> </TD></TR>'

       SELECT @strHTML = @strHTML + 
       '<TR><TD><B>' + CONVERT(VARCHAR(50),LoginName) + '</B> </TD><TD>' + CONVERT(VARCHAR(50),ISNULL(UserName,'')) + ' </TD></TR>' + CHAR(10)
       FROM #LoginMap
       ORDER BY LoginName

       PRINT @strHTML
   END

   PRINT '</TABLE></DIV><BR><A CLASS="Index" HREF="#_top">Back To Top ^</A><BR><BR>'

   ----------------SQL roles per user------------------
   EXEC ('INSERT INTO #RoleUser
SELECT b.name AS Role_name, a.name AS User_name ' + 
   'FROM ['+ @dbname+']..sysusers a ' +
   'INNER JOIN ['+ @dbname+ ']..sysmembers c on a.uid = c.memberuid ' +
   'INNER JOIN ['+ @dbname+ ']..sysusers b ON c.groupuid = b.uid ' + 
   'WHERE a.name <> ''dbo'''
   ) 

   SET @strHTML = ''

   PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%">'

   -- Query the data only if there are rows:

   IF NOT EXISTS(SELECT 1 FROM #RoleUser)
   BEGIN
       PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="1" ALIGN="center"><B><A NAME="_DBRoleMapping">Roles per user</A></B> </TD></TR>'
       PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>There are no users mapped to roles in this database</B> </TD></TR>'
   END 
   ELSE
   BEGIN
       PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="2" ALIGN="center"><B><A NAME="_DBRoleMapping">Roles per user</A></B> </TD></TR>'
       PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>Role Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>User Name</B> </TD></TR>'

       SELECT @strHTML = @strHTML + 
       '<TR><TD><B>' + CONVERT(VARCHAR(50),RoLEName) + '</B> </TD><TD>' + CONVERT(VARCHAR(50),ISNULL(UserName,'')) + ' </TD></TR>' + CHAR(10)
       FROM #RoleUser
       ORDER BY RoLEName

       PRINT @strHTML
   END
   
   PRINT '</TABLE></DIV><BR><A CLASS="Index" HREF="#_top">Back To Top ^</A><BR><BR>'

----------------Database level Permissions-------------------------

   EXEC ('INSERT INTO #DatabasePerms
(UserName,PermType,PermName,IsGrantOption)
SELECT usr.name,
CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END,
perm.permission_name, 
CASE WHEN perm.state != ''W'' THEN ''--'' ELSE ''X'' END AS IsGrantOption
FROM ['+@dbname+'].sys.database_permissions AS perm
INNER JOIN
['+@dbname+'].sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE perm.major_id = 0
ORDER BY usr.name, perm.permission_name ASC, perm.state_desc ASC'
   )

   SET @rc = @@rowcount

   PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%">'

   -- Query the data only if there are rows:
   
   IF NOT EXISTS(SELECT 1 FROM #DatabasePerms)
   BEGIN
       PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="1" ALIGN="center"><B><A NAME="_DBLvlPerms">Database level permissions</A></B> </TD></TR>'
       PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>There are no specific permissions on the database level</B> </TD></TR>'
   END 
   ELSE
   BEGIN
       PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN=" 4" ALIGN="center"><B><A NAME="_DBPObjPerms">Database level permissions</A></B> </TD></TR>'
       PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>User Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Permission type</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Permission Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Grant option?</B> </TD></TR>'
       
       SET @i = 1
       WHILE @i <= @rc
       BEGIN
           SELECT @strHTML = 
           '<TR><TD><B>' + CONVERT(VARCHAR(50),UserName) + '</B> </TD>' + 
           '<TD>' + CONVERT(VARCHAR(50),ISNULL(PermType,'--')) + ' </TD>' +
           '<TD>' + CONVERT(VARCHAR(50),ISNULL(PermName,'--')) + ' </TD>' +
           '<TD>' + CONVERT(VARCHAR(5),ISNULL(IsGrantOption,'--')) + ' </TD>'+
           '</TR>'
           FROM #DatabasePerms
           WHERE Rownumber = @i

           PRINT @strHTML
       
           SET @i = @i + 1
       END
   END

   PRINT '</TABLE></DIV><BR><A CLASS="Index" HREF="#_top">Back To Top ^</A><BR><BR>'

----------------Database object Permissions-------------------------
   EXEC ('INSERT INTO #ObjectPerms
(UserName,PerType,PermName,SchemaName,ObjectName,ObjectType,ColName,IsGrantOption)
SELECT usr.name AS UserName,
CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END AS PerType,
perm.permission_name,USER_NAME(obj.schema_id) AS SchemaName, obj.name AS ObjectName,
CASE obj.Type 
WHEN ''U'' THEN ''Table''
WHEN ''V'' THEN ''View''
WHEN ''P'' THEN ''Stored Proc''
WHEN ''FN'' THEN ''Function''
ELSE obj.Type END AS ObjectType,
CASE WHEN cl.column_id IS NULL THEN ''--'' ELSE cl.name END AS ColName,
CASE WHEN perm.state = ''W'' THEN ''X'' ELSE ''--'' END AS IsGrantOption
FROM ['+@dbname+'].sys.database_permissions AS perm
INNER JOIN
['+@dbname+'].sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
['+@dbname+'].sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
['+@dbname+'].sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHERE obj.Type <> ''S'' 
ORDER BY usr.name, perm.state_desc ASC, perm.permission_name ASC'
   )

   SET @rc = @@rowcount

   PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%">'
   
   -- Query the data only if there are rows:
   IF NOT EXISTS(SELECT 1 FROM #ObjectPerms)
   BEGIN
       PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="1" ALIGN="center"><B><A NAME="_DBPObjPerms">Object permissions</A></B> </TD></TR>'
       PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>There are no specific permissions to objects in this database</B> </TD></TR>'
   END 
   ELSE
   BEGIN
       PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="8" ALIGN="center"><B><A NAME="_DBPObjPerms">Object permissions</A></B> </TD></TR>'
       PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>User Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Permission type</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Permission Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Schema Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Object Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Object type type</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Column Name</B> </TD><TD ALIGN=" left" WIDTH="70%"><B>Grant option?</B> </TD></TR>'
   
       SET @i = 1
       WHILE @i <= @rc
       BEGIN
           SELECT @strHTML = 
           '<TR><TD><B>' + CONVERT(VARCHAR(50),UserName) + '</B> </TD>' + 
           '<TD>' + CONVERT(VARCHAR(50),ISNULL(PerType,'--')) + ' </TD>' +
           '<TD>' + CONVERT(VARCHAR(50),ISNULL(PermName,'--')) + ' </TD>' +
           '<TD>' + CONVERT(VARCHAR(50),ISNULL(SchemaName,'--')) + ' </TD>' +
           '<TD>' + CONVERT(VARCHAR(50),ISNULL(ObjectName,'--')) + ' </TD>' +
           '<TD>' + CONVERT(VARCHAR(30),ISNULL(ObjectType,'--')) + ' </TD>' +
           '<TD>' + CONVERT(VARCHAR(50),ISNULL(ColName,'--')) + ' </TD>' +
           '<TD>' + CONVERT(VARCHAR(5),ISNULL(IsGrantOption,'--')) + ' </TD></TR>'
           FROM #ObjectPerms
           WHERE Rownumber = @i

           PRINT @strHTML

           SET @i = @i + 1
       END
   END

   PRINT '</TABLE></DIV><BR><A CLASS="Index" HREF="#_top">Back To Top ^</A><BR><BR>'

   FETCH NEXT FROM dbs INTO @dbname

END

---------------Close cursor and drop all temporary objects-------------

CLOSE dbs
DEALLOCATE dbs

DROP TABLE #LoginMap
DROP TABLE #RoleUser
DROP TABLE #ObjectPerms
DROP TABLE #DatabasePerms

PRINT '</BODY></HTML>'

GO  

Example result (HTML format)

The following result is taken from my Test server. I generated a report for only one database, for simplicity. If I generate this report for more than one database, each database will have it's own section:

<HTML><HEAD><TITLE> Security list for the auditor </TITLE><STYLE>TD.Sub{FONT-WEIGHT:bold;BORDER-BOTTOM: 0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: 0pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} BODY{FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} TABLE{BORDER-BOTTOM: 1pt solid #000000;BORDER-LEFT: 0pt solid #000000;BORDER-RIGHT: 1pt solid #000000;BORDER-TOP: 0pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} TD{BORDER-BOTTOM: 0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: 1pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} TD.Title{FONT-WEIGHT:bold;BORDER-BOTTOM: 0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: 1pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 12pt} A.Index{FONT-WEIGHT:bold;FONT-SIZE:8pt;COLOR:#000099;FONT-FAMILY:Tahoma;TEXT-DECORATION:none} A.Index:HOVER{FONT-WEIGHT:bold;FONT-SIZE:8pt;COLOR:#990000;FONT-FAMILY:Tahoma;TEXT-DECORATION:none}</STYLE></HEAD><BODY><A NAME="_top"></A><BR>
<BR><CENTER><FONT SIZE="5"><B> Server MyServer\SQL_2005</B></FONT></CENTER><BR>
<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="100%">
<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="100%">
<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="9" ALIGN="center"><B><A NAME="_LoginInfomration">Logins information</A></B> </TD></TR>
<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="50%"><B>Login Name</B> </TD><TD ALIGN="left" WIDTH="50%"><B>Default DB</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Language</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Denied acess?</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Windows Auth?</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Window group?</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Date created</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Date updated</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Server roles</B> </TD></TR>
<TR><TD><B>##MS_AgentSigningCertificate##</B> </TD><TD>master </TD><TD>us_english </TD><TD>--         </TD><TD>--         </TD><TD>--         </TD><TD>Jan 16 2009  2:32PM </TD><TD>Jan 16 2009  2:32PM </TD><TD>-- </TD></TR>
<TR><TD><B>##MS_SQLAuthenticatorCertificate##</B> </TD><TD>master </TD><TD>-- </TD><TD>--         </TD><TD>--         </TD><TD>--         </TD><TD>Jan 16 2009  2:31PM </TD><TD>Jan 16 2009  2:31PM </TD><TD>-- </TD></TR>
<TR><TD><B>##MS_SQLReplicationSigningCertificate##</B> </TD><TD>master </TD><TD>-- </TD><TD>--         </TD><TD>--         </TD><TD>--         </TD><TD>Jan 16 2009  2:31PM </TD><TD>Jan 16 2009  2:31PM </TD><TD>-- </TD></TR>
<TR><TD><B>##MS_SQLResourceSigningCertificate##</B> </TD><TD>master </TD><TD>-- </TD><TD>--         </TD><TD>--         </TD><TD>--         </TD><TD>Jan 16 2009  2:31PM </TD><TD>Jan 16 2009  2:31PM </TD><TD>-- </TD></TR>
<TR><TD><B>MyServer\Michelle2</B> </TD><TD>Michelle </TD><TD>us_english </TD><TD>--         </TD><TD>X          </TD><TD>--         </TD><TD>May 30 2009  3:06PM </TD><TD>May 30 2009  3:06PM </TD><TD>securityadmin </TD></TR>
<TR><TD><B>MyServer\SQLServer2005MSFTEUser$MyServer</B> </TD><TD>master </TD><TD>us_english </TD><TD>--         </TD><TD>X          </TD><TD>X          </TD><TD>Jan 16 2009  2:18PM </TD><TD>Jan 16 2009  2:18PM </TD><TD>-- </TD></TR>
<TR><TD><B>MyServer\SQLServer2005MSSQLUser$MyServer</B> </TD><TD>master </TD><TD>us_english </TD><TD>--         </TD><TD>X          </TD><TD>X          </TD><TD>Jan 16 2009  2:18PM </TD><TD>Jan 16 2009  2:18PM </TD><TD>sysadmin </TD></TR>
<TR><TD><B>MyServer\SQLServer2005SQLAgentUser$MyServer</B> </TD><TD>master </TD><TD>us_english </TD><TD>--         </TD><TD>X          </TD><TD>X          </TD><TD>Jan 16 2009  2:18PM </TD><TD>Jan 16 2009  2:18PM </TD><TD>sysadmin </TD></TR>
<TR><TD><B>MyServer\TestGroup</B> </TD><TD>Michelle </TD><TD>us_english </TD><TD>--         </TD><TD>X          </TD><TD>X          </TD><TD>May 30 2009  3:08PM </TD><TD>Aug  9 2009 11:49PM </TD><TD>diskadmin,dbcreator </TD></TR>
<TR><TD><B>MyServer\User</B> </TD><TD>master </TD><TD>us_english </TD><TD>--         </TD><TD>X          </TD><TD>--         </TD><TD>Jun 29 2009  1:20PM </TD><TD>Jun 29 2009  1:20PM </TD><TD>sysadmin </TD></TR>
<TR><TD><B>BUILTIN\Administrators</B> </TD><TD>master </TD><TD>us_english </TD><TD>--         </TD><TD>X          </TD><TD>X          </TD><TD>Jan 16 2009  2:18PM </TD><TD>Jan 16 2009  2:18PM </TD><TD>sysadmin </TD></TR>
<TR><TD><B>NT AUTHORITY\SYSTEM</B> </TD><TD>master </TD><TD>us_english </TD><TD>--         </TD><TD>X          </TD><TD>--         </TD><TD>Jan 16 2009  2:18PM </TD><TD>Jan 16 2009  2:18PM </TD><TD>sysadmin </TD></TR>
<TR><TD><B>sa</B> </TD><TD>master </TD><TD>us_english </TD><TD>--         </TD><TD>--         </TD><TD>--         </TD><TD>Apr  8 2003  9:10AM </TD><TD>Jan 16 2009  2:18PM </TD><TD>sysadmin </TD></TR>
</TABLE></DIV><BR><A CLASS="Index" HREF="#_top">Back To Top ^</A><BR><BR>
<BR><CENTER></CENTER><BR>
<BR><CENTER><FONT SIZE="5"><B> Database test</B></FONT></CENTER><BR>
<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%">
<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="2" ALIGN="center"><B><A NAME="_LoginMapping">Mapping of logins to users</A></B> </TD></TR>
<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>Login Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>User Name</B> </TD></TR>
<TR><TD><B>MyServer\Michelle2</B> </TD><TD>MyServer\Michelle2 </TD></TR>
<TR><TD><B>MyServer\TestGroup</B> </TD><TD>MyServer\TestGroup </TD></TR>

</TABLE></DIV><BR><A CLASS="Index" HREF="#_top">Back To Top ^</A><BR><BR>
<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%">
<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="2" ALIGN="center"><B><A NAME="_DBRoleMapping">Roles per user</A></B> </TD></TR>
<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>Role Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>User Name</B> </TD></TR>
<TR><TD><B>db_datareader</B> </TD><TD>MyServer\TestGroup </TD></TR>
<TR><TD><B>db_denydatawriter</B> </TD><TD>MyServer\TestGroup </TD></TR>

</TABLE></DIV><BR><A CLASS="Index" HREF="#_top">Back To Top ^</A><BR><BR>
<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%">
<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="4" ALIGN="center"><B><A NAME="_DBPObjPerms">Database level permissions</A></B> </TD></TR>
<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>User Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Permission type</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Permission Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Grant option?</B> </TD></TR>
<TR><TD><B>MyServer\Michelle2</B> </TD><TD>GRANT </TD><TD>CONNECT </TD><TD>-- </TD></TR>
<TR><TD><B>MyServer\TestGroup</B> </TD><TD>GRANT </TD><TD>CONNECT </TD><TD>-- </TD></TR>
<TR><TD><B>dbo</B> </TD><TD>GRANT </TD><TD>CONNECT </TD><TD>-- </TD></TR>
</TABLE></DIV><BR><A CLASS="Index" HREF="#_top">Back To Top ^</A><BR><BR>
<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%">
<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="8" ALIGN="center"><B><A NAME="_DBPObjPerms">Object permissions</A></B> </TD></TR>
<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>User Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Permission type</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Permission Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Schema Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Object Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Object type type</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Column Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Grant option?</B> </TD></TR>
<TR><TD><B>MyServer\Michelle2</B> </TD><TD>DENY </TD><TD>DELETE </TD><TD>dbo </TD><TD>Database_Info </TD><TD>Table </TD><TD>-- </TD><TD>-- </TD></TR>
<TR><TD><B>MyServer\Michelle2</B> </TD><TD>DENY </TD><TD>VIEW DEFINITION </TD><TD>dbo </TD><TD>MyTestProc </TD><TD>Stored Proc </TD><TD>-- </TD><TD>-- </TD></TR>
<TR><TD><B>MyServer\Michelle2</B> </TD><TD>GRANT </TD><TD>EXECUTE </TD><TD>dbo </TD><TD>MyTestProc </TD><TD>Stored Proc </TD><TD>-- </TD><TD>-- </TD></TR>
<TR><TD><B>MyServer\Michelle2</B> </TD><TD>GRANT </TD><TD>SELECT </TD><TD>dbo </TD><TD>Database_Info </TD><TD>Table </TD><TD>-- </TD><TD>-- </TD></TR>
<TR><TD><B>MyServer\Michelle2</B> </TD><TD>GRANT </TD><TD>DELETE </TD><TD>dbo </TD><TD>Backup_Information </TD><TD>Table </TD><TD>-- </TD><TD>X </TD></TR>
<TR><TD><B>MyServer\Michelle2</B> </TD><TD>GRANT </TD><TD>INSERT </TD><TD>dbo </TD><TD>Backup_Information </TD><TD>Table </TD><TD>-- </TD><TD>X </TD></TR>
<TR><TD><B>MyServer\TestGroup</B> </TD><TD>DENY </TD><TD>DELETE </TD><TD>dbo </TD><TD>tbl_Servers </TD><TD>Table </TD><TD>-- </TD><TD>-- </TD></TR>
<TR><TD><B>MyServer\TestGroup</B> </TD><TD>DENY </TD><TD>SELECT </TD><TD>dbo </TD><TD>SQL_Servers </TD><TD>Table </TD><TD>-- </TD><TD>-- </TD></TR>
<TR><TD><B>MyServer\TestGroup</B> </TD><TD>GRANT </TD><TD>ALTER </TD><TD>dbo </TD><TD>tbl_Servers </TD><TD>Table </TD><TD>-- </TD><TD>-- </TD></TR>
<TR><TD><B>MyServer\TestGroup</B> </TD><TD>GRANT </TD><TD>DELETE </TD><TD>dbo </TD><TD>Backup_Information </TD><TD>Table </TD><TD>-- </TD><TD>-- </TD></TR>
<TR><TD><B>MyServer\ TestGroup</B> </TD><TD>GRANT </TD><TD>INSERT </TD><TD>dbo </TD><TD>Backup_Information </TD><TD>Table </TD><TD>-- </TD><TD>-- </TD></TR>
<TR><TD><B>MyServer\TestGroup</B> </TD><TD>GRANT </TD><TD>INSERT </TD><TD>dbo </TD><TD>tbl_Servers </TD><TD>Table </TD><TD>-- </TD><TD>-- </TD></TR>
<TR><TD><B>MyServer\TestGroup</B> </TD><TD>GRANT </TD><TD>SELECT </TD><TD>dbo </TD><TD>tbl_Servers </TD><TD>Table </TD><TD>-- </TD><TD>-- </TD></TR>
<TR><TD><B>MyServer\TestGroup</B> </TD><TD>GRANT </TD><TD>SELECT </TD><TD>dbo </TD><TD>Backup_Information </TD><TD>Table </TD><TD>-- </TD><TD>-- </TD></TR>
</TABLE></DIV><BR><A CLASS="Index" HREF="#_top">Back To Top ^</A><BR><BR>

Sample Report

Save the result from the query above in an .html file and open it with an Internet Browser.

The report from the Browser will look something like this, but should have information for all of your databases.

Server MyServer\SQL_2005
 
Logins information
Login Name Default DB Language Denied acess? Windows Auth? Window group? Date created Date updated Server roles
##MS_AgentSigningCertificate## master us_english -- -- -- Jan 16 2009 2:32PM Jan 16 2009 2:32PM --
##MS_SQLAuthenticatorCertificate## master -- -- -- -- Jan 16 2009 2:31PM Jan 16 2009 2:31PM --
##MS_SQLReplicationSigningCertificate## master -- -- -- -- Jan 16 2009 2:31PM Jan 16 2009 2:31PM --
##MS_SQLResourceSigningCertificate## master -- -- -- -- Jan 16 2009 2:31PM Jan 16 2009 2:31PM --
MyServer\Michelle2 Michelle us_english -- X -- May 30 2009 3:06PM May 30 2009 3:06PM securityadmin
MyServer\SQLServer2005MSFTEUser
$MyServer 		master us_english -- X X Jan 16 2009 2:18PM Jan 16 2009 2:18PM --
MyServer\SQLServer2005MSSQLUser
$MyServer 		master us_english -- X X Jan 16 2009 2:18PM Jan 16 2009 2:18PM sysadmin
MyServer\SQLServer2005SQLAgentUser
$MyServer		 master us_english -- X X Jan 16 2009 2:18PM Jan 16 2009 2:18PM sysadmin
MyServer\TestGroup Michelle us_english -- X X May 30 2009 3:08PM Aug 9 2009 11:49PM diskadmin, dbcreator
MyServer\User master us_english -- X -- Jun 29 2009 1:20PM Jun 29 2009 1:20PM sysadmin
BUILTIN\Administrators master us_english -- X X Jan 16 2009 2:18PM Jan 16 2009 2:18PM sysadmin
NT AUTHORITY\SYSTEM master us_english -- X -- Jan 16 2009 2:18PM Jan 16 2009 2:18PM sysadmin
sa master us_english -- -- -- Apr 8 2003 9:10AM Jan 16 2009 2:18PM sysadmin


Database test
Mapping of logins to users
Login Name User Name
MyServer\Michelle2 MyServer\Michelle2
MyServer\TestGroup MyServer\TestGroup


Roles per user
Role Name User Name
db_datareader MyServer\TestGroup
db_denydatawriter MyServer\TestGroup


Database level permissions
User Name Permission type Permission Name Grant option?
MyServer\Michelle2 GRANT CONNECT --
MyServer\TestGroup GRANT CONNECT --
dbo GRANT CONNECT --


Object permissions
User Name Permission type Permission Name Schema Name Object Name Object type type Column Name Grant option?
MyServer\Michelle2 DENY DELETE dbo Database_Info Table -- --
MyServer\Michelle2 DENY VIEW DEFINITION dbo MyTestProc Stored Proc -- --
MyServer\Michelle2 GRANT EXECUTE dbo MyTestProc Stored Proc -- --
MyServer\Michelle2 GRANT SELECT dbo Database_Info Table -- --
MyServer\Michelle2 GRANT DELETE dbo Backup_Information Table -- X
MyServer\Michelle2 GRANT INSERT dbo Backup_Information Table -- X
MyServer\TestGroup DENY DELETE dbo tbl_Servers Table -- --
MyServer\TestGroup DENY SELECT dbo SQL_Servers Table -- --
MyServer\TestGroup GRANT ALTER dbo tbl_Servers Table -- --
MyServer\TestGroup GRANT DELETE dbo Backup_Information Table -- --
MyServer\TestGroup GRANT INSERT dbo Backup_Information Table -- --
MyServer\TestGroup GRANT INSERT dbo tbl_Servers Table -- --
MyServer\TestGroup GRANT SELECT dbo tbl_Servers Table -- --
MyServer\TestGroup GRANT SELECT dbo Backup_Information Table -- --
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Michelle Gutzait Michelle Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Friday, November 18, 2016 - 8:51:20 AM - Greg Robidoux Back To Top (43800)

After you create the stored procedure you need to execute the following command which will run the stored procedure:

spAuditUsersPermissions

-Greg

Friday, November 18, 2016 - 4:16:17 AM - Elliott Back To Top (43798)

 Hi,

 

when I run the command the only message I get is "command(s) completed successfully.

 

there is no other text to copy and paste?

 

thanks,

Elliott

 

Thursday, November 17, 2016 - 12:07:28 PM - Michellr Back To Top (43790)

Hi Elliott,

You can  copy the text that the SP returns from the grid (without the header), save it into an html file (I. E.: open notepad, paste text and save as .html not as .txt). Open html file from an Internet browser.

Let me know how it went!

 

Michelle.

 

Thursday, November 17, 2016 - 9:11:55 AM - Greg Robidoux Back To Top (43782)

Hi Elliott,

the output should show on the Messages tab when you run the query.

-Greg

Thursday, November 17, 2016 - 4:38:41 AM - Elliott Back To Top (43779)

 Hi,

Stupid question, but where is the output file saved to? I run the command it game me the message command completed successfully but I cant find the file to open the results?

 

thanks,

 

Monday, June 9, 2014 - 7:22:05 AM - Ricky Lively Back To Top (32150)

/*
Script to auto generate a security report for your SQL Server instance
Written By: Michelle Gutzait -- 8/18/2009
http://www.mssqltips.com/sqlservertip/1818/script-to-auto-generate-a-security-report-for-your-sql-server-instance/

-- 20140601 -- add disabled login
-- 20140601 -- List all Server Permissions
*/
-- Stored Procedure to generate Security Audit report in HTML format:

CREATE PROC spAuditUsersPermissions
AS
SET NOCOUNT ON

DECLARE @sql VARCHAR(MAX)
DECLARE @strHTML VARCHAR(MAX)
DECLARE @i INT
DECLARE @rc INT
DECLARE @dbname VARCHAR(400)

-----------------Print header of the report--------------------

SELECT @strHTML = '<HTML><HEAD><TITLE> Security list for the auditor </TITLE><STYLE>TD.Sub{FONT-WEIGHT:bold;BORDER-BOTTOM: 0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: 0pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} BODY{FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} TABLE{BORDER-BOTTOM: 1pt solid #000000;BORDER-LEFT: 0pt solid #000000;BORDER-RIGHT: 1pt solid #000000;BORDER-TOP: 0pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} TD{BORDER-BOTTOM: 0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: 1pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 8pt} TD.Title{FONT-WEIGHT:bold;BORDER-BOTTOM: 0pt solid #000000;BORDER-LEFT: 1pt solid #000000;BORDER-RIGHT: 0pt solid #000000;BORDER-TOP: 1pt solid #000000; FONT-FAMILY: Tahoma;FONT-SIZE: 12pt} A.Index{FONT-WEIGHT:bold;FONT-SIZE:8pt;COLOR:#000099;FONT-FAMILY:Tahoma;TEXT-DECORATION:none} A.Index:HOVER{FONT-WEIGHT:bold;FONT-SIZE:8pt;COLOR:#990000;FONT-FAMILY:Tahoma;TEXT-DECORATION:none}</STYLE></HEAD><BODY><A NAME="_top"></A><BR>'
PRINT @strHTML

-----------------Login information-------------------------------------------------------------

SELECT ROW_NUMBER () OVER (ORDER BY name) AS RowNumber,
-- 20140601 begin -- add disabled login
sid,
-- 20140601 end
name, dbname,language,  
CONVERT(CHAR(10),CASE denylogin WHEN 1 THEN 'X' ELSE '--' END) AS IsDenied,
CONVERT(CHAR(10),CASE isntname WHEN 1 THEN 'X' ELSE '--' END) AS IsWinAuTHENtication,
CONVERT(CHAR(10),CASE isntgroup WHEN 1 THEN 'X' ELSE '--' END) AS IsWinGroup,
createdate,UPDATEdate,
CONVERT(VARCHAR(2000),
CASE sysadmin WHEN 1 THEN 'sysadmin,' ELSE '' END +
CASE securityadmin WHEN 1 THEN 'securityadmin,' ELSE '' END +
CASE serveradmin WHEN 1 THEN 'serveradmin,' ELSE '' END +
CASE setupadmin WHEN 1 THEN 'setupadmin,' ELSE '' END +
CASE processadmin WHEN 1 THEN 'processadmin,' ELSE '' END +
CASE diskadmin WHEN 1 THEN 'diskadmin,' ELSE '' END +
CASE dbcreator WHEN 1 THEN 'dbcreator,' ELSE '' END +
CASE bulkadmin WHEN 1 THEN 'bulkadmin' ELSE '' END ) AS ServerRoles
INTO #syslogins
FROM master..syslogins WITH (nolock)
ORDER BY name

SET @rc = @@rowcount

SELECT @strHTML = '<BR><CENTER><FONT SIZE="5"><B> Server ' + @@servername + '</B></FONT></CENTER><BR>'
PRINT @strHTML
PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="100%">'

-- Query the data only if there are rows:
IF @rc = 0
BEGIN
   PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="1" ALIGN="center"><B><A NAME="_LoginInfomration">Logins information</A></B> </TD></TR>'
   PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>There are no logins on this server</B> </TD></TR>'
END
ELSE
BEGIN
   -- 20140601 begin -- add disabled login
   UPDATE #syslogins
   SET IsDenied = 'disabled'
   from #syslogins
   inner join sys.server_principals
   on server_principals.principal_id = #syslogins.sid
   WHERE IsDenied = '--'
   AND server_principals.is_disabled = 1
   -- 20140601 end

   UPDATE #syslogins
   SET ServerRoles = SUBSTRING(ServerRoles,1,LEN(ServerRoles)-1)
   WHERE SUBSTRING(ServerRoles,LEN(ServerRoles),1) = ','

   UPDATE #syslogins SET ServerRoles = '--'
   WHERE LTRIM(RTRIM(ServerRoles)) = ''

   PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="100%">'
   PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="9" ALIGN="center"><B><A NAME="_LoginInfomration">Logins information</A></B> </TD></TR>'
   PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="50%"><B>Login Name</B> </TD><TD ALIGN="left" WIDTH="50%"><B>Default DB</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Language</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Denied acess?</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Windows Auth?</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Window group?</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Date created</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Date UPDATEd</B> </TD><TD AALIGN="left" WIDTH="770%"><B>Server roles</B> </TD></TR>'

   SET @i = 1
   WHILE @i <= @rc
   BEGIN
       SELECT @strHTML =  
       '<TR><TD><B>' + CONVERT(VARCHAR(50),name) + '</B> </TD>' +  
       '<TD>' + CONVERT(VARCHAR(50),CASE ISNULL(dbname,'--') WHEN '' THEN '--' ELSE ISNULL(dbname,'--') END) + ' </TD>' +
       '<TD>' + CONVERT(VARCHAR(50),ISNULL(language,'--')) + ' </TD>' +
       '<TD>' + CONVERT(VARCHAR(10),ISNULL(IsDenied,'--')) + ' </TD>' +
       '<TD>' + CONVERT(VARCHAR(10),ISNULL(IsWinAuTHENtication,'--')) + ' </TD>' +
       '<TD>' + CONVERT(VARCHAR(10),ISNULL(IsWinGroup,'--')) + ' </TD>' +
       '<TD>' + CONVERT(VARCHAR(30),ISNULL(createdate,'--')) + ' </TD>' +
       '<TD>' + CONVERT(VARCHAR(30),ISNULL(UPDATEdate,'--')) + ' </TD>' +
       '<TD>' + CONVERT(VARCHAR(100),ISNULL(ServerRoles,'--')) + ' </TD>' +
       '</TR>'
       FROM #syslogins
       WHERE RowNumber = @i

       PRINT @strHTML

       SET @i = @i + 1
   END

   PRINT '</TABLE></DIV><BR><A CLASS="Index" HREF="#_top">Back To Top ^</A><BR><BR>'
   PRINT'<BR><CENTER></CENTER><BR>'
END

DROP TABLE #syslogins

-- 20140601 begin -- List all Server Permissions
   PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="70%">'
   PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="6" ALIGN="center"><B><A NAME="_ServerPermissions">Server Permissions</A></B> </TD></TR>'
   PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="50%"><B>Grantee</B> </TD><TD ALIGN="left" WIDTH="50%"><B>Class</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Permission Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Permission Type</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Object Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Object Type</B> </TD></TR>'

SELECT '<TR><TD>' + GRE.name + ' </TD>' AS '&nbsp;'
      ,'<TD>' + PER.class_desc + ' </TD>'
      ,'<TD>' + PER.permission_name + ' </TD>'
      ,'<TD>' + PER.state_desc + ' </TD>'
      ,'<TD>' + COALESCE(PRC.name, EP.name, N'&nbsp;') + ' </TD>'
      ,'<TD>' + COALESCE(PRC.type_desc, EP.type_desc, N'&nbsp;') + ' </TD></TR>'
FROM sys.server_permissions AS PER
     INNER JOIN sys.server_principals AS GRO
         ON PER.grantor_principal_id = GRO.principal_id
     INNER JOIN sys.server_principals AS GRE
         ON PER.grantee_principal_id = GRE.principal_id
     LEFT JOIN sys.server_principals AS PRC
         ON PER.class = 101
            AND PER.major_id = PRC.principal_id
     LEFT JOIN sys.endpoints AS EP
         ON PER.class = 105
            AND PER.major_id = EP.endpoint_id
WHERE PER.permission_name <> 'CONNECT SQL'            
ORDER BY GRE.name
        ,PER.permission_name;
        
   PRINT '</TABLE></DIV><BR><A CLASS="Index" HREF="#_top">Back To Top ^</A><BR><BR>'
   PRINT'<BR><CENTER></CENTER><BR>'
-- 20140601 end

---------------Fetch data per database-------------------------------------------------

CREATE TABLE #LoginMap (LoginName VARCHAR(200), UserName VARCHAR(200) NULL)

CREATE TABLE #RoleUser (RoLEName VARCHAR(200), UserName VARCHAR(200) NULL)

CREATE TABLE #ObjectPerms (RowNumber INT IDENTITY, UserName VARCHAR(50), PerType VARCHAR(10),PermName VARCHAR(30), SchemaName VARCHAR(50),  
ObjectName VARCHAR(100), ObjectType VARCHAR(20), ColName VARCHAR(50), IsGrantOption VARCHAR(10))

CREATE TABLE #DatabasePerms (RowNumber INT IDENTITY,UserName VARCHAR(50),PermType VARCHAR(20),PermName VARCHAR(50),IsGrantOption VARCHAR(5))

DECLARE dbs CURSOR FOR SELECT name FROM master..sysdatabases ORDER BY name

OPEN dbs
FETCH NEXT FROM dbs INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
   TRUNCATE TABLE #LoginMap
   TRUNCATE TABLE #RoleUser
   TRUNCATE TABLE #ObjectPerms  
   TRUNCATE TABLE #DatabasePerms
   SELECT @strHTML = '<BR><CENTER><FONT SIZE="5"><B> Database ' + @dbname + '</B></FONT></CENTER><BR>'
   PRINT @strHTML

-----------------Mapping of logins to users------------------
   EXEC('
INSERT INTO #LoginMap
SELECT login.loginname,users.name  
FROM ['+ @dbname+'].dbo.sysusers users  
INNER JOIN [master].[dbo].[syslogins] login  
ON users.[sid] = login.[sid]
WHERE users.uid < 16382  
and users.name not in (''public'',''dbo'',''guest'')
')

   SET @strHTML = ''

   PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%">'

   --Query the data only if there are rows
   IF NOT EXISTS (SELECT 1 FROM #LoginMap)
   BEGIN
       PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="1" ALIGN="center"><B><A NAME="_LoginMapping">Mapping of logins to users</A></B> </TD></TR>'
       PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>There are no mappings in this database</B> </TD></TR>'
   END  
   ELSE
   BEGIN
       PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="2" ALIGN="center"><B><A NAME="_LoginMapping">Mapping of logins to users</A></B> </TD></TR>'
       PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>Login Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>User Name</B> </TD></TR>'

       SELECT @strHTML = @strHTML +  
       '<TR><TD><B>' + CONVERT(VARCHAR(50),LoginName) + '</B> </TD><TD>' + CONVERT(VARCHAR(50),ISNULL(UserName,'')) + ' </TD></TR>' + CHAR(10)
       FROM #LoginMap
       ORDER BY LoginName

       PRINT @strHTML
   END

   PRINT '</TABLE></DIV><BR><A CLASS="Index" HREF="#_top">Back To Top ^</A><BR><BR>'

   ----------------SQL roles per user------------------
   EXEC ('INSERT INTO #RoleUser
SELECT b.name AS Role_name, a.name AS User_name ' +  
   'FROM ['+ @dbname+']..sysusers a ' +
   'INNER JOIN ['+ @dbname+ ']..sysmembers c on a.uid = c.memberuid ' +
   'INNER JOIN ['+ @dbname+ ']..sysusers b ON c.groupuid = b.uid ' +  
   'WHERE a.name <> ''dbo'''
   )  

   SET @strHTML = ''

   PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%">'

   -- Query the data only if there are rows:

   IF NOT EXISTS(SELECT 1 FROM #RoleUser)
   BEGIN
       PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="1" ALIGN="center"><B><A NAME="_DBRoleMapping">Roles per user</A></B> </TD></TR>'
       PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>There are no users mapped to roles in this database</B> </TD></TR>'
   END  
   ELSE
   BEGIN
       PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="2" ALIGN="center"><B><A NAME="_DBRoleMapping">Roles per user</A></B> </TD></TR>'
       PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>Role Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>User Name</B> </TD></TR>'

       SELECT @strHTML = @strHTML +  
       '<TR><TD><B>' + CONVERT(VARCHAR(50),RoLEName) + '</B> </TD><TD>' + CONVERT(VARCHAR(50),ISNULL(UserName,'')) + ' </TD></TR>' + CHAR(10)
       FROM #RoleUser
       ORDER BY RoLEName

       PRINT @strHTML
   END
    
   PRINT '</TABLE></DIV><BR><A CLASS="Index" HREF="#_top">Back To Top ^</A><BR><BR>'

----------------Database level Permissions-------------------------

   EXEC ('INSERT INTO #DatabasePerms
(UserName,PermType,PermName,IsGrantOption)
SELECT usr.name,
CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END,
perm.permission_name,  
CASE WHEN perm.state != ''W'' THEN ''--'' ELSE ''X'' END AS IsGrantOption
FROM ['+@dbname+'].sys.database_permissions AS perm
INNER JOIN
['+@dbname+'].sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHERE perm.major_id = 0
ORDER BY usr.name, perm.permission_name ASC, perm.state_desc ASC'
   )

   SET @rc = @@rowcount

   PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%">'

   -- Query the data only if there are rows:
    
   IF NOT EXISTS(SELECT 1 FROM #DatabasePerms)
   BEGIN
       PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="1" ALIGN="center"><B><A NAME="_DBLvlPerms">Database level permissions</A></B> </TD></TR>'
       PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>There are no specific permissions on the database level</B> </TD></TR>'
   END  
   ELSE
   BEGIN
       PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN=" 4" ALIGN="center"><B><A NAME="_DBPObjPerms">Database level permissions</A></B> </TD></TR>'
       PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>User Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Permission type</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Permission Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Grant option?</B> </TD></TR>'
        
       SET @i = 1
       WHILE @i <= @rc
       BEGIN
           SELECT @strHTML =  
           '<TR><TD><B>' + CONVERT(VARCHAR(50),UserName) + '</B> </TD>' +  
           '<TD>' + CONVERT(VARCHAR(50),ISNULL(PermType,'--')) + ' </TD>' +
           '<TD>' + CONVERT(VARCHAR(50),ISNULL(PermName,'--')) + ' </TD>' +
           '<TD>' + CONVERT(VARCHAR(5),ISNULL(IsGrantOption,'--')) + ' </TD>'+
           '</TR>'
           FROM #DatabasePerms
           WHERE Rownumber = @i

           PRINT @strHTML
        
           SET @i = @i + 1
       END
   END

   PRINT '</TABLE></DIV><BR><A CLASS="Index" HREF="#_top">Back To Top ^</A><BR><BR>'

----------------Database object Permissions-------------------------
   EXEC ('INSERT INTO #ObjectPerms
(UserName,PerType,PermName,SchemaName,ObjectName,ObjectType,ColName,IsGrantOption)
SELECT usr.name AS UserName,
CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END AS PerType,
perm.permission_name,USER_NAME(obj.schema_id) AS SchemaName, obj.name AS ObjectName,
CASE obj.Type  
WHEN ''U'' THEN ''Table''
WHEN ''V'' THEN ''View''
WHEN ''P'' THEN ''Stored Proc''
WHEN ''FN'' THEN ''Function''
ELSE obj.Type END AS ObjectType,
CASE WHEN cl.column_id IS NULL THEN ''--'' ELSE cl.name END AS ColName,
CASE WHEN perm.state = ''W'' THEN ''X'' ELSE ''--'' END AS IsGrantOption
FROM ['+@dbname+'].sys.database_permissions AS perm
INNER JOIN
['+@dbname+'].sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
['+@dbname+'].sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
['+@dbname+'].sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHERE obj.Type <> ''S''  
ORDER BY usr.name, perm.state_desc ASC, perm.permission_name ASC'
   )

   SET @rc = @@rowcount

   PRINT '<DIV ALIGN="center"><TABLE BORDER="0" CELLPADDING="2" CELLSPACING="0" BORDERCOLOUR="003366" WIDTH="60%">'
    
   -- Query the data only if there are rows:
   IF NOT EXISTS(SELECT 1 FROM #ObjectPerms)
   BEGIN
       PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="1" ALIGN="center"><B><A NAME="_DBPObjPerms">Object permissions</A></B> </TD></TR>'
       PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>There are no specific permissions to objects in this database</B> </TD></TR>'
   END  
   ELSE
   BEGIN
       PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="8" ALIGN="center"><B><A NAME="_DBPObjPerms">Object permissions</A></B> </TD></TR>'
       PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%"><B>User Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Permission type</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Permission Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Schema Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Object Name</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Object type type</B> </TD><TD ALIGN="left" WIDTH="70%"><B>Column Name</B> </TD><TD ALIGN=" left" WIDTH="70%"><B>Grant option?</B> </TD></TR>'
    
       SET @i = 1
       WHILE @i <= @rc
       BEGIN
           SELECT @strHTML =  
           '<TR><TD><B>' + CONVERT(VARCHAR(50),UserName) + '</B> </TD>' +  
           '<TD>' + CONVERT(VARCHAR(50),ISNULL(PerType,'--')) + ' </TD>' +
           '<TD>' + CONVERT(VARCHAR(50),ISNULL(PermName,'--')) + ' </TD>' +
           '<TD>' + CONVERT(VARCHAR(50),ISNULL(SchemaName,'--')) + ' </TD>' +
           '<TD>' + CONVERT(VARCHAR(50),ISNULL(ObjectName,'--')) + ' </TD>' +
           '<TD>' + CONVERT(VARCHAR(30),ISNULL(ObjectType,'--')) + ' </TD>' +
           '<TD>' + CONVERT(VARCHAR(50),ISNULL(ColName,'--')) + ' </TD>' +
           '<TD>' + CONVERT(VARCHAR(5),ISNULL(IsGrantOption,'--')) + ' </TD></TR>'
           FROM #ObjectPerms
           WHERE Rownumber = @i

           PRINT @strHTML

           SET @i = @i + 1
       END
   END

   PRINT '</TABLE></DIV><BR><A CLASS="Index" HREF="#_top">Back To Top ^</A><BR><BR>'

   FETCH NEXT FROM dbs INTO @dbname

END

---------------Close cursor and drop all temporary objects-------------

CLOSE dbs
DEALLOCATE dbs

DROP TABLE #LoginMap
DROP TABLE #RoleUser
DROP TABLE #ObjectPerms
DROP TABLE #DatabasePerms

PRINT '</BODY></HTML>'

GO  

Thursday, April 24, 2014 - 3:05:33 PM - Ankit Shah Back To Top (30506)

Hi Michelle ,

  this is very nice script. and it works great for me on SQL 2012. Do you have any script to generate report only for failed logins?

Wednesday, February 19, 2014 - 9:17:44 AM - Greg Robidoux Back To Top (29502)

Hi Lee,

I just ran this for SQL Server 2012 and this worked great.  This should be fine from all version from SQL 2005 - SQL 2012.

Greg

Wednesday, February 19, 2014 - 8:10:22 AM - Lee Back To Top (29500)

Question: Does this also work with SQL Server 2008 or do I need to make changes?

Friday, December 21, 2012 - 10:26:42 AM - Jack Burgess Back To Top (21081)

Great script!  The only thing missing from our perspective would be the server-wide permissions that get set at the instance level (like "view server state" and "alter trace").  If anybody has already done that modification, let me know!

 

 

Thursday, October 18, 2012 - 9:59:52 AM - Don Meeks Back To Top (19979)

To prevent code from stopping on 'Offline' databases:

Replace:
DECLARE dbs CURSOR FOR SELECT name FROM master..sysdatabases ORDER BY name

With:
DECLARE dbs CURSOR FOR SELECT name FROM master..sysdatabases
     where DATABASEPROPERTYEX(name, 'Status') = 'online'
     ORDER BY name

Tuesday, May 31, 2011 - 9:59:58 AM - Lelo121 Back To Top (13938)

This is kind of off subject but somewhat related. For an audit, in addition to the information generated in this report, are requesting other items such as the ones listed below. Is there a stored procedure or view that shows this information:

  • Patches
  • List of sample databases (to determine if they are still installed)
  • List of default accounts (could this be done using the syslogins?)
  • Enumerate data connectors
  • Query/retrieval mechanisms

Also, instead of using syslogins, how can I edit it to use sys.server_principals  and sys.sql_logins?

Monday, October 11, 2010 - 5:21:06 PM - Bob Lee Back To Top (10251)
Before I look into modifying for SQL 2000 did anyone already do this and the thread broke?  I love the 2006 Michelle.  Well done!

Bob

Monday, December 14, 2009 - 8:30:55 AM - MichDBA Back To Top (4567)

Hi,

Great, I see that there is a demand for the SQL 2000 script version, so I will work on it and will post it later.

 

Michelle.


Monday, December 14, 2009 - 7:27:19 AM - sokhay3 Back To Top (4566)

Hi

thanks for your script it works great.

Is there a way to convert easily this script for make it works for MS SQL Server 2000.

Thanks in advance for your answer. 

 

Tuesday, October 20, 2009 - 4:42:21 PM - cyberchic26 Back To Top (4265)

This worked.  THANKS!! This also solved another issue I had as well.

 

 

Monday, October 19, 2009 - 11:54:49 AM - MichDBA Back To Top (4249)

I don't know of a way to supress these [SQLSTATE 01000]  messages except for adding code to remove them from the output file.

Try executing an Operating System command from the job step, instead:

          osql -S<your_server_name> -E -Q" exec <your_database>..SP_SecurityAuditReports" -o<output file>

 

(replace   <your server name> ,    <your_database>  and  <output file>    accordingly).

 

I hope this one works... Let me know.


Monday, October 19, 2009 - 11:28:55 AM - cyberchic26 Back To Top (4248)

I have the same issue...I was hoping someone had replied. 


Monday, August 24, 2009 - 1:06:26 PM - mhickma Back To Top (3949)
Is there a way to suppress the MANY [SQLSTATE 01000] that now plagues the report if it is run as a job step? job step command = exec SP_SecurityAuditReport

Monday, August 24, 2009 - 7:23:11 AM - admin Back To Top (3948)

The script has been updated to reflect some of these changes.

Sunday, August 23, 2009 - 2:02:15 PM - MichDBA Back To Top (3943)

Hi mhickma,

Great question!   Yes, you can email this report easily.

You can do one of the following:

  • Run it from an SSIS package that sends the output to an *.htm file and then send the output file by email (add a send email task)
  • Run it from a job , using an output file (in the step's advance options) and add a step to that job that sends the output file by email using database mail.
    You can find some examples here:  http://msdn.microsoft.com/en-us/library/ms190307.aspx.
  • Run a batch file to create the output file, something like:
osql -S"<server_name>" -E -Q"EXEC Michelle..spAuditUsersPermissions" -o"c:\temp\try.htm"
And then send it (the output file -- c:\temp\try.htm) using database mail as described above.
 

Hope this helps....

Michelle.

 
 

 

Sunday, August 23, 2009 - 1:47:17 PM - MichDBA Back To Top (3942)

 Hi Lazlo,

Thanks for the comments, we will modify the script accordingly.

Regarding #2, I do not get the same behavior as you (I tried it on few servers). Is it possible that you have a SET ROWCOUNT 1 somewhere?

Regarding  #3, I've seen cass where    OBJECTPROPERTY(obj.object_id,''IsMSShipped'')  returned the wrong value (maybe prior to SQL2005 SP2 or SP3?). Instead, you can use WHERE type <> 'S', but it will still return few of the system tables such as dbproperties, so you can add a conditionto omit them.

 

Thanks,

Michelle.


Tuesday, August 18, 2009 - 1:50:22 PM - mhickma Back To Top (3909)
Cool script! Now is there a way to use this script to send database mail with the results as the body? I could see using this in a job and sending a report on a monthly basis, especially the granulated view; commenting out the where clause.

Tuesday, August 18, 2009 - 8:59:30 AM - stittdba Back To Top (3906)

I work with SQL Servers that use collation Latin1_General_CS_AS. In order for this code to work you need to make the following changes,

On line 103, change "Dbs" to "dbs"
On line 113, change "@dbName" to "@dbname"
On line 229, change "RowNumber" to "Rownumber"
On line 295, change "RowNumber" to "Rownumber"
On line 305, change "Dbs" to "dbs"

 If you work in case-insensitive environments, the code will work just fine.

Tuesday, August 18, 2009 - 6:17:52 AM - Lazlo Back To Top (3901)

Great script, thank you.

Some comments:

1. The title must be corrected in line 216 (to "Database level permissions")

2. only the first row is printed in Login mapping and Roles per user tables.

3. I had to comment out the where condition in line 263 to get all of the object permissions (I don't really understand why...)

Lazlo














get free sql tips
agree to terms