Post reply

Move logins to new role

  • August 23, 2018 at 9:01 am

    #378389

    Is there a way I can script out moving users out of one role into another?  I inherited a database that has a large number of users and just about all of them have db_datareader and db_datawriter.  I want to get them out of those roles and into a user defined role in order to limit access.  I can go one by one, but I was wondering if anyone is aware of a way to grab all users at once and move them out of the db_datareader and db_datawriter roles into one that I setup.

  • August 23, 2018 at 9:24 am

    #2003071

    It's the users you need to change, not the logins. A user is a database object, while a login in Server based.

    You could use some dynamic SQL to achieve this. You'll need to replace the appropriate parts, however, this should get you down the right path:

    DECLARE @TargetDB sysname, @Newrole sysname;
    DECLARE @SQL nvarchar(MAX);
    SET @TargetDB = 'YourDB';
    SET @Newrole = 'YourNewRoleName';
    SET @SQL = N'USE ' + QUOTENAME(@TargetDB) + N';' + NCHAR(10) +
        STUFF((SELECT DISTINCT
             NCHAR(10) +
             N'ALTER ROLE ' + QUOTENAME(@Newrole) +' ADD MEMBER ' + QUOTENAME(dpu.name) + N';'
          FROM sys.database_principals dpu
            JOIN sys.database_role_members drm ON dpu.principal_id = drm.member_principal_id
            JOIN sys.database_principals dpr ON drm.role_principal_id = dpr.principal_id
          WHERE dpr.name IN ('db_datareader','db_datawriter')
          FOR XML PATH(N'')),1,1,N'');
    PRINT @SQL;
    --EXEC sp_executesql @SQL; --Uncomment to run
    SET @SQL = N'USE ' + QUOTENAME(@TargetDB) + N';' + NCHAR(10) +
        STUFF((SELECT NCHAR(10) +
             N'ALTER ROLE ' + QUOTENAME(dpr.name) +' DROP MEMBER ' + QUOTENAME(dpu.name) + N';'
          FROM sys.database_principals dpu
            JOIN sys.database_role_members drm ON dpu.principal_id = drm.member_principal_id
            JOIN sys.database_principals dpr ON drm.role_principal_id = dpr.principal_id
          WHERE dpr.name IN ('db_datareader','db_datawriter')
          FOR XML PATH(N'')),1,1,N'');
    PRINT @SQL;
    --EXEC sp_executesql @SQL; --uncomment to run

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply