How to find which Database Roles are associated with a given User?
In SSMS I have a User
X and there are
P Database Roles available, how may I check what roles are added to a user
What have I tried:
In SSMS right click on database -> properties -> Permissions and see Explicit tab for a user
X. I can see Permissions not association between role and the user. The same is for role I'm interested in, I see only permissions for role.
EDIT: Regarding GUI solution, I have no Properties option available for Users nor Roles.
You can use the
sys.database_principals object to find this out:
SELECT u.[name] AS [UserName], r.[name] AS RoleName FROM sys.database_principals u JOIN sys.database_role_members drm ON u.principal_id = drm.member_principal_id JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id WHERE u.[type] IN ('S','U') --SQL User or Windows User AND u.[name] = N'X';
sys.database_role_members (Transact-SQL), Database users, application roles, and other database roles can be members of a database role. To add members to a role, use the ALTER� This will only return roles and the users associated if the role is not empty of members. select rp.name as 'Role Name', mp.name as 'User' from sys.database_role_members rm inner join sys.database_principals rp on rm.role_principal_id = rp.principal_id inner join sys.database_principals mp on rm.member_principal_id = mp.principal_id
Through the GUI:
Open the database that you want to check, open Security folder, open Users folder. Here you have a list of defined users for this database. Right click a user -> properties -> Membership. Here you see the defined roles for this database (custom roles also end up in this list). The user has/is a part of the role if it has an X/mark infront of it.
SELECT DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName FROM sys.database_role_members AS DRM RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.type = 'R' --AND DP2.name = 'YourUserName' ORDER BY DP1.name;
Which lists all roles and the users which are a member of it. (Script is from msdn link). This script goes from roles to users. For a specific user fill in the commented parameter. Or just use the script provided by Larnu.
List all permissions for a given role?, I can't find the reference, but here is a very descriptive script (I have had it in DatabaseUserName: Name of the associated user as defined in the database user account. ObjectType : Type of object the user/role is assigned permissions on. Any user can view their own role membership. To view other role memberships requires membership in the db_securityadmin fixed database role or VIEW DEFINITION on the database. The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission.
I do it this way:
select user_name(role_principal_id) from sys.database_role_members where member_principal_id = user_id('your_user');
SQL Server - List Database Roles and Asscoiated Users, to get the User name. This will only return roles and the users associated if the role is not empty of members. select rp.name as 'Role Name',� In SQL Server Management Studio (SSMS), when you click the user mapping tab, you can assign any database role in the database to a user, but you cannot see in a single screen all of the database roles assigned to each database user. In order to do that you must click on every user line and "collect" the database roles assigned to each user.
SQL Server Database Users to Roles Mapping Report, The server login name associated with that user; The assigned database role name. SQL Server Function to Show Users to Database Roles� A query like the this will find all roles granted to a user: select. grantee. from. see code depot for full scripts. sys.dba_role_privs. where. granted_role = 'DBA'. and.
How to Show All Oracle Database Privileges for a User, A database administrator (DBA) for Oracle can simply execute a query to view the rows in about user privileges related to the system , tables , and roles , respectively. GRANTEE is the name, role, or user that was assigned the privilege. To determine which users have direct grant access to a table we'll use the� I'm looking for a select statement which will retrieve a list of all database roles for my current connection. I want to create a view which will return all roles to a client software, such that the software can adjust its user interface according to the roles (e.g show/hide menu entries etc)
There are two types of database-level roles: fixed-database roles that are predefined in the database and user-defined database roles that you can create. Fixed-database roles are defined at the database level and exist in each database. Members of the db_owner database role can manage fixed-database role membership. There are also some special
- Regarding GUI solution, I have no Properties option available for Users nor Roles. Please have a look at my edit, thank you.
- @Yoda That is odd. Even on a server where i have basically no server rights, and limited database rights i can still check (only my own) users properties. What SSMS version (and SQL Server version) are you using? The latter shouldn't matter a whole lot, but SSMS version could.
- It's SSMS v17.3 and MSSQL Server 2014.
- @Yoda you could try and update your SSMS to a more recent version (17.3 is several years old). This seems like a GUI problem. Since you were able to access the underlying data, it's unlikely to be a rights issue.