Database restore script - roles, users, permissions and schemas

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

The statement did not return a resultset - bug: MS JDBC driver + SQL Server 2000

There is a bug that has been driving me insane. I thought I was doing something wrong but apparently it's a bug in Microsoft SQL Server JDBC driver when using SQL Server 2000.

I had a bunch of sprocs that were failing for no apparent reason. I thought it was code change to incorporate a database connection pool. Coincidentally I updated the Microsoft JDBC driver at the same time and forgot about it.

Oddly enough the fix is to add "SET NOCOUNT ON" to stored procedures and then things run as expected.

The statement did not resturn a ResultSet

The Resultset has No Current Row

If you get this error using Microsoft JDBC driver to return records as a ResultSet:

The ResultSet has no current row

ResultSet rs = getResultSetFunction();
rs.next() <-----------

"ResultSet has not current row" does not mean that the resultset has not returned any records. It means that the recordset has not been positioned to the row you want to read from. When you first load the recordset, it's not positioned to any row.

So rs.next() will position the resultset to the first row (i.e. make the first row the current row), so you can read some data from that row.

The result set needs to have a current row to read some data from it.