September 8, 2015 at 3:51 pm
Is there a command that will list the user members of a Database Role, as opposed to right-clicking the Database Role/Properties, which doesn't allow you to sort the members (in case you have hundreds)?
September 9, 2015 at 12:32 am
dan-572483 (9/8/2015)
Is there a command that will list the user members of a Database Role, as opposed to right-clicking the Database Role/Properties, which doesn't allow you to sort the members (in case you have hundreds)?
Quick suggestion
😎
USE [db_name];
GO
SET NOCOUNT ON;
SELECT
SDP.name
,SDP.principal_id
,SDP.type
,SDP.type_desc
,SDP.default_schema_name
,SDP.create_date
,SDP.modify_date
,SDP.owning_principal_id
,SDP.sid
,SDP.is_fixed_role
FROM sys.database_principals SDP
ORDER BY SDP.name;
September 9, 2015 at 1:10 am
SELECT u.name
FROM sys.database_principals u
JOIN sys.database_role_members rm ON u.principal_id = rm.member_principal_id
JOIN sys.database_principals r ON r.principal_id = rm.role_principal_id
WHERE r.name = 'yourrole'
ORDER BY u.name
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply