Copying User Access From Database to Database
Published: Dec 12, 2014
This topic came about the other day when I was required to move some tables into a completely new database as part of a schema restructure and new project.

The issue was that I was that in order to maintain existing functionality I was using synonyms from the old database to the new, but this meant that permissions all required copying from the old database to the new otherwise access would fail.

This wouldn’t normally be a problem as it’s not too much effort to simply use the GUI to point and click your way through a few Windows Groups. However, in this instance, this was one of our main production servers which had over 30 groups, each with varying access.

So what was the approach I took? Well, a little t-SQL of course. It wasn’t as simple as I’d first hoped (double join to sysusers to start with) and therefore the code started to get a tad complicated, but I guess that’s to be expected. Also, as I like a bit of flexibility in my code, it also went incredibly dynamic so I admit it’s not the easiest to read. It really does work though. Honest.

Anyway, for those interested few, here’s the code I ended up with. It will copy all user access to the new database and add them to the relevant roles as it goes.

declare @sourceDatabase varchar(100), @destinationDatabase varchar(100), @sql varchar(max)
select @sourceDatabase = 'sourceDB',--'sourceDB',
      
@destinationDatabase = 'dbaTest'--'destDB'

select @sql = '
declare @loginTable table
(
id smallint identity(1, 1),
userName varchar(500),
roleName varchar(500)
)
;
with sourceDB as
(
select u.name as userName, s.name as roleName
from '
+ @sourceDatabase + '.dbo.sysusers u
join '
+ @sourceDatabase + '.dbo.sysmembers m
on u.uid = m.memberuid
join '
+ @sourceDatabase + '.dbo.sysusers s
on m.groupuid = s.uid
), destDB as
(
select u.name as userName, s.name as roleName
from '
+ @destinationDatabase + '.dbo.sysusers u
join '
+ @destinationDatabase + '.dbo.sysmembers m
on u.uid = m.memberuid
join '
+ @destinationDatabase + '.dbo.sysusers s
on m.groupuid = s.uid
)
insert into @loginTable
select s.userName, s.roleName
from sourceDB s
left join destDB d
on s.userName = d.userName
and s.roleName = d.roleName
where coalesce(d.userName, d.roleName) is null

declare @counter smallint = 1, @userSQL varchar(1500), @roleSQL varchar(1500)

while @counter <= (select max(id) from @loginTable)
begin
if not exists
(
select l.userName
from @loginTable l
join '
+ @destinationDatabase + '.dbo.sysusers u
on l.userName = u.name
where id = @counter
)
begin
select @userSQL = ''exec '
+ @destinationDatabase + '.dbo.sp_grantdbaccess ['' + userName + '']''
from @loginTable
where id = @counter

exec (@userSQL)
end

select @roleSQL = ''exec '
+ @destinationDatabase + '.dbo.sp_addrolemember '''''' + roleName + '''''', '''''' + userName + ''''''''
from @loginTable
where id = @counter

exec (@roleSQL)

select @counter += 1
end'

exec (@sql)
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron