SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transfer server roles between instances of SQL 2005


Transfer server roles between instances of SQL 2005

Author
Message
Waseem Jaleel
Waseem Jaleel
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1431 Visits: 606
I am working on automating a process to sync logins and server roles between 2005 instances. I have a script which will move the logins and passwords, but the server roles are required as well. Is there any script which gets this done.

Thanks..
Waseem Jaleel
Waseem Jaleel
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1431 Visits: 606
I just created a script today since I could not find it anywhere.. it would be gr8 if any of you would go through this and let me know if you think this would correctly transfer the server roles after transfering the logins and passwords (between 2005 instances)

Here is the script:

declare @loginname as varchar(100)
declare @sysadmin as bit
declare @securityadmin as bit
declare @serveradmin as bit
declare @setupadmin as bit
declare @processadmin as bit
declare @diskadmin as bit
declare @dbcreator as bit
declare @bulkadmin as bit
declare @tmpstr as varchar(500)

DECLARE srvrole_curs CURSOR FORWARD_ONLY FOR
select convert(varchar(100),suser_sname(sid)),
sysadmin,
securityadmin,
serveradmin,
setupadmin,
processadmin,
diskadmin,
dbcreator,
bulkadmin
from syslogins
where sysadmin<>0
or securityadmin<>0
or serveradmin<>0
or setupadmin <>0
or processadmin <>0
or diskadmin<>0
or dbcreator<>0
or bulkadmin<>0
OPEN srvrole_curs

FETCH NEXT FROM srvrole_curs INTO @loginname, @sysadmin, @securityadmin,
@serveradmin,@setupadmin,@processadmin,@diskadmin,@dbcreator,@bulkadmin

while (@@fetch_status = 0)
BEGIN


if @sysadmin=1
begin
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @loginname + ''', @rolename=''sysadmin'''
PRINT @tmpstr
end


if @securityadmin=1
begin
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @loginname + ''', @rolename=''securityadmin'''
PRINT @tmpstr
end



if @serveradmin=1
begin
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @loginname + ''', @rolename=''serveradmin'''
PRINT @tmpstr
end


if @setupadmin=1
begin
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @loginname + ''', @rolename=''setupadmin'''
PRINT @tmpstr
end


if @processadmin=1
begin
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @loginname + ''', @rolename=''processadmin'''
PRINT @tmpstr
end



if @dbcreator=1
begin
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @loginname + ''', @rolename=''dbcreator'''
PRINT @tmpstr
end


if @diskadmin=1
begin
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @loginname + ''', @rolename=''diskadmin'''
PRINT @tmpstr
end


if @bulkadmin=1
begin
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @loginname + ''', @rolename=''bulkadmin'''
PRINT @tmpstr
end


FETCH NEXT FROM srvrole_curs INTO @loginname, @sysadmin, @securityadmin,
@serveradmin,@setupadmin,@processadmin,@diskadmin,@dbcreator,@bulkadmin



end
close srvrole_curs
deallocate srvrole_curs
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)

Group: General Forum Members
Points: 136392 Visits: 9519
Well, two things that I noticed: 1) unnecessary use of a Cursor, and 2) "From syslogins" is invalid in 2005, should be "From sys.syslogins".

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)

Group: General Forum Members
Points: 136392 Visits: 9519
Here is a non-cursor version of your sript:

Declare @Prefix varchar(255)
Declare @tmpstr varchar(MAX)

