Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006
    Posts
    17

    Unanswered: sp_dropuser from all databases

    I want to create Stored Proc that when given an account name will run
    exec master.dbo.sp_droplogin 'name'
    then run a cursor for every name in sysdatbases and run
    exec {variableDB}.dbo.sp_dropuser 'name'

    Please help me out.

  2. #2
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46

    Cool

    How about this :


    HTML Code:
    create procedure [dbo].[sp_DropLoginFromAllDatabases](@LoginName sysname)
    as
    begin
      set nocount on
    
      declare @DBName   sysname
      declare @SQL      nvarchar(4000)
      declare @ErrorMsg nvarchar(4000)
      declare @Enter    nvarchar(2)
    
      set @Enter = char(13) + char(10)
     
      set @LoginName = lower(rtrim(isnull(@LoginName, '')))
    
      if @LoginName = ''
      begin
        set @ErrorMsg = '@LoginName cannot be null or empty'
        goto Error
      end
    
      if @LoginName = 'dbo'
      begin
        set @ErrorMsg = 'User [dbo] cannot be deleted'
        goto Error
      end
    
      if @LoginName = 'guest'
      begin
        set @ErrorMsg = 'User [guest] cannot be deleted'
        goto Error
      end
    
      declare DatabasesCursor cursor fast_forward
      for select name
          from master.dbo.sysdatabases
          where (status & 32) = 0 and		--Loading
                (status & 512) = 0 and		--offline
                (status & 1024) = 0 and		--read only
                (status & 32768) = 0 and		--emergency mode
                (name <> 'master')
          order by name
    
      open DatabasesCursor
    
      fetch next from DatabasesCursor
      into @DBName
    
      while (@@fetch_status = 0)
      begin
        set @SQL = 'if exists(select 1' + @Enter + 
                   '          from ' + @DBName + '.dbo.sysusers' + @Enter + 
                   '          where islogin = 1 and' + @Enter + 
                   '                lower(name) = N''' + @LoginName + ''')' + @Enter + 
                   'begin' + @Enter + 
                   '  print(''Deleting user ['+@LoginName+'] from [' + @DBName + '] database...'')' + @Enter + 
                   '  exec ' + @DBName + '.dbo.sp_dropuser N''' + @LoginName + '''' + @Enter + 
                   'end'
        exec(@SQL)
    
     
        fetch next from DatabasesCursor
        into @DBName
      end
    
      close DatabasesCursor
      deallocate DatabasesCursor
    
      if exists(select *
                from master.dbo.syslogins
                where name = @LoginName)
      begin
        print('Deleting system login ['+@LoginName+']')
        set @SQL = 'drop login ['+@LoginName+']'
        exec(@SQL)
      end
    
    OK:
      return 0
    Error:
      set @ErrorMsg = 'Error in stored procedure sp_DropLoginFromAllDatabases:' + @Enter +
                      isnull(@ErrorMsg, '')
      raiserror(@ErrorMsg, 16, 10)
      return -1
    end


    Regards
    Kris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •