Sql-server – how to remove all permissions in a database

permissionsreplicationrestorescriptingsql server

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?

Best Answer

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)
Related Question