Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: DB permissions audit

    looking for a way to audit my sql servers (2000/2005/2208) to list users and their permissions.

    I found the code (below) on an old blog and it works very well!! but I cannot tweek it properly to include SQL logins, AD accounts AND AD group permissions. Can anone help or does anyone know of an alternate method??


    Code:
    SET ANSI_NULLS ON  GO   
    SET QUOTED_IDENTIFIER ON  GO   
    SET ANSI_PADDING ON  GO 
    
    -- CREATING A TEMP TABLE TO LOAD WITH DATABASE ROLES
    CREATE TABLE [TEMPDB].[DBO].[DB_ROLES]
    (         [DBNAME] [SYSNAME] ,
             [USERNAME] [SYSNAME] ,         [DB_OWNER] [VARCHAR](3) ,
             [DB_ACCESSADMIN] [VARCHAR](3) ,
             [DB_SECURITYADMIN] [VARCHAR](3) ,
             [DB_DDLADMIN] [VARCHAR](3) ,
             [DB_DATAREADER] [VARCHAR](3) ,
             [DB_DATAWRITER] [VARCHAR](3) ,
             [DB_DENYDATAREADER] [VARCHAR](3) ,
             [DB_DENYDATAWRITER] [VARCHAR](3) ,
             [DT_CREATE] [DATETIME] NOT NULL,
             [DT_UPDATE] [DATETIME] NOT NULL,
             [DT_REPORT] [DATETIME] NOT NULL CONSTRAINT [DF__DBROLES__CUR_DAT__3A179ED3]  DEFAULT (GETDATE())   ) ON [PRIMARY];   
    
    
    INSERT INTO    [TEMPDB].[DBO].[DB_ROLES]   EXEC SP_MSFOREACHDB   '     SELECT        ''?'' AS DBNAME,
             USERNAME,        MAX(CASE ROLENAME WHEN ''DB_OWNER''         THEN ''YES'' ELSE ''NO'' END) AS DB_OWNER,
                       MAX(CASE ROLENAME WHEN ''DB_ACCESSADMIN ''   THEN ''YES'' ELSE ''NO'' END) AS DB_ACCESSADMIN ,
                                 MAX(CASE ROLENAME WHEN ''DB_SECURITYADMIN''  THEN ''YES'' ELSE ''NO'' END) AS DB_SECURITYADMIN,          
                                 MAX(CASE ROLENAME WHEN ''DB_DDLADMIN''   THEN ''YES'' ELSE ''NO'' END) AS DB_DDLADMIN,          
                                 MAX(CASE ROLENAME WHEN ''DB_DATAREADER''        THEN ''YES'' ELSE ''NO'' END) AS DB_DATAREADER,          
                                 MAX(CASE ROLENAME WHEN ''DB_DATAWRITER''        THEN ''YES'' ELSE ''NO'' END) AS DB_DATAWRITER,        
                                 MAX(CASE ROLENAME WHEN ''DB_DENYDATAREADER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DENYDATAREADER,          
                                 MAX(CASE ROLENAME WHEN ''DB_DENYDATAWRITER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DENYDATAWRITER,          
                                 CREATEDATE,        
                                 UPDATEDATE,          
                                 GETDATE()          
                                 FROM (          
                                 SELECT B.NAME AS USERNAME, C.NAME AS ROLENAME, B.CREATEDATE, B.UPDATEDATE               
                                 FROM                          
                                 [?].DBO.SYSMEMBERS A   JOIN [?].DBO.SYSUSERS  B  ON A.MEMBERUID = B.UID                           
                                 JOIN [?].DBO.SYSUSERS C ON A.GROUPUID = C.UID                            )S                     
                                 GROUP BY USERNAME, CREATEDATE, UPDATEDATE            
                                 ORDER BY USERNAME'
    
    SELECT  SERVERPROPERTY('SERVERNAME') AS [SERVERNAME],
    B.NAME AS [LOGINNAME],   
    CASE B.SYSADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS SYSADMIN,   
    CASE B.SECURITYADMIN WHEN '1' THEN 'YES' ELSE 'NO' END AS SECURITYADMIN,   
    CASE B.SETUPADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS SETUPADMIN,   
    CASE B.PROCESSADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS PROCESSADMIN,   
    CASE B.DISKADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS DISKADMIN,   
    CASE B.DBCREATOR  WHEN '1' THEN 'YES' ELSE 'NO' END AS DBCREATOR,   
    CASE B.BULKADMIN  WHEN '1' THEN 'YES' ELSE 'NO' END AS BULKADMIN,   B.DBNAME AS [DEFAULT_DBNAME],   
    A.* INTO #LOGINS FROM [TEMPDB].[DBO].[DB_ROLES] 
    A RIGHT JOIN MASTER..SYSLOGINS B ON A.USERNAME=B.NAME
      
    --WHERE B.ISNTUSER=1  --INCLUDE TO EXCLUDE THE SQL LOGINS   
    SELECT * FROM #LOGINS ORDER BY [LOGINNAME] 
    DROP TABLE  [TEMPDB].[DBO].[DB_ROLES]   
    DROP TABLE  #LOGINS

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here's a script that lists the server permissions and the highest level database permission for each login:

    Code:
    set nocount on
    
    --SQLServerSecurityGrid.sql
    --Bruce Lindman (sqlblindman@gmail.com), 2013
    --Shows the highest server and database security level for all server user accounts
    
    declare	@MaxPermissionLevel int = null
    	--null = All databases and logins
    	--db_owner = 0 --Can perform all configuration and maintenance activies.
    	--db_accessadmin = 1 --Can add or remove access for logins.
    	--db_securityadmin = 2 --Can modify role membership and manage permissions.
    	--db_ddladmin = 3 --Can run any DDL command.
    	--db_datawriter = 4
    	--db_datareader = 5
    	--db_backupoperator = 6
    	--db_denydatawriter = 7
    	--db_denydatareader = 8
    
    declare	@SQLString varchar(max)
    declare	@PivotColumns varchar(max)
    
    create table #LoginRights
    		(DisplayOrder int,
    		LoginName varchar(128),
    		Scope varchar(128),
    		PermissionLevel int,
    		Setting varchar(128))
    
    insert into #LoginRights
    		(DisplayOrder,
    		LoginName,
    		Scope,
    		PermissionLevel,
    		Setting)
    select	1 as DisplayOrder,
    		Logins.name as LoginName,
    		Roles.name as Scope,
    		0 as PermissionLevel,
    		'Granted' as Setting
    from	master.sys.server_principals as Logins
    		left outer join master.sys.server_role_members on Logins.principal_id = server_role_members.member_principal_id
    		left outer join master.sys.server_principals as Roles on server_role_members.role_principal_id = Roles.principal_id
    
    select	@SQLString = coalesce(@SQLString, '') + '
    use [' + name + ']
    
    ;with	Exceptions as
    			(select	''msdb'' as DatabaseName, ''MS_DataCollectorInternalUser'' as LoginName
    			union select ''msdb'' as DatabaseName, ''##MS_PolicyEventProcessingLogin##'' as LoginName
    			union select ''msdb'' as DatabaseName, ''##MS_PolicyTsqlExecutionLogin##'' as LoginName
    			union select null as DatabaseName, ''dbo'' as LoginName),
    		PermissionRanks as
    			(select	2 as DisplayOrder,
    					coalesce(suser_sname(Principals.sid), Principals.name) as LoginName,
    					''' + name + ''' as Scope,
    					case when suser_sname(Principals.sid) is null then ''Orphaned'' else convert(char(50), Roles.name) end as Setting,
    
    							case Roles.name --Just return the role with the highest level permissions.
    								when ''db_owner'' then 0 --Can perform all configuration and maintenance activies.
    								when ''db_accessadmin'' then 1 --Can add or remove access for logins.
    								when ''db_securityadmin'' then 2 --Can modify role membership and manage permissions.
    								when ''db_ddladmin'' then 3 --Can run any DDL command.
    								when ''db_datawriter'' then 4
    								when ''db_datareader'' then 5
    								when ''db_backupoperator'' then 6
    								when ''db_denydatawriter'' then 7
    								when ''db_denydatareader'' then 8
    								else 9
    							end as PermissionLevel,
    
    					ROW_NUMBER() over
    						(Partition by Principals.sid
    						order by 
    							case Roles.name --Just return the role with the highest level permissions.
    								when ''db_owner'' then 0 --Can perform all configuration and maintenance activies.
    								when ''db_accessadmin'' then 1 --Can add or remove access for logins.
    								when ''db_securityadmin'' then 2 --Can modify role membership and manage permissions.
    								when ''db_ddladmin'' then 3 --Can run any DDL command.
    								when ''db_datawriter'' then 4
    								when ''db_datareader'' then 5
    								when ''db_backupoperator'' then 6
    								when ''db_denydatawriter'' then 7
    								when ''db_denydatareader'' then 8
    								else 9
    							end,
    							Roles.name) as PermissionRank
    				from	sys.database_principals as Principals
    						inner join sys.database_role_members on Principals.principal_id = database_role_members.member_principal_id
    						inner join sys.database_principals as Roles on database_role_members.role_principal_id = Roles.principal_id
    						left outer join Exceptions
    							on ''' + name + ''' = coalesce(Exceptions.DatabaseName, ''' + name + ''')
    							and coalesce(suser_sname(Principals.sid), Principals.name) = coalesce(Exceptions.LoginName, suser_sname(Principals.sid), Principals.name)
    				where	Principals.Type not in (''R'') --Exclude roles
    						and coalesce(Exceptions.DatabaseName, Exceptions.LoginName) is null
    				)
    insert into #LoginRights
    		(DisplayOrder,
    		LoginName,
    		Scope,
    		PermissionLevel,
    		Setting)
    select	DisplayOrder,
    		LoginName,
    		Scope,
    		PermissionLevel,
    		Setting
    from	PermissionRanks
    where	PermissionRank = 1
    '
    from	master.sys.databases
    where	databases.state = 0 --Only check Online databases
    
    exec (@SQLString)
    
    if @MaxPermissionLevel is not null delete from #LoginRights where PermissionLevel > @MaxPermissionLevel or Scope is null
    
    ;with	PivotStrings as
    		(select	distinct
    				'
    		max(case when Scope = ''' + Scope + ''' then rtrim(Setting) else '''' end) as ''' + Scope + '''' as SQLString,
    				DENSE_RANK() over (order by DisplayOrder, Scope) as SortOrder
    		from	#LoginRights)
    select	@PivotColumns = coalesce(@PivotColumns + ', ', '') + SQLString
    from	PivotStrings
    order by SortOrder
    
    set		@SQLString = '
    select	convert(varchar(' + (select convert(varchar(3), MAX(len(LoginName))) from #LoginRights) + '), LoginName) as LoginName,' + @PivotColumns + '
    from	#LoginRights
    group by LoginName
    order by LoginName'
    
    exec (@SQLString)
    
    drop table #LoginRights
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •