I want to get list of all database users with specified role. Role is a parameter for stored procedure or function.
Somethinf like a select statement with user name and its role.
+============+==========
| User name | Role |
+============+==========
MS SQL Server 2008
Something like this can help you:
select sys.server_role_members.role_principal_id ,role.name as rolename
, sys.server_role_members.member_principal_id , member.name as membername
from sys.server_role_members
join sys.server_principals as role
on sys.server_role_members.role_principal_id = role.principal_id
join sys.server_principals as member
on sys.server_role_members. member_principal_id = member_principal_id;