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