Feeds:
Posts
Comments

Archive for July, 2019

Quick one today:

Occasionally, we also have a need to retrieve all the server roles a login is associated with.¬†Earlier we covered a similar post where covered a script to retrieve all database users & their database role assignments; In this we’ll¬† cover similar task, but for Server Roles & Logins.

--
-- List all Server roles a login is part of
--
SELECT DB_NAME(),  RP.type_desc AS [Role Type], RP.name, MP.name, MP.type_desc AS [LoginType]
FROM sys.server_role_members AS R
INNER JOIN sys.server_principals AS RP
	ON RP.principal_id = R.role_principal_id
	AND RP.type_desc = 'SERVER_ROLE'
INNER JOIN sys.server_principals AS MP
	ON MP.principal_id = R.member_principal_id
GO

Just like last time, if you run this from multi-script window, we could compare between two different environments.

 

Hope this helps,
_Sqltimes

 

Advertisements

Read Full Post »