Cool Tips‎ > ‎SQL Server Security‎ > ‎

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




Comments