Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

SQL Server: Listing all the database roles for a given database user

SQL Server 2005 introduces Common Table Expressions (CTEs), which are great for recursive queries. Previously, in order to do recursion, we'd have to resort to a temporary table or a like type of structure. Therefore, if we have hierarchical lookups, CTEs are a great construct to use to navigate the hierarchy.

This brings me to database roles. In SQL Server, database roles can be nested, much like Windows groups can be nested for the operating system. However, there isn't a system stored procedure which can give all of the roles for a given database user. There is the dynamic management view sys.database_role_members, and it provides information on all role memberships with the exception of the public database role, which every database user is a member of. However, if you're looking to just report on the "tree" of database roles in a nested role situation, you'll have to resort to recursion. In SQL Server 2005 this is easily accomplished through the use of a CTE:
DECLARE @username NVARCHAR(128);
SET @username '<Database User>';

WITH CTE_Roles (role_principal_id)
AS
    (
    SELECT role_principal_id
        FROM sys.database_role_members
        WHERE member_principal_id USER_ID(@username)
        UNION ALL
    SELECT drm.role_principal_id
        FROM sys.database_role_members drm
                INNER JOIN CTE_Roles CR
                ON drm.member_principal_id CR.role_principal_id
    )
SELECT DISTINCT USER_NAME(role_principal_idRoleName
    FROM CTE_Roles
    UNION ALL
SELECT 'public'
    ORDER BY RoleName;
To make the query easier to use, I've used a variable, @username, at the front of the query in order to make it locate the username to change in order to report on any particular database user. Note the last UNION ALL, which adds the public database role. As I indicated, this isn't contained in the sys.database_role_members DMV, therefore, in order to include it in the list, a UNION ALL is required. Finally, note the DISTINCT in the SELECT query. It's entirely possible that in the nesting of database roles, especially in a complex security model, a user will be a member of a particular role through more than one path. We see this all the time on the OS side and it's equally possible within SQL Server. The DISTINCT keyword ensures that we only see a database role once in the resulting output.


Technorati Tags: | | | | | | |

Comments

Posted by Ranga on 2 November 2007
Very good script...thank you

I just added some more code to make it run for all databases....

if object_id('tempdb..#User_roles') is null
create table #User_roles(dbname varchar(100), rolename varchar(100))

delete #User_roles

EXEC sp_MSforeachdb 'use ?
DECLARE @username NVARCHAR(128);
SET @username = ''<username>'';

WITH CTE_Roles (role_principal_id)
AS
(
SELECT role_principal_id
FROM sys.database_role_members
WHERE member_principal_id = USER_ID(@username)
UNION ALL
SELECT drm.role_principal_id
FROM sys.database_role_members drm
INNER JOIN CTE_Roles CR
ON drm.member_principal_id = CR.role_principal_id
)

Insert into #User_roles(dbname ,rolename )
SELECT DISTINCT db_name(), USER_NAME(role_principal_id) RoleName
FROM CTE_Roles
ORDER BY RoleName;
'
select * from #User_roles order by dbname
Posted by Anonymous on 6 November 2007
Building upon my post from last Tuesday, if you know all the roles for a given user, you'll probably...
Posted by dg81328 on 27 January 2011

Here's a much, much simpler form built for a specific database:

SELECT dp.name, drm.member_principal_id AS PID, USER_NAME(drm.role_principal_id)

FROM

sys.database_principals dp

JOIN sys.database_role_members drm

ON dp.principal_id = drm.member_principal_id

Leave a Comment

Please register or log in to leave a comment.