Link to home
Start Free TrialLog in
Avatar of JOSHUABT
JOSHUABT

asked on

Export/Import Fixed Server Role membership in SQL 2005

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.
Avatar of Reza Rad
Reza Rad
Flag of New Zealand image

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
Avatar of JOSHUABT
JOSHUABT

ASKER

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,
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?
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

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,
ASKER CERTIFIED SOLUTION
Avatar of DBAduck - Ben Miller
DBAduck - Ben Miller
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
dbaduck,

That works!
Thank you so much.