How can I remove a specific security user from all databases on a server matching a given name?

Question

I have a setup similar to this:

• SQL-2005-SRVR
  • Security
    • MYAPP_LOGIN
  • Database1
    • Security
      • MYAPP_USER
      • OTHERUSER
  • Database2
    • Security
      • OTHERUSER
  • Database3
    • Security
      • MYAPP_USER
      • TESTUSER
  • Database4
    • Security
      • MYAPP_USER
  • Database5
    • Security
      • OTHERUSER
      • TESTUSER

I am trying to figure out how to write a SQL script that will remove all users matching MYAPP_USER from all databases on the server. I am not familiar with how to iterate over multiple databases from within a single script, or even how to query if the user exists within that database. Additionally, I am unsure what context/catalog this script would be run from - master, msdb, model?

In the above example, Database1, Database3, and Database4 would all have their users named MYAPP_USER dropped.

I also need to remove the login from the server itself, but I know that's as easy as DROP LOGIN MYAPP_LOGIN.

The script will be run as a server administrator, so permissions are not an issue.

How would I go about removing all of the users in each database that match the name MYAPP_USER?

Answer 1

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; 
begin try
  drop user [<put a name in here>]
  print ''did drop at ?''
end try
begin catch
  print ''did not drop at ?''
end catch
'
GO

Way easier. And you can tell where it worked.

Answer 2

You could use the undocumented procedure sp_MSforeachdb, check for the existence of the database user by joining master.sys.server_principals to sys.database_principals (and filtering on the sql server login name), then drop the user if found. The user linked to MYAPP_LOGIN will get dropped, even if it is not specifically named MYAPP_USER

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; 
    DECLARE @Tsql NVARCHAR(MAX)
    SET @Tsql = ''''

    SELECT @Tsql = ''DROP USER '' + d.name
    FROM sys.database_principals d
    JOIN master.sys.server_principals s
        ON s.sid = d.sid
    WHERE s.name = ''MYAPP_LOGIN''

    EXEC (@Tsql)
'
GO

Note If the SQL Server login is created from Windows, replace MYAPP_LOGIN with YourDomain\\ADUser (yes, it's a double back-slash).