July 28, 2016 at 3:11 pm
Hello,
We've created many new database roles in our test environment and have, through some trial and error, manually adjusted permissions such that they will work properly with our applications. Now we are ready to deploy these roles into our production environment. But when doing a "CREATE TO" for a particular role, the "create" is all it does. The permissions associated with that role are not included in the script.
Does anyone know of a way to set up the "create" scripts to include the corresponding permissions and properties of that role so that it can be easily moved into another database?
As an example, we'd be looking for a script that does something like this:
CREATE ROLE [SomeDatabaseRole] AUTHORIZATION [SomeSchema]
GO
ALTER ROLE [SomeDatabaseRole] ADD MEMBER [SomeMember]
GO
GRANT SELECT ON [SomeSchema].[SomeTable1] TO [SomeDatabaseRole]
GO
GRANT SELECT ON [SomeSchema].[SomeTable2] TO [SomeDatabaseRole]
GO
etc...
Any help is greatly appreciated!
July 29, 2016 at 3:02 pm
Found a solution for what I'm looking for:
declare @DBRoleName varchar(40) = '[dbrolename]'
SELECT 'GRANT ' + dbprm.permission_name + ' ON ' + OBJECT_SCHEMA_NAME(major_id) + '.' + OBJECT_NAME(major_id) + ' TO ' + dbrol.name + char(13) COLLATE Latin1_General_CI_AS
from sys.database_permissions dbprm
join sys.database_principals dbrol on
dbprm.grantee_principal_id = dbrol.principal_id
where dbrol.name = @DBRoleName
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply