Post reply

Generate a script to drop a DB user

  • September 2, 2022 at 10:34 am

    #4083215

    I have a script below to search ALL DBs for a specific user:

    DECLARE @DBuser_sql VARCHAR(4000)
    DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250))

    SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name FROM ?.sys.database_principals a
    LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
    WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
    INSERT @DBuser_table
    EXEC sp_MSforeachdb @command1=@dbuser_sql
    SELECT * FROM @DBuser_table
    where username = 'frodo'
    ORDER BY DBName

    The top part of the image below is the result of the script.  the bottom part is what i want to create based on the result.

    dbowner

    • This topic was modified 2 years, 2 months ago by  lan2022nguyen.
    • This topic was modified 2 years, 2 months ago by  lan2022nguyen.
    • This topic was modified 2 years, 2 months ago by  lan2022nguyen.
    • This topic was modified 2 years, 2 months ago by  lan2022nguyen.
    • This topic was modified 2 years, 2 months ago by  lan2022nguyen.

  • September 2, 2022 at 10:51 am

    #4083225

    Something like this should do it:

    SELECT CONCAT('USE ', u.DBName, '; ALTER ROLE [db_owner] DROP MEMBER [', u.Username, '];')
    FROM @USER_table u
    WHERE u.Username = 'Frodo'
    ORDER BY u.DBName;

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • September 2, 2022 at 11:00 am

    #4083228

    Thanks Phil, worked! 🙂

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

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