PROBLEM: I need a T-SQL script to remove a login from all SQL Servers in my environment.

SOLUTION: My solution includes the use of MS SQL 2008 Central Management Servers and the script listed below. This script is a work in progress because I’m constantly adding, removing and updating features as I encounter problems. Maybe you can help me enhance it with some recommendations. The most recent update handles the transfer of schemas to dbo because a database user can not be dropped if it owns a schema.

In the absence o f a SQL Server 2008 instance, you can use the SQLCMD utility to execute the included script against all your servers; either way you will need to provide a list of servers.

I recommend you build a server for the sole purpose of collecting information about all SQL servers in your environment, use this server as your “Registered Central Management Server”. Once you have your Central Management Server ready and with all your SQL Servers registered, right click on the node or group of servers you want to check for the existence of the login to be removed and select “New Query”. After a second or two you will notice a query window with a pink status bar. Copy and paste the code below into that window. On the query menu, click Specify Values for Template Parameters and change the value with the login you are looking for.

SELECT [name] 
  FROM master..syslogins
 WHERE [name] LIKE '%<login_name, sysname, login_name>%'

If the query above did not return any records, there is nothing else to do. If records were returned, you will see the name of the server(s) where the login exists and the execution of the script below will take care of removing them from your environment.

DECLARE @dbname VARCHAR(100),
        @USER VARCHAR(100),
        @login VARCHAR(100),
        @SQL NVARCHAR(2000),
        @version TINYINT,
        @CATALOGNAME nvarchar(128),
        @SCHEMANAME nvarchar(128),
        @OBJECTTYPE nvarchar(20),
        @OBJECTNAME nvarchar(128)

SET NOCOUNT ON

--[ GET SQL SERVER VERSION.  WILL USE FOR DML COMMANDS NOT COMPATIBLE IN ALL VERSIONS ]
SELECT @version = LEFT(CAST(SERVERPROPERTY('productversion') AS varchar(50)),1)

SET @USER = '<login_name, sysname, login_name>'

IF OBJECT_ID('tempdb.dbo.##users') IS NULL
   CREATE TABLE ##users ([USER] VARCHAR(100))

IF OBJECT_ID('tempdb.dbo.##Schemas') IS NULL
CREATE TABLE ##Schemas (
       ID int IDENTITY(1,1),
       CATALOGNAME nvarchar(128),
       SCHEMANAME nvarchar(128),
       OBJECTNAME nvarchar(128),
       OBJECTTYPE nvarchar(20))

--[ COLLECT NAME OF ALL DATABASES ON THE SERVER ]
DECLARE crDBname CURSOR FOR
 SELECT [name]
   FROM master.dbo.sysdatabases
  WHERE databasepropertyex([name],'Updateability') = 'READ_WRITE' AND
        databasepropertyex([name], 'status') = 'ONLINE' 
  ORDER BY [name]

--[ COLLECT NAME OF ALL SERVER LOGINS THAT MATCH PATTERN OF LOGIN TO REMOVE ]
DECLARE crLogin CURSOR FOR
 SELECT loginname
   FROM master.dbo.syslogins
  WHERE [name] LIKE '%'+ @USER

--[ REPOSITORY OF ALL DATABASE USERS. IT IS EMPTY INITIALLY ]
DECLARE crUser CURSOR FOR
 SELECT [USER]
   FROM ##users

