tsql - T-SQL Script current user database permissions -
occasionally have restore db's our production sql server test sql instance. when database has been restored, manually restore correct access permissions (e.g. db owner/ reader/ writer) restored database. process works well, except having manually screenshot permissions before restore , reapply them image taken.
is there easy way use t-sql store current permissions user before database restore , reapply same permissions once restoration complete?
the answer question 1 jeff.
but script howard practical, added column, generates tsql syntax information. can copy , run sql 'replicate' permissions db.
select dp.permission_name collate latin1_general_cs_as permission, t.table_schema + '.' + o.name object, dpr.name username , 'grant ' + dp.permission_name collate latin1_general_cs_as + ' on ' + t.table_schema + '.' + o.name + ' ' + dpr.name sys.database_permissions dp inner join sys.objects o on dp.major_id=o.object_id inner join sys.schemas s on o.schema_id = s.schema_id inner join sys.database_principals dpr on dp.grantee_principal_id=dpr.principal_id inner join information_schema.tables t on table_name = o.name dpr.name not in ('public','guest') order permission, object,username
Comments
Post a Comment