TSQL to generate GRANT statements from a database

Published 17 August 07 03:45 AM | SQL Master 

select p.state_desc + ' ' + p.permission_name + ' OBJECT::' + s.name collate Latin1_general_CI_AS+ o.name collate Latin1_general_CI_AS+ ' TO ' + u.name collate Latin1_general_CI_AS + 'GO', p.* from sys.database_permissions p inner join sys.objects o on p.major_id = o.object_id inner join sys.schemas s on s.schema_id = o.schema_id inner join sys.database_principals u on p.grantee_principal_id = u.principal_id

The above script is useful when you want to GRANT access to set of users or roles within your database. I have seen this question in the forums, hence the blog is here.

Comments

# SSQA.net - SqlServer-QA.net said on August 17, 2007 4:07 AM:

select p . state_desc + ' ' + p . permission_name + ' OBJECT::' + s . name collate Latin1_general_CI_AS

# Other SQL Server Blogs around the Web said on August 17, 2007 4:14 AM:

select p . state_desc + ' ' + p . permission_name + ' OBJECT::' + s . name collate Latin1_general_CI_AS

Anonymous comments are disabled

About SQL Master

**__________________________________** SQL Server MVP, Sr. DBA & industry expert. - Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.