--[ CHECK FOR THE EXISTANCE OF USER IN EVERY DATABASE ]
   OPEN crDBname
  FETCH NEXT FROM crDBname INTO @dbname
  WHILE @@fetch_status = 0
        BEGIN
        DELETE FROM ##users

        SET @SQL = N'IF EXISTS (SELECT * FROM ['+ @dbname +'].dbo.sysusers WHERE name LIKE ''%'+@USER+''')' +CHAR(13)+
                    '   BEGIN' + CHAR(13) +
                    '   INSERT INTO ##users' + CHAR(13) +
                    '   SELECT name FROM ['+ @dbname +'].dbo.sysusers WHERE name LIKE ''%'+ @USER +''''+CHAR(13) +
                    '   PRINT ''USER '+ @USER +' EXISTS IN '+ @dbname +'.''' + CHAR(13) +
                    'END'
        EXEC (@SQL)

        --[ CHECK FOR DB OBJECT OWNERSHIP BY THIS USER & TRANSFER OWNERSHIP TO DBO IF ]
        --[ EXISTS. ONLY APPLIES TO SQL 2005+                                         ]
        IF @version IN(9,0) --[ CHECK FOR SQL VERSION >= 2005                 ]
           BEGIN
           SELECT @SQL = N'USE ' + @dbname + CHAR(13) +
                          'INSERT ##Schemas (CATALOGNAME, SCHEMANAME, OBJECTNAME, OBJECTTYPE)'+CHAR(13)+
                          'SELECT [TABLE_CATALOG] [CATALOGNAME], '+CHAR(13)+
                          '       [TABLE_SCHEMA] [SCHEMANAME], '+CHAR(13)+
                          '       [TABLE_NAME] [OBJECTNAME], '+CHAR(13)+
                          '       ''VIEW'' [OBJECTTYPE] '+CHAR(13)+
                          '  FROM INFORMATION_SCHEMA.SCHEMATA A '+CHAR(13)+
                          ' INNER JOIN INFORMATION_SCHEMA.VIEWS B ON A.[SCHEMA_NAME] = B.TABLE_SCHEMA '+CHAR(13)+
                          ' WHERE SCHEMA_OWNER LIKE ''%'+ @USER +''''+CHAR(13)
           EXEC (@SQL) 

           SELECT @SQL = N'USE ' + @dbname + CHAR(13) +
                          'INSERT ##Schemas (CATALOGNAME, SCHEMANAME, OBJECTNAME, OBJECTTYPE)'+CHAR(13)+
                          'SELECT [SPECIFIC_CATALOG] [CATALOGNAME], '+CHAR(13)+
                          '       [SPECIFIC_SCHEMA] [SCHEMANAME], '+CHAR(13)+
                          '       SPECIFIC_NAME [OBJECTNAME], '+CHAR(13)+
                          '       ROUTINE_TYPE [OBJECTTYPE] '+CHAR(13)+
                          '  FROM INFORMATION_SCHEMA.SCHEMATA A '+CHAR(13)+
                          ' INNER JOIN INFORMATION_SCHEMA.ROUTINES B ON A.[SCHEMA_NAME] = B.SPECIFIC_SCHEMA '+CHAR(13)+
                          ' WHERE SCHEMA_OWNER LIKE ''%'+ @USER +''''+CHAR(13)
            EXEC (@SQL)

           SELECT @SQL = N'USE ' + @dbname + CHAR(13) +
                          'INSERT ##Schemas (CATALOGNAME, SCHEMANAME, OBJECTNAME, OBJECTTYPE)'+CHAR(13)+
                          'SELECT CATALOG_NAME [CATALOGNAME], '+CHAR(13)+
                          '       [SCHEMA_NAME] [SCHEMANAME], '+CHAR(13)+
                          '       TABLE_NAME [OBJECTNAME], '+CHAR(13)+
                          '       ''TABLE'' [OBJECTTYPE] '+CHAR(13)+
                          '  FROM INFORMATION_SCHEMA.SCHEMATA A '+CHAR(13)+
                          ' INNER JOIN INFORMATION_SCHEMA.TABLES B ON A.[SCHEMA_NAME] = B.TABLE_SCHEMA '+CHAR(13)+
                          ' WHERE SCHEMA_OWNER LIKE ''%'+ @USER +''''+CHAR(13)
           EXEC (@SQL)

           DECLARE crSchema CURSOR FOR
            SELECT [CATALOGNAME], [SCHEMANAME], [OBJECTTYPE], [OBJECTNAME]
              FROM ##Schemas
             ORDER BY ID

            OPEN crSchema
           FETCH NEXT FROM crSchema INTO @CATALOGNAME, @SCHEMANAME, @OBJECTTYPE, @OBJECTNAME
           WHILE @@FETCH_STATUS = 0
                 BEGIN /*[ TRANSFER DB OBJECTS FROM USER SCHEMA TO DBO SCHEMA ]*/
                 SET @SQL = N'USE ' + @dbname + CHAR(13) +
                             'ALTER SCHEMA [dbo] TRANSFER ['+ @SCHEMANAME +'].['+ @OBJECTNAME +']'+CHAR(13)+
                             'PRINT ''Transfering ownership of '+ @OBJECTTYPE +' '+ @OBJECTNAME +' to [dbo]'''
                 EXEC (@SQL)

                 FETCH NEXT FROM crSchema INTO @CATALOGNAME, @SCHEMANAME, @OBJECTTYPE, @OBJECTNAME
             END       /*[ TRANSFER DB OBJECTS FROM USER SCHEMA TO DBO SCHEMA ]*/
           CLOSE crSchema
           DEALLOCATE crSchema
        END
