« SQL Server 2012 RTM sample databases
An unexpected error occurred in Report Processing »

Serverrollen ermitteln

In diesem kurzen Beitrag geht es um die Frage, wie erstellt man ein Skript, um die Serverrollen einzelner Logins auf einen anderen Server zu übertragen.

Die Lösung ist relativ einfach: Man ermittelt anhand der Tabelle master..syslogins die erteilten Rechte und erzeugt darüber ein Skript, welches später auf dem anderen Server ausgeführt werden kann.

-- Server-Rollen
WITH serv_permission
AS
(
SELECT 'EXEC master..sp_addsrvrolemember @loginame = N'''+loginname+''', @rolename = N''sysadmin'';' AS script FROM MASTER..syslogins
WHERE sysadmin = 1
UNION
SELECT 'EXEC master..sp_addsrvrolemember @loginame = N'''+loginname+''', @rolename = N''securityadmin'';' AS script FROM MASTER..syslogins
WHERE securityadmin = 1
UNION
SELECT 'EXEC master..sp_addsrvrolemember @loginame = N'''+loginname+''', @rolename = N''serveradmin'';' AS script FROM MASTER..syslogins
WHERE serveradmin = 1
UNION
SELECT 'EXEC master..sp_addsrvrolemember @loginame = N'''+loginname+''', @rolename = N''setupadmin'';' AS script FROM MASTER..syslogins
WHERE setupadmin = 1
UNION
SELECT 'EXEC master..sp_addsrvrolemember @loginame = N'''+loginname+''', @rolename = N''processadmin'';' AS script FROM MASTER..syslogins
WHERE processadmin = 1
UNION
SELECT 'EXEC master..sp_addsrvrolemember @loginame = N'''+loginname+''', @rolename = N''diskadmin'';' AS script FROM MASTER..syslogins
WHERE diskadmin = 1
UNION
SELECT 'EXEC master..sp_addsrvrolemember @loginame = N'''+loginname+''', @rolename = N''dbcreator'';' AS script FROM MASTER..syslogins
WHERE dbcreator = 1
UNION
SELECT 'EXEC master..sp_addsrvrolemember @loginame = N'''+loginname+''', @rolename = N''bulkadmin'';' AS script FROM MASTER..syslogins
WHERE bulkadmin = 1
)
SELECT script
FROM serv_permission
ORDER BY script;

  Serverrollen ermitteln

administration
SQL Server
 Print article This entry was posted by cmu on 14.03.12 at 08:54:00 . Follow any responses to this post through RSS 2.0.