Script to check the status of Encryption

****** Query to find the status of encryption *****

SELECT DB_NAME (e.database_id) AS DatabaseName, e.database_id, e.encryption_state,

CASE e.encryption_state

     WHEN 0 THEN 'No database encryption key present, no encryption'

     WHEN 1 THEN 'Unencrypted'

     WHEN 2 THEN 'Encryption in progress'

     WHEN 3 THEN 'Encrypted'

     WHEN 4 THEN 'Key change in progress'

     WHEN 5 THEN 'Decryption in progress'

END AS encryption_state_desc, c.name, e.percent_complete

FROM sys.dm_database_encryption_keys AS e

LEFT JOIN master.sys.certificates AS c

ON e.encryptor_thumbprint = c.thumbprint

Script to Get Security Information of SQL Server 2012 and Above

Script to find server level roles assigned to Server level logins \ roles

SELECT a.name as Name,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName, 

SUSER_NAME(b.role_principal_id) AS AssociatedServerRole 

FROM sys.server_principals a  JOIN sys.server_role_members b ON a.principal_id=b.member_principal_id 

WHERE a.is_fixed_role <> 1 AND a.name NOT LIKE '##%' AND a.name NOT LIKE 'NT%'

AND a.name <> 'public' ORDER BY Name, LoginType 

Script to find Server level permissions assigned to Server level logins \ roles

SELECT a.name AS Name, a.type_desc AS LoginType,b.class_desc AS ClassDesc 

,b.permission_name AS ServerLevelPermission,b.state_desc AS PermissionState 

FROM sys.server_principals a JOIN sys.server_permissions b 

ON a.principal_id = b.grantee_principal_id WHERE a.is_fixed_role <> 1 

AND a.name NOT LIKE '##%' AND a.name NOT LIKE 'NT%' AND a.name <> 'public'  ORDER BY Name 

Script to find all user database level roles assigned to database users \ roles

DECLARE @DBuser_sql VARCHAR(4000) 

DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), Authentication_type VARCHAR(250),AssociatedRole VARCHAR(200)) 

SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType, CASE a.authentication_type 

WHEN 0 THEN ''No Authencication'' 

WHEN 1 THEN ''Uncontained User - Instance Level'' 

WHEN 2 THEN ''Contained User - Database Level'' 

WHEN 3 THEN ''Windows Login User'' END As AuthenticationType, 

USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a 

RIGHT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id 

WHERE a.sid IS NOT NULL AND a.type NOT IN (''C'') 

AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND a.name NOT LIKE ''NT%'' 

AND a.name NOT IN (''public'',''dbo'',''guest'') 

AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY DBName'

INSERT @DBuser_table 

EXEC sp_MSforeachdb @command1=@dbuser_sql 

SELECT * FROM @DBuser_table ORDER BY DBName 

Script to find all user database level permissions assigned to database users \ roles

DECLARE @Obj_sql VARCHAR(2000) 

DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250),Authentication_Type VARCHAR(250), Permission VARCHAR(500), PermissionState VARCHAR(200)) 

SET @Obj_sql='SELECT ''?'' AS DBName,a.name AS UserName,CASE a.authentication_type 

WHEN 0 THEN ''No Authencication'' 

WHEN 1 THEN ''Uncontained User - Instance Level'' 

WHEN 2 THEN ''Contained User - Database Level'' 

WHEN 3 THEN ''Windows Login User'' END As AuthenticationType, 

b.permission_name AS Permission,b.state_desc AS PermissionState 

FROM ?.sys.database_principals a join 

?.sys.database_permissions  b on a.principal_id=b.grantee_principal_id 

WHERE a.name not in (''public'',''guest'',''dbo'') AND b.class <> 1 

AND a.name NOT LIKE ''NT%'' AND ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'')'

INSERT @Obj_table 

EXEC sp_msforeachdb @command1=@Obj_sql 

SELECT * FROM @Obj_table ORDER BY DBName 

Script to find all user database level object permissions assigned to database users \ roles

DECLARE @Obj_sql VARCHAR(2000) 

DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250),Authentication_Type VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200)) 

SET @Obj_sql='SELECT ''?'' AS DBName,U.name as username,  CASE S.authentication_type 

WHEN 0 THEN ''No Authencication'' 

WHEN 1 THEN ''Uncontained User - Instance Level'' 

WHEN 2 THEN ''Contained User - Database Level'' 

WHEN 3 THEN ''Windows Login User'' END AS AuthenticationType, 

O.name as object,  permission_name AS permission from ?.sys.database_permissions 

JOIN ?.sys.sysusers U ON grantee_principal_id = uid 

JOIN ?.sys.database_principals S ON s.principal_id=U.uid 

JOIN ?.sys.sysobjects O ON major_id = id 

WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'')'

INSERT @Obj_table 

EXEC sp_msforeachdb @command1=@Obj_sql 

SELECT * FROM @Obj_table ORDER BY DBName 

Script to get Security Information of SQL Server 2005,2008,2008R2

Script to find ALL server level logins and role assigned

SELECT a.name as LoginName,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName,

CASE WHEN b.sysadmin = 1 THEN 'sysadmin'

          WHEN b.securityadmin=1 THEN 'securityadmin'

          WHEN b.serveradmin=1 THEN 'serveradmin'

          WHEN b.setupadmin=1 THEN 'setupadmin'

          WHEN b.processadmin=1 THEN 'processadmin'

          WHEN b.diskadmin=1 THEN 'diskadmin'

          WHEN b.dbcreator=1 THEN 'dbcreator'

          WHEN b.bulkadmin=1 THEN 'bulkadmin'

          ELSE 'Public' END AS 'ServerRole'

FROM sys.server_principals a  JOIN master..syslogins b ON a.sid=b.sid WHERE a.type  <> 'R' AND a.name NOT LIKE '##%'




Script to find ALL database users and roles assigned




DECLARE @DBuser_sql VARCHAR(4000)

DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200))

SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a

LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id

WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'

INSERT @DBuser_table

EXEC sp_MSforeachdb @command1=@dbuser_sql

SELECT * FROM @DBuser_table ORDER BY DBName




Script to find Object level permission for ALL user databases




DECLARE @Obj_sql VARCHAR(2000)

DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200))

SET @Obj_sql='select ''?'' as DBName,U.name as username, O.name as object,  permission_name as permission from ?.sys.database_permissions

join ?.sys.sysusers U on grantee_principal_id = uid join ?.sys.sysobjects O on major_id = id WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') order by U.name '

INSERT @Obj_table

EXEC sp_msforeachdb @command1=@Obj_sql

SELECT * FROM @Obj_table