5

every time I need to either restore a database or reinitialize a replication publication I first (on the restored to be or subscription database) I script all the permissions, triggers, indexes, synonyms, sometimes statistics

then I do the restore or (apply the snapshot) and then re-apply my saved scripts on the restored database.

I have some LIVE servers where the db_denydatawriter have been used profusely and although I have all the permissions as they used to be, because the db_denydatawriter comes with the restore (because it was in live) the new restored database has it now.

and it causes problems.

so much so that now I have this script (uses sp_foreachdb) just to remove that in test:

     declare @db_list NVARCHAR(MAX)


     SELECT @db_list  = STUFF((
                                SELECT ', ' + name FROM sys.databases
                                WHERE name NOT IN ('DBA','TABLEBACKUPS','MASTER','MSDB','MODEL','TEMPDB','distribution')
                                FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')

     SELECT [@db_list]  =@db_list 

     exec sp_foreachdb @database_list = @db_list
                     ,@exclude_list = 'dba,tablebackups,master,msdb,model,tempdb'
                     ,@command='use ?;begin try EXEC sp_droprolemember @rolename = [db_denydatawriter], @membername = [mycompany\Developers] end try begin catch end catch '    

question:

How can I delete all the permissions in the newly restored database before I apply my scripts containing the permissions before the restore?

In other words, what would be the easiest way to remove all existing permissions (after the restore) before I re-apply the former permissions?

1

2 Answers 2

1

Try sp_dbpermissions <db_name>.

It will give both grant and revoke scripts.

you can download the script by Kenneth Fisher from his blog.

1
  • I also use that script, and for automated needs it was a great inspiration for me.
    – Hybris95
    Dec 13, 2021 at 14:54
0

This should help drop all users after restore.

declare @sql nvarchar(max)

set @sql = ''

select @sql = @sql + '
print ''Dropping ' + name + '''drop user ' + name + ''
from dbo.sysusers
where name not in ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'public') and LEFT(name, 3) <> 'db_'
order by name

--print @sql
execute (@sql)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.