Set @Prefix = '
exec master.dbo.sp_addsrvrolemember @loginame='''

Set @tmpstr=''

Select @tmpstr = @tmpstr
+ Case When sysadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''sysadmin''' Else '' End
+ Case When securityadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''securityadmin''' Else '' End
+ Case When serveradmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''serveradmin''' Else '' End
+ Case When setupadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''setupadmin''' Else '' End
+ Case When processadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''processadmin''' Else '' End
+ Case When diskadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''diskadmin''' Else '' End
+ Case When dbcreator = 1 Then @Prefix + [LoginName] + ''', @rolename=''dbcreator''' Else '' End
+ Case When bulkadmin = 1 Then @Prefix + [LoginName] + ''', @rolename=''bulkadmin''' Else '' End
From (select convert(varchar(100),suser_sname(sid)) as [LoginName],
sysadmin,
securityadmin,
serveradmin,
setupadmin,
processadmin,
diskadmin,
dbcreator,
bulkadmin
from sys.syslogins
where sysadmin<>0
or securityadmin<>0
or serveradmin<>0
or setupadmin <>0
or processadmin <>0
or diskadmin<>0
or dbcreator<>0
or bulkadmin<>0) L

Print @tmpstr



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Waseem Jaleel
Waseem Jaleel
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1431 Visits: 606
Thank you so much.. I will follow this approach as well in the scripts I will be creating as well.
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)

Group: General Forum Members
Points: 136392 Visits: 9519
Glad I could help.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
S. Kusen
S. Kusen
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10320 Visits: 1170

/*
This script will script the role members for all roles on the database.

This is useful for scripting permissions in a development environment before refreshing
development with a copy of production. This will allow us to easily ensure
development permissions are not lost during a prod to dev restoration.

*/

/*********************************************/
/********* DB CONTEXT STATEMENT *********/
/*********************************************/
SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '' AS [-- SQL STATEMENTS --],
2 AS [-- RESULT ORDER HOLDER --]

UNION


/*********************************************/
/********* DB ROLE PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
3 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT 'EXEC sp_addrolemember @rolename ='
+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' +
SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''')
AS [-- SQL STATEMENTS --],
3 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_role_members AS rm
WHERE USER_NAME(rm.member_principal_id) IN (
--get user names on the database
SELECT [name]
FROM sys.database_principals
WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
and [type] IN ('G', 'S', 'U')
-- S = SQL user, U = Windows user, G = Windows group
)
--ORDER BY rm.role_principal_id ASC


UNION

SELECT '' AS [-- SQL STATEMENTS --],
4 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/********* OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT CASE
WHEN perm.state <> 'W' THEN perm.state_desc
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) +
'.' + QUOTENAME(obj.name) --select, execute, etc on specific objects
+ CASE
WHEN cl.column_id IS NULL THEN SPACE(0)
ELSE '(' + QUOTENAME(cl.name) + ')'
END
+ SPACE(1) + 'TO' + SPACE(1) +
QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --]
FROM
sys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
--WHERE usr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC



UNION

SELECT '' AS [-- SQL STATEMENTS --],
6 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/********* DB LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
7 AS [-- RESULT ORDER HOLDER --]
UNION
SELECT CASE
WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
ELSE 'GRANT'
END
+ SPACE(1) + perm.permission_name --CONNECT, etc
+ SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO
+ CASE
WHEN perm.state <> 'W' THEN SPACE(0)
ELSE SPACE(1) + 'WITH GRANT OPTION'
END
AS [-- SQL STATEMENTS --],
7 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
--WHERE usr.name = @OldUser

WHERE [perm].[major_id] = 0
AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

ORDER BY [-- RESULT ORDER HOLDER --]



I created this script to do a similar thing for database level permissions.

This is useful for scripting out access in development, restoring a database from production/staging into a development environment, and then running the output from this script, column 1, to restore permissions in development quickly and easily.

If anyone has any comments or if something else exists that does a similar function, it would be appreciated.

My use:
Developer has dbo rights in development, but no rights in production. When restoring the DB from production to development, their dbo rights are washed away. So before the restore, i script out the permissions, then run the output to restore their dbo rights.

Steve
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)

Group: General Forum Members
Points: 136392 Visits: 9519
SK: Nice script. However, two things:

1) Could you please edit your code to bring the indentation in to a readable level? It tends to mess up the reading panes for a lot of browsers (including mine).

2) The OP wanted to script out the memberships for SERVER roles, not database roles.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
S. Kusen
S. Kusen
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10320 Visits: 1170
RBarry,

Thought it might have just been my screen resolution. I fixed the tabs in there.

I did note that the script was "similar" except for database level instead of login level. Really just wanted to take it to another level since the other script was already posted.

Thanks!
Steve
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)SSC Guru (136K reputation)

Group: General Forum Members
Points: 136392 Visits: 9519
SK (1/31/2009)
I did note that the script was "similar" except for database level instead of login level. Really just wanted to take it to another level since the other script was already posted.

Sorry, SK. I must have missed that amid my furious scrolling back and forth... Sick

Thanks for fixing that... Smile

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search