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_id) RoleName
    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: DATABASE | SQL | T-SQL | SQL Server | Microsoft SQL Server | SQL Server 2005 | Security | Database Security