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

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 = ' <CENTER> Server ' + @@servername + '</CENTER> '

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"><A NAME="_LoginInfomration">Logins information</A> </TD></TR>'

PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%">There are no logins on this server </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"><A NAME="_LoginInfomration">Logins information</A> </TD></TR>'

PRINT '<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 AALIGN="left" WIDTH="770%">Server roles </TD></TR>'

SET @i = 1

WHILE @i <= @rc

BEGIN

SELECT @strHTML =

'<TR><TD>' + CONVERT(VARCHAR(50),name) + ' </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> <A CLASS="Index" HREF="#_top">Back To Top ^</A> '

PRINT' <CENTER></CENTER> '

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 = ' <CENTER> Database ' + @dbname + '</CENTER> '

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"><A NAME="_LoginMapping">Mapping of logins to users</A> </TD></TR>'

PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%">There are no mappings in this database </TD></TR>'

END

ELSE

BEGIN

PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="2" ALIGN="center"><A NAME="_LoginMapping">Mapping of logins to users</A> </TD></TR>'

PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%">Login Name </TD><TD ALIGN="left" WIDTH="70%">User Name </TD></TR>'

SELECT @strHTML = @strHTML +

'<TR><TD>' + CONVERT(VARCHAR(50),LoginName) + ' </TD><TD>' + CONVERT(VARCHAR(50),ISNULL(UserName,'')) + ' </TD></TR>' + CHAR(10)

FROM #LoginMap

ORDER BY LoginName

PRINT @strHTML

END

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

----------------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"><A NAME="_DBRoleMapping">Roles per user</A> </TD></TR>'

PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%">There are no users mapped to roles in this database </TD></TR>'

END

ELSE

BEGIN

PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="2" ALIGN="center"><A NAME="_DBRoleMapping">Roles per user</A> </TD></TR>'

PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%">Role Name </TD><TD ALIGN="left" WIDTH="70%">User Name </TD></TR>'

SELECT @strHTML = @strHTML +

'<TR><TD>' + CONVERT(VARCHAR(50),RoLEName) + ' </TD><TD>' + CONVERT(VARCHAR(50),ISNULL(UserName,'')) + ' </TD></TR>' + CHAR(10)

FROM #RoleUser

ORDER BY RoLEName

PRINT @strHTML

END

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

----------------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"><A NAME="_DBLvlPerms">Database level permissions</A> </TD></TR>'

PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%">There are no specific permissions on the database level </TD></TR>'

END

ELSE

BEGIN

PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN=" 4" ALIGN="center"><A NAME="_DBPObjPerms">Database level permissions</A> </TD></TR>'

PRINT '<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>'

SET @i = 1

WHILE @i <= @rc

BEGIN

SELECT @strHTML =

'<TR><TD>' + CONVERT(VARCHAR(50),UserName) + ' </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

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

----------------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"><A NAME="_DBPObjPerms">Object permissions</A> </TD></TR>'

PRINT '<TR BGCOLOR="EEEEEE"><TD ALIGN="left" WIDTH="70%">There are no specific permissions to objects in this database </TD></TR>'

END

ELSE

BEGIN

PRINT '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="8" ALIGN="center"><A NAME="_DBPObjPerms">Object permissions</A> </TD></TR>'

PRINT '<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>'

SET @i = 1

WHILE @i <= @rc

BEGIN

SELECT @strHTML =

'<TR><TD>' + CONVERT(VARCHAR(50),UserName) + ' </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

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

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