Delete Orphan User

I have collected a script that deletes the orphan user from your DB. I want to share it with you. The script is written by a famous DBA named "Dale Kelly".

I have changed some T-SQL statement to make it more users friendly.

 

Use master

Go

Create Table #Orphans

                                     (

                                                  RowID    int not null primary key identity(1,1) ,

                                                  TDBName  varchar (100),

                                                  UserName varchar (100),

                                                  UserSid  varbinary(85)

                                     )

 SET NOCOUNT ON

 DECLARE @DBName sysname,

         @Qry    nvarchar(4000)

 SET @Qry = ''

 SET @DBName = ''

 

 WHILE @DBName IS NOT NULL

 BEGIN

   SET @DBName = (SELECT MIN(name) FROM master..sysdatabases

                  WHERE name NOT IN ('model', 'msdb','distribution')  /** to exclude named databases add them to the Not In clause **/

                        AND DATABASEPROPERTY(name, 'IsOffline') = 0

                        AND DATABASEPROPERTY(name, 'IsSuspect') = 0

                        AND name > @DBName

                 )

   IF @DBName IS NULL BREAK

        

   SET @Qry = 'SELECT ''' + @DBName + ''' AS DBName, name AS UserName,

                      sid AS UserSID

               FROM [' + @DBName + ']..sysusers

               WHERE issqluser = 1

                     AND (sid is not null AND sid <> 0x0)

                     AND suser_sname(sid) is null order by name'

                    

   INSERT INTO #Orphans EXEC (@Qry)

 

END

SELECT * FROM #Orphans WHERE TDBName

/* To drop orphans uncomment this section */

Declare @SQL          varchar (200)

Declare @DDBName      varchar (100)

Declare @Orphanname   varchar (100)

Declare @DBSysSchema  varchar (100)

Declare @FROM         int

Declare @To           int

 

SELECT @FROM = 0, @To = @@ROWCOUNT

FROM #Orphans

 

 

WHILE @FROM < @To

      BEGIN

              SET @FROM = @FROM + 1

 

              SELECT @DDBName = TDBName, @Orphanname = UserName FROM #Orphans

              WHERE  RowID = @FROM

     

              SET @DBSysSchema = '[' + @DDBName + ']' + '.[sys].[schemas]'

              PRINT @DBsysSchema

              PRINT @DDBname

              PRINT @Orphanname

              SET @SQL = 'If Exists (SELECT * FROM ' + @DBSysSchema

                          + ' WHERE name = ''' + @Orphanname + ''')

                            BEGIN

                                  Use [' + @DDBName

                                        + '] Drop Schema [' + @Orphanname + ']

                            END'

              PRINT @SQL

              EXEC (@SQL)

    

              BEGIN Try

                  SET @SQL = 'Use ' + @DDBName

                             + ' Drop User [' + @Orphanname + ']'

                  EXEC (@SQL)

              END Try

              BEGIN Catch

              END Catch

  

     END

 

 

Drop table #Orphans

 

 

 

Hope you like it.

 

Posted by: MR.JOYDEEP DAS