Audit SQL Server Permissions

Script

SET NOCOUNT ON

go



SELECT @@servername AS 'ServerName', 

--sid, 

name, 

CASE hasaccess    WHEN 1 THEN 'Yes' ELSE '   ' END AS 'HasAccess',

CASE sysadmin       WHEN 1 THEN 'Yes' ELSE '   ' END AS 'SysAdmin',

CASE securityadmin WHEN 1 THEN 'Yes' ELSE '   ' END AS 'SecAdmin' ,

CASE serveradmin    WHEN 1 THEN 'Yes' ELSE '   ' END AS 'SrvrAdmn',

CASE setupadmin     WHEN 1 THEN 'Yes' ELSE '   ' END AS 'setupadmin',

CASE processadmin   WHEN 1 THEN 'Yes' ELSE '   ' END AS 'processadmin',

CASE diskadmin      WHEN 1 THEN 'Yes' ELSE '   ' END AS 'diskadmin',

CASE dbcreator      WHEN 1 THEN 'Yes' ELSE '   ' END AS 'dbcreator',

CASE bulkadmin      WHEN 1 THEN 'Yes' ELSE '   ' END AS 'bulkadmin' 

INTO ##AuditTable

  FROM master.dbo.syslogins

  WHERE name NOT LIKE '##%'


 --SELECT * FROM ##AuditTable


DECLARE @SQLOut VARCHAR(1000) 

DECLARE @ColNbr INT


SET @ColNbr = 1


WHILE @ColNbr < 76

BEGIN


SET @SQLOut = 'ALTER TABLE ##AuditTable ADD DB'+ Convert(varchar(2), @ColNbr) + ' sysname'

EXEC (@SQLOut)

SET @SQLOut = 'UPDATE ##AuditTable SET DB'+ Convert(varchar(2), @ColNbr) + '= '' ''' 

EXEC (@SQLOut)


SET @ColNbr = @ColNbr + 1


end





DECLARE @User       sysname

DECLARE @HasAccess  VARCHAR(25)

--DECLARE @SQLOut VARCHAR(100) 

--DECLARE @ColNbr INT

DECLARE @DBName sysname

DECLARE @DBNbr INT



DECLARE DBCursor CURSOR

FOR

SELECT name, dbid

  FROM master.dbo.sysdatabases

WHERE name NOT IN ('tempdb')

  AND status NOT IN (65568, 48, 512, 256, 128, 64, 528, 32, 66048, 66056)


OPEN DBCursor


FETCH DBCursor

INTO @DBName, @DBNbr


WHILE @@Fetch_Status = 0

 

   BEGIN 


SET @SQLOut = 'UPDATE ##AuditTable SET DB'+ Convert(varchar(2), @DBNbr) + '= ''' + @DBName +  ''' Where name = ''sa''' 

EXEC (@SQLOut)


--  DECLARE @SQLOut VARCHAR(100), @DBName sysname;       set @DBName = 'master'   


SET @SQLOut = 'DECLARE UserCursor CURSOR  '

+ 'FOR '

+ 'SELECT memberprinc.[name], roleprinc.[name]     '

+ 'FROM [' + @DBName + ']' + '.sys.database_role_members members '

+ 'JOIN [' + @DBName + ']' + '.sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] '

+ 'JOIN [' + @DBName + ']' + '.sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] '

+ 'WHERE (memberprinc.[name] NOT LIKE ''db_%'' and memberprinc.[name] != ''sa'')'

EXEC (@SQLOut)


OPEN UserCursor


FETCH UserCursor

 INTO @User, @HasAccess



WHILE @@fetch_status = 0

BEGIN

SET @ColNbr = @DBNbr


SET @SQLOut = 'UPDATE ##AuditTable SET DB' 

+ CONVERT(VARCHAR(3), @ColNbr) 

+ ' = ' 

+ ISNULL('DB' + CONVERT(VARCHAR(3), @ColNbr), ', ') 

+ '+ ''' + @HasAccess + ','''

+ ' WHERE name = ''' 

+ @User + ''''

 

--print @SQLOut

EXEC(@SQLOut)


FETCH UserCursor

   INTO @User, @HasAccess


end 


CLOSE UserCursor

DEALLOCATE UserCursor


FETCH DBCursor

INTO @DBName, @DBNbr


END   


CLOSE DBCursor

DEALLOCATE DBCursor



 SELECT * FROM ##AuditTable

 go


 DROP TABLE ##AuditTable

 GO.