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.
| 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 |
| 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
