Schema LEVEL SECURITY ,SQL Server

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 = '
' 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 = '
Server ' + @@servername + '
' PRINT @strHTML PRINT '

' -- Query the data only if there are rows: IF @rc = 0 BEGIN PRINT '' PRINT '' 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 '

Logins information

There are no logins on this server

' PRINT '' PRINT '' SET @i = 1 WHILE @i <= @rc BEGIN SELECT @strHTML = '' + '' + '' + '' + '' + '' + '' + '' + '' + '' FROM #syslogins WHERE RowNumber = @i PRINT @strHTML SET @i = @i + 1 END PRINT '

Logins information
Login Name	Default DB	Language	Denied acess?	Windows Auth?	Window group?	Date created	Date UPDATEd	Server roles
' + CONVERT(VARCHAR(50),name) + '	' + CONVERT(VARCHAR(50),CASE ISNULL(dbname,'--') WHEN '' THEN '--' ELSE ISNULL(dbname,'--') END) + '	' + CONVERT(VARCHAR(50),ISNULL(language,'--')) + '	' + CONVERT(VARCHAR(10),ISNULL(IsDenied,'--')) + '	' + CONVERT(VARCHAR(10),ISNULL(IsWinAuTHENtication,'--')) + '	' + CONVERT(VARCHAR(10),ISNULL(IsWinGroup,'--')) + '	' + CONVERT(VARCHAR(30),ISNULL(createdate,'--')) + '	' + CONVERT(VARCHAR(30),ISNULL(UPDATEdate,'--')) + '	' + CONVERT(VARCHAR(100),ISNULL(ServerRoles,'--')) + '

Back To Top ^

' PRINT'

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

' --Query the data only if there are rows IF NOT EXISTS (SELECT 1 FROM #LoginMap) BEGIN PRINT '' PRINT '' END ELSE BEGIN PRINT '' PRINT '' SELECT @strHTML = @strHTML + '' + CHAR(10) FROM #LoginMap ORDER BY LoginName PRINT @strHTML END PRINT '

Mapping of logins to users
There are no mappings in this database
Mapping of logins to users
Login Name	User Name
' + CONVERT(VARCHAR(50),LoginName) + '	' + CONVERT(VARCHAR(50),ISNULL(UserName,'')) + '

Back To Top ^

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

' -- Query the data only if there are rows: IF NOT EXISTS(SELECT 1 FROM #RoleUser) BEGIN PRINT '' PRINT '' END ELSE BEGIN PRINT '' PRINT '' SELECT @strHTML = @strHTML + '' + CHAR(10) FROM #RoleUser ORDER BY RoLEName PRINT @strHTML END PRINT '

Roles per user
There are no users mapped to roles in this database
Roles per user
Role Name	User Name
' + CONVERT(VARCHAR(50),RoLEName) + '	' + CONVERT(VARCHAR(50),ISNULL(UserName,'')) + '

Back To Top ^

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

' -- Query the data only if there are rows: IF NOT EXISTS(SELECT 1 FROM #DatabasePerms) BEGIN PRINT '' PRINT '' END ELSE BEGIN PRINT '' PRINT '' SET @i = 1 WHILE @i <= @rc BEGIN SELECT @strHTML = '' + '' + '' + ''+ '' FROM #DatabasePerms WHERE Rownumber = @i PRINT @strHTML SET @i = @i + 1 END END PRINT '

Database level permissions
There are no specific permissions on the database level
Database level permissions
User Name	Permission type	Permission Name	Grant option?
' + CONVERT(VARCHAR(50),UserName) + '	' + CONVERT(VARCHAR(50),ISNULL(PermType,'--')) + '	' + CONVERT(VARCHAR(50),ISNULL(PermName,'--')) + '	' + CONVERT(VARCHAR(5),ISNULL(IsGrantOption,'--')) + '

Back To Top ^

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

' -- Query the data only if there are rows: IF NOT EXISTS(SELECT 1 FROM #ObjectPerms) BEGIN PRINT '' PRINT '' END ELSE BEGIN PRINT '' PRINT '' SET @i = 1 WHILE @i <= @rc BEGIN SELECT @strHTML = '' + '' + '' + '' + '' + '' + '' + '' FROM #ObjectPerms WHERE Rownumber = @i PRINT @strHTML SET @i = @i + 1 END END PRINT '

Object permissions
There are no specific permissions to objects in this database
Object permissions
User Name	Permission type	Permission Name	Schema Name	Object Name	Object type type	Column Name	Grant option?
' + CONVERT(VARCHAR(50),UserName) + '	' + CONVERT(VARCHAR(50),ISNULL(PerType,'--')) + '	' + CONVERT(VARCHAR(50),ISNULL(PermName,'--')) + '	' + CONVERT(VARCHAR(50),ISNULL(SchemaName,'--')) + '	' + CONVERT(VARCHAR(50),ISNULL(ObjectName,'--')) + '	' + CONVERT(VARCHAR(30),ISNULL(ObjectType,'--')) + '	' + CONVERT(VARCHAR(50),ISNULL(ColName,'--')) + '	' + CONVERT(VARCHAR(5),ISNULL(IsGrantOption,'--')) + '

Back To Top ^

' 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 '' GO https://www.mssqltips.com/sqlservertip/1818/script-to-auto-generate-a-security-report-for-your-sql-server-instance/