DBA - How To Drop Login and User Name From All Databases In SQL Server

The below code can be used to drop Login from SQL Server and  user name associated with this Login in different databases.


USE MASTER
GO
DECLARE @LoginName VARCHAR(100)
SET @LoginName='Domain\LoginName' --> Provide the LoginName Windows or SQL Server
DECLARE @DBName VARCHAR(1000)
IF EXISTS (SELECT *
           FROM   syslogins
           WHERE  loginname = @LoginName)
  BEGIN
      PRINT @LoginName + ' login exists'

      DECLARE CUR CURSOR FOR
        --Get the list of databases
        SELECT name
        FROM   sys.databases
        WHERE  state = 0
        AND is_read_only=0
        

      --OPEN CURSOR
      OPEN CUR

      --Fetch First Row
      FETCH NEXT FROM CUR INTO @DBName

      --Loop
      WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE @SQL NVARCHAR(MAX);
            DECLARE @BIGSQL NVARCHAR(MAX);
            DECLARE @DropUSerSQL NVARCHAR(500)
            DECLARE @DropLoginSQL NVARCHAR(500)
            DECLARE @USER NVARCHAR(100)

            IF OBJECT_ID('tempdb..##T') IS NOT NULL
              DROP TABLE ##T

            --> Get User Name for Login
            SET @SQL='
     (SELECT name into ##T FROM sys.database_principals where suser_sname(sid)='''''
                     + @LoginName + '''''' + ')'
            SET @BigSQL = 'USE [' + @dbName
                          + ']; EXEC sp_executesql N''' + @sql + '''';

            EXEC(@BIGSQL)

            SET @USER=(SELECT Name
                       FROM   ##T)

            --> If User is owner of Database, Change the owner to sa
            IF ( @USER = 'dbo' )
              BEGIN
                  PRINT 'Changed DATABASE owner FROM ' + @LoginName
                        + ' TO sa'

                  EXECUTE('USE [' + @dbName + '];EXEC sp_changedbowner ''sa''')
              END
            --If not owner of DB , proceed to Drop User
            ELSE
              BEGIN
                  PRINT 'Droping  USER ' + @USER + ' IN Database:'
                        + @DBName

                  SET @DropUSerSQL= 'DROP USER [' + @USER + ']'

                  EXECUTE('USE [' + @dbName + ']; EXEC sp_executesql N''' + @DropUSerSQL + '''')
              END

            --Fetch next DataBase name 
            FETCH NEXT FROM CUR INTO @DBName
        END

      --Close and Deallocate Cursor
      CLOSE CUR

      DEALLOCATE CUR

      PRINT 'Dropping Login ' + @LoginName

      SET @DropLoginSQL= 'DROP Login [' + @LoginName + ']'

      EXECUTE(@DropLoginSQL)
  END
ELSE
  BEGIN
      PRINT 'Login Does NOT EXISTS'
  END 

4 comments:

  1. There is then going to be no reason for them to prove that what they are banning is actually hateful as they can just say that it is hateful and ban it before most people get the chance to take a deeper look or remove it with only a few people ever finding out about it.

    James Martin

    ReplyDelete
  2. i never know the use of adobe shadow until i saw this post. thank you for this! this is very helpful. https://sites.google.com/site/hotmailloginonline/

    ReplyDelete
  3. If more people that write articles involved themselves with writing great content like you, more readers would be interested in their writings. I have learned too many things from your article. entrar no hotmail

    ReplyDelete
  4. Your blog is too much amazing. I have found with ease what I was looking. Moreover, the content quality is awesome. Thanks for the nudge! traffic secrets

    ReplyDelete