TRUNCATE TABLE ##Schemas

        --[ CREATE DDL STATEMENT TO REMOVE USER.  HERE WE USE THE @version    ]
        --[ VARIABLE TO DETERMINE WHICH COMMAND TO USE                        ]
        OPEN crUser
        FETCH NEXT FROM crUser INTO @USER
        WHILE @@fetch_status = 0
              BEGIN
              IF @version IN(9,0)
                 BEGIN
                 SET @SQL = N'USE ' + @dbname + CHAR(13) +
                             'ALTER USER ['+ @USER +'] WITH DEFAULT_SCHEMA = [dbo]'+ CHAR(13) +
                             'IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'''+ @USER +''')' + CHAR(13) +
                             '    DROP SCHEMA ['+ @USER +']' + CHAR(13) +
                             'IF EXISTS (SELECT * FROM sysusers WHERE name = '''+ @USER +''')' + CHAR(13) +
                             '   BEGIN' + CHAR(13) +
                             '   DROP USER ['+ @USER +']' + CHAR(13) +
                             'PRINT ''USER '+ @USER +' has been dropped FROM '+ @dbname +'.''' + CHAR(13) +
                             'END'
              END
              ELSE
                 BEGIN
                 SET @SQL = N'USE ' + @dbname + CHAR(13) +
                             'IF EXISTS (SELECT * FROM sysusers WHERE name = '''+@USER+''')' + CHAR(13) +
                             '   BEGIN' + CHAR(13) +
                             '   EXEC sp_revokedbaccess '''+@USER+'''' + CHAR(13) +
                             'PRINT ''USER '+@USER+' has been dropped FROM '+@dbname+'.''' + CHAR(13) +
                             'END'
              END --IF
              EXEC (@SQL)

              FETCH NEXT FROM crUser INTO @USER
          END --WHILE
        CLOSE crUser
        FETCH NEXT FROM crDBname INTO @dbname
    END

   DEALLOCATE crUser
     CLOSE crDBname
DEALLOCATE crDBname

DROP TABLE ##users
SET @SQL = ''

OPEN crLogin
FETCH NEXT FROM crLogin INTO @login
--PRINT 'Logins = ' +@login
WHILE @@fetch_status = 0
      BEGIN
      IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = @login)
         SET @SQL = N'EXEC master.dbo.sp_droplogin ['+ @login +']'
         EXEC (@SQL)

      IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = @login )
         SET @SQL = N'USE master' +CHAR(13) +
                     'EXEC master.dbo.sp_revokelogin '''+ @login +'''' + CHAR(13)
         EXEC (@SQL)
      FETCH NEXT FROM crLogin INTO @login
  END
CLOSE crLogin
DEALLOCATE crLogin