SQL Server 2005 Users & Roles

Question

All,

I am trying to script out a database in SQL Server Management Studio 2005. In my database users belong to different roles. Unfortunately I can't find how to script out the relationship between users and roles.

Thanks,

M

Answer 1

See these links:

http://www.sql-server-performance.com/articles/dba/object_permission_scripts_p1.aspx

http://blog.crowe.co.nz/blog/archive/2006/02/18/558.aspx

Answer 2

Here's some code we use to first check if a user is already mapped to a role, and if not, to do the mapping. You should be able to take the TSQL at the top that uses database_prinicipals and database_role_members and use that to extract out the relationships you have in your databaase.

   SELECT @sql = '  IF EXISTS (SELECT  * FROM ' + @DatabaseName + '.sys.database_principals a
            JOIN ' + @DatabaseName + '.sys.database_role_members b ON a.principal_id = b.role_principal_id
            JOIN    ' + @DatabaseName + '.sys.database_principals c ON b.member_principal_id = c.principal_id
            WHERE   a.Type = ''R'' AND a.Name = ''' + @CurrentDbRole + '''
            AND     c.type IN ( ''U'', ''S'') AND c.name = ''' + @MappedUser + ''')'                
        + ' BEGIN
                PRINT '''';
                PRINT N''The [' + @MappedUser + '] user is already a member of the [' 
                        + @CurrentDbRole + '] role in the [' + @DatabaseName + '] database. Skipping Role Member creation.''; 
                PRINT '''';
            END
            ELSE
            BEGIN
                PRINT '''';
                PRINT N''Adding the [' + @MappedUser + '] database user as member of the [' + @CurrentDbRole 
                        + '] role in the [' + @DatabaseName + '] database... '';
                PRINT '''';

                USE ' + @DatabaseName +'; 
                EXECUTE sp_addrolemember [' + @CurrentDbRole + '], [' + @MappedUser + '];   

                PRINT '''';
                PRINT ''Completed adding the user to the role.'';
                PRINT '''';             
            END; ';

Answer 3

The role membership is stored in sys.database_role_members:

select u.name as UserName,
  r.Name as RoleName
from sys.database_principals u
join sys.database_role_members m on u.principal_id = m.member_principal_id
join sys.database_principals r on m.role_principal_id = p.principal_id;

User may still get additional privileges by their server fixed roles membership, which is stored in sys.server_role_memebers and needs to be joined with sys.server_principals.