Generate a database security report
Applicability:
SQL Server 2000: N/A
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Tested
SQL Server 2014: Tested
SQL Server 2016: Tested
SQL Server 2017: Tested
Credits:
Author:Unknown
Date: 1 Aug 2010
Description
This is a really helpful report that produces a comprehensive web page that can be used to document the security on a server.
Similar level of detail to the migration security output, but intended to document rather than to recreate
Code
-- 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