Export/Import Fixed Server Role membership in SQL 2005

JOSHUABT
JOSHUABT used Ask the Experts™
on
Hello,
Does any one have T-SQL script to export and import all Fixed Server Role members in SQL 2005?
I need to export the list of members from production and import to dev server.

Thank you for your help in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reza RadConsultant, Trainer

Commented:
what do you mean by export list of members?!
Do you mean Transfer logins from production server to dev?
if yes, read this link carefully:
http://support.microsoft.com/kb/246133

Author

Commented:
Reza_Rad,

Thank you for your response.
The script in the article you provided transfers SQL logins, but not their fixed server role membership.

If possible, I want to export all fixed server role membres from PROD and import into DEV.

Thanks,
Reza RadConsultant, Trainer

Commented:
As you say, they are FIXED server roles, and should be already exists in DEV, so why you want to import them again?
Could you explain more?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Commented:
Do you mean something like this?

WITH Logins
AS (
	SELECT 
		M.role_principal_id AS RoleId,
		M.member_principal_id AS UserId,
		P.name AS LoginName
	FROM sys.server_role_members M 
	INNER JOIN sys.server_principals P ON m.member_principal_id = P.principal_id
)
SELECT 
	Logins.LoginName,
	P1.name AS RoleName 
FROM Logins
INNER JOIN sys.server_principals P1 ON Logins.RoleId = P1.principal_id

Open in new window

Author

Commented:
Sorry, my question probably was clear enough.

SQL logins have been transferred from PROD to DEV using the script provided in http://support.microsoft.com/kb/246133.
However, logins' fixed server role membership cannot be transferred with the same script.
I am looking for a script that will take care of this role memebership transfer.

Thanks,
Commented:
You could use something like this on the old server to generate the commands to use and you can edit it before you run it on the new server.

SELECT 'EXEC sp_addsrvrolemember ''' + p.name + ''', ''' + p1.name + '''' AS command
FROM sys.server_role_members sr
INNER JOIN sys.server_principals p ON sr.member_principal_id = p.principal_id
INNER JOIN sys.server_principals p1 ON sr.role_principal_id = p1.principal_id
WHERE p.name <> 'sa'

Open in new window


Then you would just copy the output of this and execute it on the new server.

Author

Commented:
dbaduck,

That works!
Thank you so much.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial