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

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> 
 <CENTER> Server MyServer\SQL_2005</CENTER> 
<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”><A NAME=”_LoginInfomration”>Logins information</A> </TD></TR>
<TR BGCOLOR=”EEEEEE”><TD ALIGN=”left” WIDTH=”50%”>Login Name </TD><TD ALIGN=”left” WIDTH=”50%”>Default DB </TD><TD ALIGN=”left” WIDTH=”70%”>Language </TD><TD ALIGN=”left” WIDTH=”70%”>Denied acess? </TD><TD ALIGN=”left” WIDTH=”70%”>Windows Auth? </TD><TD ALIGN=”left” WIDTH=”70%”>Window group? </TD><TD ALIGN=”left” WIDTH=”70%”>Date created </TD><TD ALIGN=”left” WIDTH=”70%”>Date updated </TD><TD ALIGN=”left” WIDTH=”70%”>Server roles </TD></TR>
<TR><TD>##MS_AgentSigningCertificate## </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>##MS_SQLAuthenticatorCertificate## </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>##MS_SQLReplicationSigningCertificate## </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>##MS_SQLResourceSigningCertificate## </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>MyServer\Michelle2 </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>MyServer\SQLServer2005MSFTEUser$MyServer </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>MyServer\SQLServer2005MSSQLUser$MyServer </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>MyServer\SQLServer2005SQLAgentUser$MyServer </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>MyServer\TestGroup </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>MyServer\User </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>BUILTIN\Administrators </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>NT AUTHORITY\SYSTEM </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>sa </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> <A CLASS=”Index” HREF=”#_top”>Back To Top ^</A> 
 <CENTER></CENTER> 
 <CENTER> Database test</CENTER> 
<DIV ALIGN=”center”><TABLE BORDER=”0″ CELLPADDING=”2″ CELLSPACING=”0″ BORDERCOLOUR=”003366″ WIDTH=”60%”>
<TR BGCOLOR=”EEEEEE”><TD CLASS=”Title” COLSPAN=”2″ ALIGN=”center”><A NAME=”_LoginMapping”>Mapping of logins to users</A> </TD></TR>
<TR BGCOLOR=”EEEEEE”><TD ALIGN=”left” WIDTH=”70%”>Login Name </TD><TD ALIGN=”left” WIDTH=”70%”>User Name </TD></TR>
<TR><TD>MyServer\Michelle2 </TD><TD>MyServer\Michelle2 </TD></TR>
<TR><TD>MyServer\TestGroup </TD><TD>MyServer\TestGroup </TD></TR>

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

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

Run this SP “as is” and check what output you get.
You can reformat the report as you wish. For example: You can add an Index at the beginning with reference (link) to each one of the tables of data; you can add a logo or add, remove or reformat columns of the report as required.
More about how to GRANT permissions in SQL 2005.
More about Scripting permissions in SQL 2005: Script to determine permissions in SQL Server 2005
Server level permissions for SQL Server 2005 and SQL Server 2008
Database level permissions for SQL Server 2005 and 2008

Michelle Gutzait

Michelle is the SQL Server Team Lead at www.pythian.com, a worldwide company providing Remote Administration services for databases, operating systems, SANs and networks. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the past 10 years, she has worked exclusively with SQL Server. Her skills include SQL infrastructure and database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS/SSIS packages, administrative and infrastructure tools development, reporting services and more.

The Stored Procedure:

Example result (HTML format)

Sample Report

Leave a ReplyCancel Reply