Bookmark and Share
Server and database roles
(Publish Date: 2014-6-2 3:33pm, Total Visits: 472, Today: 1, This Week: 4, This Month: 4)

-- ADD a login to the serveradmin role

EXEC sp_addsrvrolemember mylogin, serveradmin;
GO

-- view the members of the server roles

use master;
GO

SELECT r.name AS RoleName, p.name AS PrincipalName
FROM sys.server_role_members AS srm
INNER JOIN sys.server_principals AS r
on srm.role_principal_id=r.principal_id
INNER JOIN sys.server_principals AS p
ON srm.member_principal_id=p.principal_id;
GO

-- drop mylogin from the serveradmin role

EXEC sp_addsrvrolemember mylogin, serveradmin;
GO

-- list the server permissions that have been granted

SELECT p.name AS PrincipalName, sp.permission_name AS PermissionName
FROM sys.server_permissions AS sp
INNER JOIN sys.server_principals AS p
on sp.grantee_principal_id = p.principal_id
ORDER BY p.name, sp.permission_name;
GO

-- ADD a user to the database role

EXEC sp_addrolemember my_dbrole, mylogin;
GO

-- drop a user from the database role

EXEC sp_droprolemember my_dbrole, mylogin;
GO

-- view the members of the database roles

SELECT r.name AS RoleName, p.name AS PrincipalName
FROM sys.database_role_members AS drm
INNER JOIN sys.database_principals AS r
on drm.role_principal_id=r.principal_id
INNER JOIN sys.database_principals AS p
ON drm.member_principal_id=p.principal_id;
GO