I noted some code for creating some database roles and assigning users to them the other day. Here's a more extensive script that can be used when transfering databases between environments (say dev, test and production) where you want to assign users to specific schemas and roles, clear out permissions, and/or restore database users after restoring a database. Using a script is preferable to doing everything by hand because it can produce more consistent results and will be faster. Additionally you can use the same script in different environments and set up different users and/or schemas as needed.
This script should save a lot of time, though could be even more automated (creating logins for instance).
Additionally if you have existing users with appropriate permissions you may want to (I have recently learned) assign the new user in the newly restored database the old sysid to keep those permissions in tact.
Another item of note - all permissions and roles are stored in the master database. If you want to return to default roles and permissions, hopefully you have backed up the database and can restore the master database to retrieve those settings. Restoring the master database requires putting the SQL Server database in single user mode and there are a few tricks to that which I have around here somewhere...
/*
Before running this script:
a. create the user + pwd on the server
b. replace this everywhere found below with your new user name and schema:
set @newuser = 'test'
set @dbschema = 'dbs'
c. run script below which does the following:
add schema to all dbs
add user to all dbs
add all objects to schema @dbschema
add roles to dbs
add user to roles
*/
/*
STEP 1: add schema to every db
*/
Declare @dbschema varchar(10)
Declare @sql varchar(4000)
set @dbschema = 'dbs'
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') ' +
' BEGIN ' +
' use ? ' +
' declare @s varchar(255) ' +
' if not exists(select name from sys.schemas where name=''' + @dbschema + ''')' +
' begin ' +
' set @s = ''create schema ' + @dbschema +
' '' exec (@s) ' +
' end ' +
' end '
print @sql
exec master.dbo.sp_MSforeachdb @sql
GO
/************
Step 2: change schema of all user defined objects
************/
Declare @sql varchar(2000)
Declare @cntitems int
Declare @dbschema varchar(20)
Declare @oldschema varchar(20)
Declare @itemname varchar(128)
Declare @db varchar(128)
set @dbschema = 'dbs'
if (select object_id('tempdb..##objnames')) is not null
drop table ##objnames
create table ##objnames (itemname varchar(128), oldschema varchar(128), dbname varchar(128))
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') ' +
' BEGIN ' +
' use ? ' +
' insert into ##objnames select t.name, s.name, ''?'' from ' +
' sys.all_objects t ' +
' left outer join sys.schemas s ' +
' on s.schema_id = t.schema_id ' +
' where is_ms_shipped = 0 and parent_object_id = 0 ' +
' and type_desc <> ''system_table'' and ' +
' t.name not like ''dt_%'' and t.name not like ''%#'' and s.name <> ''' + @dbschema +
' '' END'
print @sql
exec master.dbo.sp_MSforeachdb @sql
/*****
loop through the objects we found and change schemas
******/
select @cntitems=count(*) from ##objnames
While @cntitems > 0
begin
select top 1 @itemname=itemname, @oldschema = oldschema, @db = dbname from ##objnames
delete from ##objnames where itemname = @itemname and oldschema=@oldschema and dbname=@db
if (@itemname like '%-%')
print ('ERROR: cannot update item with - in name: ' + @itemname)
else
begin
set @sql = 'use ' + @db + ' ALTER SCHEMA ' + @dbschema + ' TRANSFER ' + @oldschema + '.' + @itemname
print (@sql)
exec (@sql)
end
select @cntitems=count(*) from ##objnames
end
/*********
step 3: delete orphan users now that schemas are fixed
**********/
-- var for exec sql
-- vars are for db loop below
declare @CNT int
declare @name char(128)
declare @sid varbinary(85)
declare @c int
declare @hexnum char(100)
set nocount on
/*
You may need to change the database owner...
set @sql = 'select @cnt = count(*) from master..syslogins l right join ' +
rtrim(@db) + '..sysusers u on l.sid = u.sid' +
' where l.sid is null and u.name = ''DBO'''
exec sp_executesql @sql,N'@cnt int out',@cnt out
if DB is not mapped to login that exists map DBO to SA
if @cnt = 1
begin
--print 'exec ' + @db + '..sp_changedbowner ''SA'''
-- exec sp_changedbowner 'SA'
end -- if @cnt = 1
*/
Declare @sql varchar(1000)
Declare @db varchar(50)
if (select object_id('tempdb..##orphans')) is not null
drop table ##orphans
create table ##orphans (orphan varchar(128), dbname varchar(128))
set @sql= 'BEGIN ' +
' use ? ' +
' insert into ##orphans select u.name, ''?'' from master..syslogins l right join ' +
' sysusers u on l.sid = u.sid ' +
' where l.sid is null and issqlrole <> 1 and isapprole <> 1 ' +
' and (u.name <> ''INFORMATION_SCHEMA'' and u.name <> ''guest'' and u.name <> ''dbo'' ' +
' and u.name <> ''system_function_schema'' ' +
' and u.name <> ''sys'') and u.name <> ''MS_DataCollectorInternalUser'' ' +
' END '
exec master.dbo.sp_MSforeachdb @sql
select @cnt = count(*) from ##orphans
WHILE @cnt > 0
BEGIN
select top 1 @name= orphan, @db = dbname from ##orphans
delete from ##orphans where orphan = @name
set @sql = 'exec ' + rtrim(@db) + '..sp_revokedbaccess ''' + rtrim(@name) + ''''
print @sql
exec (@sql) -- drop the orphan from the db
select @cnt = count(*) from ##orphans
end
go
/**********
Step 4: create roles and assign user to roles
*********/
Declare @newuser varchar(20)
declare @dbschema varchar(20)
Declare @sql varchar(1000)
set @dbschema = 'dbs'
set @newuser = 'test'
/* create the cto roles on each database */
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') ' +
' BEGIN ' +
' use ? ' +
' IF NOT EXISTS (select * from sys.database_principals where name=''dbs_select'') ' +
' create role dbs_select ' +
' end '
print @sql
exec master.dbo.sp_MSforeachdb @sql
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') BEGIN ' +
+ ' use ? ' +
' IF NOT EXISTS (select * from sys.database_principals where name=''dbs_execute'') ' +
'create role dbs_execute ' +
' end '
print @sql
exec master.dbo.sp_MSforeachdb @sql
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') BEGIN ' +
+ ' use ? ' +
' IF NOT EXISTS (select * from sys.database_principals where name=''dbs_delete'') ' +
'create role dbs_delete ' +
' end '
print @sql
exec master.dbo.sp_MSforeachdb @sql
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') ' +
' BEGIN ' +
' use ? ' +
' IF NOT EXISTS (select * from sys.database_principals where name=''dbs_update'') ' +
' create role dbs_update ' +
' end '
print @sql
exec master.dbo.sp_MSforeachdb @sql
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') BEGIN ' +
+ ' use ? ' +
' IF NOT EXISTS (select * from sys.database_principals where name=''dbs_insert'') ' +
' create role dbs_insert ' +
' end '
print @sql
exec master.dbo.sp_MSforeachdb @sql
/* grant these roles permissions to cto schema in this database */
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') ' +
' begin ' +
' use ? ' +
' grant execute on schema :: ' + @dbschema + ' TO dbs_execute ' +
' end '
print (@sql)
exec master.dbo.sp_MSforeachdb @sql
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') ' +
' begin ' +
' use ? ' +
' print ''?'' '+
' grant select on schema :: ' + @dbschema + ' TO dbs_select ' +
' end '
print (@sql)
exec master.dbo.sp_MSforeachdb @sql
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') ' +
' begin ' +
' use ? ' +
' grant delete on schema :: ' + @dbschema + ' TO dbs_delete ' +
' end '
print (@sql)
exec master.dbo.sp_MSforeachdb @sql
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') ' +
' begin ' +
' use ? ' +
' grant insert on schema :: ' + @dbschema + ' TO dbs_insert ' +
' end '
print (@sql)
exec master.dbo.sp_MSforeachdb @sql
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') ' +
' begin ' +
' use ? ' +
' grant update on schema :: ' + @dbschema + ' TO dbs_update ' +
' end '
print (@sql)
exec master.dbo.sp_MSforeachdb @sql
set @sql =
'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') ' +
' begin ' +
' use ? ' +
'if not exists( select u.name from master..syslogins l right join ' +
+ '?.dbo.sysusers u
on l.sid = u.sid
where u.name=''' + @newuser + ''') create user ' + @newuser
+ ' end '
print (@sql)
exec master.dbo.sp_MSforeachdb @sql
/* assign user to roles in each db */
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') BEGIN ' +
' use ? ' +
' exec sp_addrolemember ''dbs_execute'', ' + @newuser +
' end '
print @sql
exec master.dbo.sp_MSforeachdb @sql
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') BEGIN ' +
' use ? ' +
' exec sp_addrolemember ''dbs_select'', ' + @newuser +
' end '
print (@sql)
exec master.dbo.sp_MSforeachdb @sql
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') BEGIN ' +
' use ? ' +
' exec sp_addrolemember ''dbs_insert'', ' + @newuser +
' end '
print (@sql)
exec master.dbo.sp_MSforeachdb @sql
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') BEGIN ' +
' use ? ' +
' exec sp_addrolemember ''dbs_update'', ' + @newuser +
' end '
print (@sql)
exec master.dbo.sp_MSforeachdb @sql
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') BEGIN ' +
' use ? ' +
' exec sp_addrolemember ''dbs_delete'', ' + @newuser +
' end '
print (@sql)
exec master.dbo.sp_MSforeachdb @sql
set @newuser='test'
set @dbschema = 'dbs'
set @sql = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') ' +
' begin ' +
' use ? ' +
' declare @s varchar(255) ' +
' set @s = ''alter user ' + @newuser + ' with default_schema = ' + @dbschema + ' '' ' +
' exec (@s) ' +
' end '
print (@sql)
exec master.dbo.sp_MSforeachdb @sql
-- Remove temporary tables
drop table ##orphans, ##objnames
