Script User, Database and Object Permission in SQL Server 2005 and onward

Generate Create User Script on a Database

 

SELECT 'CREATE USER [' + name + '] for login [' + name + ']'

from sys.database_principals

where

 Type = 'U'

  and

name <>  'dbo'

Generate Database role permission on a Database

 

 

 

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

 

 

Generate Object Level peremission on a Database

 

 

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 

I have struggle a lot to find out those scripts but I always try to make like simple for other DBA's.
let me know if you have any questions.