Generate Object Level Permission

Run below script to generate users and permission in any databases.
Mostly it helps to generate users and its permission before refreshing from prod database or migrating to another server.




SELECT 'CREATE USER [' + name + '] for login [' + name + ']' from sys.database_principals
-- where
 --Type = 'U'
 --and
 -- name not like '%'
 --------------------------------------
 SELECT 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + '''' from sys.database_principals users inner join sys.database_role_members link on link.member_principal_id = users.principal_id inner join sys.database_principals roles on roles.principal_id = link.role_principal_id
 -----------------------------------
 if object_id('dbo.tempPermissions') is not null Drop table dbo.tempPermissions Create table tempPermissions(ID int identity , Queries Varchar(255)) Insert into tempPermissions(Queries) select 'GRANT ' + dp.permission_name collate latin1_general_cs_as + ' ON ' + s.name + '.' + o.name + ' TO ' + '[' +dpr.name + ']' FROM sys.database_permissions AS dp INNER JOIN sys.objects AS o ON dp.major_id=o.object_id INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id WHERE dpr.name NOT IN ('public','guest') declare @count int, @max int, @query Varchar(255) set @count =1 set @max = (Select max(ID) from tempPermissions) set @query = (Select Queries from tempPermissions where ID = @count) while(@count < @max) begin exec(@query) set @count += 1 set @query = (Select Queries from tempPermissions where ID = @count) end select * from tempPermissions drop table tempPermissions