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.
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.
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,
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?
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
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,
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
dbaduck,
That works!
Thank you so much.
That works!
Thank you so much.
Do you mean Transfer logins from production server to dev?
if yes, read this link carefully:
http://support.microsoft.com/kb/246133