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.
Windows Server 2008Microsoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
JOSHUABT

8/22/2022 - Mon
Reza Rad

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
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,
Reza Rad

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?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
DBAduck - Ben Miller

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

JOSHUABT

ASKER
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
DBAduck - Ben Miller

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
JOSHUABT

ASKER
dbaduck,

That works!
Thank you so much.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.