x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Remove a User From All Databases on a server

I'm sure Nigel or Tara alread have blogged this, but it was asked for and I scratched one up.Anyone have any horror stories with something like this?

EDIT: If you look in that thread, you'll see Pat Phelan's use of sp_MSForEachDb.  Very Clever.

USE Northwind
GO

CREATE PROC isp_dropuser_ALL @user sysname
AS
SET NOCOUNT ON
DECLARE @MAX_name sysname, @name sysname, @sql nvarchar(4000), @check int

SELECT @sql = 'SELECT @Check=1 FROM master..syslogins WHERE [name] = '''+@user+'''', @check = NULL
 EXECUTE sp_executesql @sql,
N'@Check int OUT', @Check OUT
 IF @Check IS NOT NULL
   BEGIN
  SELECT @MAX_name = MAX([name]), @name = MIN([name]) FROM master..sysdatabases
  
  WHILE @name <= @MAX_name
    BEGIN
   PRINT 'Interogatting Database ' + @name
   SELECT @sql = 'SELECT @Check=1 FROM ' + @name + '..sysusers WHERE [name] =
'''+@user+'''', @check = NULL
   EXECUTE sp_executesql @sql,
N'@Check int OUT', @Check OUT
   IF @Check IS NOT NULL
     BEGIN 
    SELECT @sql = 'EXEC
'+@name+'..sp_dropuser ''' +@user+''''
    EXEC(@sql)
     END
   SELECT @name = MIN([name]) FROM master..sysdatabases WHERE [name] > @name
    END
  PRINT 'Removing Login ' + @user + ' From Server ' + @@SERVERNAME
  SELECT @sql = 'EXEC master..sp_droplogin '''
+@user+''''
  EXEC(@sql)
 END
   ELSE
  PRINT 'User ' + @User + ' does not have a Login to this Server'
SET NOCOUNT OFF
GO

EXEC sp_addlogin 'myUser99', 'myPassword99', 'Northwind'
EXEC sp_adduser 'myUser99'
select [name] from master..syslogins WHERE [name] Like 'my%'
select [name] from sysusers WHERE [name] Like 'my%'
GO

EXEC isp_dropUser_ALL 'myUser99'
select [name] from master..syslogins WHERE [name] Like 'my%'
select [name] from sysusers WHERE [name] Like 'my%'
GO

EXEC isp_dropUser_ALL 'xxx'
GO

DROP PROC isp_dropuser_ALL
GO

 

Print | posted on Monday, May 23, 2005 2:57 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Remove a User From All Databases on a server

Hello

I want to create missed user roles after migration from SQL 6.5 to SQL 2000
9/26/2005 5:53 AM | balamurugan
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET