Menu
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
556
Answer: In SQL Server, there is a system view called sys. database_principals. You can run a query against this system view that returns all of the Users that have been created in SQL Server as well as information about these Users.
To find all the role assignments to users in SQL Server database, you can use the following query. SELECT r.name role_principal_name, m.name AS member_principal_name FROM sys. database_role_members rm JOIN sys. database_principals r ON rm.
Using SQL Server Management Studio First, move to “Object Explorer” and expand the database that you want. Next, under the database, expand the “Security” directory. Now, under Security, expand the “Users” option. This will display a list that contains all the users created in that database.
Expand the Azure SQL DB and navigate to security -> Roles -> Database Roles to get a list of available fixed database roles, expand the Azure SQL DB and navigate to Security -> Roles -> Database Roles.
In SQL 2005 and 2008 this information is most easily accessed in two catalog views.
This query should give you the information you're looking for.
select rp.name as database_role, mp.name as database_user from sys.database_role_members drm join sys.database_principals rp on (drm.role_principal_id = rp.principal_id) join sys.database_principals mp on (drm.member_principal_id = mp.principal_id) just depending on how you want to pass the parameter...assuming you'll use the id of the role
declare @roleID int select role_principal_id as roleID, user_name(role_principal_id) as roleName, member_principal_id as memberID, user_name(member_principal_id) as memberName from sys.database_role_members where role_principal_id = @roleID If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
