Wednesday, October 7, 2015

Finding All Databases Where User is not in Role

This script will find all the databases (matching a certain pattern) on the server where a given user is not a member of a given role. Because sometimes you need to know that.

-- Be sure to change the User and Role variables. 
declare @Sql      nvarchar(4000)    set @Sql = ''
declare @UserName sysname           set @UserName = 'Larry'
declare @RoleName sysname           set @RoleName = 'db_owner'

set @Sql = 'USE [?]

            IF ''?'' LIKE ''MYDATABASE%''
            BEGIN
                IF NOT EXISTS (SELECT * 
                                 FROM sys.database_role_members
                                WHERE USER_NAME(role_principal_id)   = ''' + @RoleName + '''
                                  AND USER_NAME(member_principal_id) = ''' + @UserName + ''')
                BEGIN
                    RAISERROR(''Database %s - user %s not in role %s'', 10, 1, ''?'',
                              ''' + @UserName + ''', ''' + @RoleName + ''') WITH NOWAIT
                END
            END'

exec sp_msforeachdb @Sql