Script – Server Level – Database Level Permissions

The Script scripts all the permission granted to each login on Server and Database Level.

DECLARE @Start int=1
DECLARE @End int
DECLARE @DatabaseName varchar(100)
DECLARE @cmd nvarchar(4000)
DECLARE @Permission Table (ID int Identity,Script varchar(max))
DECLARE @DbList Table (id int identity,DBName varchar(100))

INSERT INTO @DbList(DBName)
SELECT name FROM sys.databases where name not in ('master','model','msdb','tempdb') and state=0
SET @End=@@ROWCOUNT


  insert into @Permission (Script) VALUES ('---'+QUOTENAME('Server Level Permission')+'---')
  
  insert into @Permission
  SELECT 'GRANT '+sp.[permission_name] COLLATE DATABASE_DEFAULT+' TO '+QUOTENAME(sps.name) from sys.server_permissions sp 
  join sys.server_principals sps on sp.grantee_principal_id=sps.principal_id 
  and 
  --sps.type not in ('S','C') and 
  sps.principal_id not in (2)

  insert into @Permission (Script) VALUES ('---'+QUOTENAME('Server Level Role Permission')+'---')
  
  insert into @Permission
  SELECT 'ALTER SERVER ROLE '+ QUOTENAME(sPrinc.name) +' ADD MEMBER '+QUOTENAME(sRole.name)
  FROM sys.server_role_members AS sRo JOIN sys.server_principals AS sPrinc
  ON sRo.member_principal_id = sPrinc.principal_id JOIN sys.server_principals AS sRole
  ON sRo.role_principal_id = sRole.principal_id	

WHILE @Start <=@End
BEGIN

  SELECT @DatabaseName=DBName FROM @DbList WHERE id=@Start
  

  SET @cmd='SELECT CASE 
  WHEN dps.[permission_name]=''CONNECT''
       THEN ''GRANT ''+ dps.[permission_name] COLLATE DATABASE_DEFAULT +'' TO ''+ QUOTENAME(dp.name) --QUOTENAME(DB_NAME()) 
  WHEN dps.[permission_name]=''SELECT'' 
       THEN  ''GRANT ''+dps.[permission_name] COLLATE DATABASE_DEFAULT +'' ON ''+QUOTENAME(sc.name)+''.'' +QUOTENAME(so.name)+ '' TO ''+QUOTENAME(dp.name) --QUOTENAME(DB_NAME()) 
  WHEN dps.[permission_name]=''UPDATE'' 
       THEN  ''GRANT ''+dps.[permission_name] COLLATE DATABASE_DEFAULT +'' ON ''+QUOTENAME(sc.name)+''.'' +QUOTENAME(so.name)+ '' TO ''+QUOTENAME(dp.name) --QUOTENAME(DB_NAME())
  WHEN dps.[permission_name]=''DELETE'' 
       THEN  ''GRANT ''+dps.[permission_name] COLLATE DATABASE_DEFAULT +'' ON ''+QUOTENAME(sc.name)+''.'' +QUOTENAME(so.name)+ '' TO ''+QUOTENAME(dp.name) --QUOTENAME(DB_NAME())
  WHEN dps.[permission_name]=''EXECUTE''
       THEN ''GRANT ''+dps.[permission_name] COLLATE DATABASE_DEFAULT  +'' ON ''+QUOTENAME(sc.name)+''.'' +QUOTENAME(so.name)+ '' TO ''+QUOTENAME(dp.name) --QUOTENAME(DB_NAME()) 
  ELSE
  dps.[permission_name]
  END
  FROM ' +QUOTENAME(@DatabaseName)+'.sys.database_permissions dps 
    join '+QUOTENAME(@DatabaseName)+'.sys.database_principals dp on dp.principal_id=dps.grantee_principal_id 
    join '+QUOTENAME(@DatabaseName)+'.sys.objects so on so.[object_id]= dps.major_id
    join '+QUOTENAME(@DatabaseName)+'.sys.schemas sc on sc.[schema_id]=so.[schema_id]
  
  WHERE dp.name not in (''dbo'',''public'',''guest'',''db_executor'')' 



  insert into @Permission (Script) VALUES ('USE '+QUOTENAME(@DatabaseName))
  insert into @Permission (Script) VALUES ('GO')

  insert into @Permission
  exec(@cmd)

  ---print @cmd

  SET @cmd=''
  SET @cmd='SELECT ''ALTER ROLE ''+QUOTENAME(dRole.name)+'' ADD MEMBER ''+QUOTENAME(dPrinc.name)
  FROM '+QUOTENAME(@DatabaseName)+'.sys.database_role_members AS dRo JOIN '
        +QUOTENAME(@DatabaseName)+'.sys.database_principals AS dPrinc
  ON dRo.member_principal_id = dPrinc.principal_id JOIN sys.database_principals AS dRole
  ON dRo.role_principal_id = dRole.principal_id where dPrinc.name !=''dbo'''
  insert into @Permission
  exec(@cmd)
  print @cmd
  SET @Start=@Start+1
END
SELECT * FROM @Permission

Permission

Leave a Reply

Your email address will not be published. Required fields are marked *