SQLSkills blogspot

Generate a security report on SQL Server instance - Security script

-- Srored Procedure to generate Security Audit report in HTML format:

CREATE PROC spAuditUsersPermissions

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 '--' ELSEISNULL(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), PerTypeVARCHAR(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),PermTypeVARCHAR(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>'

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

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

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

---------------For the result- Execute the stored procedure -------------

USE [DbNAME]

DECLARE @return_value int

EXEC @return_value = [dbo].[spAuditUsersPermissions]

SELECT 'Return Value' = @return_value

Copy Output msg into notepad and save as "SQLSecurityReport.html".
Open SQLSecurityReport.html to view detaial.
SQLSecurityReport.html Output looks below:

Server AUZUR2K8DC6DQ\JIRA2PROD

Logins information
Login Name	Default DB	Language	Denied acess?	Windows Auth?	Window group?	Date created	Date UPDATEd	Server roles
##MS_AgentSigningCertificate##	master	us_english	--	--	--	Feb 19 2014 5:13PM	Feb 19 2014 5:13PM	--
##MS_PolicyEventProcessingLogin##	master	us_english	--	--	--	Feb 19 2014 5:13PM	Feb 19 2014 5:13PM	--
##MS_PolicySigningCertificate##	master	--	--	--	--	Feb 19 2014 5:13PM	Feb 19 2014 5:13PM	--
##MS_PolicyTsqlExecutionLogin##	master	us_english	--	--	--	Apr 2 2010 5:37PM	Feb 19 2014 5:13PM	--
##MS_SmoExtendedSigningCertificate##	master	--	--	--	--	Feb 19 2014 5:13PM	Feb 19 2014 5:13PM	--
##MS_SQLAuthenticatorCertificate##	master	--	--	--	--	Feb 19 2014 5:13PM	Feb 19 2014 5:13PM	--
##MS_SQLReplicationSigningCertificate##	master	--	--	--	--	Feb 19 2014 5:13PM	Feb 19 2014 5:13PM	--
##MS_SQLResourceSigningCertificate##	master	--	--	--	--	Feb 19 2014 5:13PM	Feb 19 2014 5:13PM	--
AUZURMSD01\DepDBAmy	master	us_english	--	X	X	Feb 12 2014 2:57PM	Feb 12 2014 2:57PM	sysadmin
AUZURMSD01\hsrvjpsql	master	us_english	--	X	--	Feb 12 2014 2:16PM	Feb 12 2014 2:16PM	sysadmin
Jirausr	master	us_english	--	--	--	Feb 13 2014 2:14PM	Feb 13 2014 2:14PM	--
NT AUTHORITY\SYSTEM	master	us_english	--	X	--	Feb 12 2014 2:16PM	Feb 12 2014 2:16PM	sysadmin
NT SERVICE\MSSQL$JIRA2PROD	master	us_english	--	X	X	Feb 12 2014 2:16PM	Feb 12 2014 2:16PM	sysadmin
NT SERVICE\SQLAgent$JIRA2PROD	master	us_english	--	X	X	Feb 12 2014 2:16PM	Feb 12 2014 2:16PM	sysadmin
sa	master	us_english	--	--	--	Apr 8 2003 9:10AM	Feb 12 2014 2:16PM	sysadmin

Mapping of logins to users
Login Name	User Name
Jirausr	Jirausr

Roles per user
Role Name	User Name
db_owner	Jirausr

Database tempdb

Mapping of logins to users

There are no mappings in this database

Roles per user

There are no users mapped to roles in this database

Database level permissions
User Name	Permission type	Permission Name	Grant option?
dbo	GRANT	CONNECT	--
guest	GRANT	CONNECT	--

Object permissions

There are no specific permissions to objects in this database

SQLSkills blogspot

Search This Blog

GUNNU

Generate a security report on SQL Server instance - Security script

Translate

My Blog List