delete users/logins using stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

delete users/logins using stored proc

Author

Topic

Wrangler
Starting Member

35 Posts

Posted - 2011-04-21 : 14:48:11

I need to clean up 2 databases within one Stored Procedure if possible. The problem is changing from one database to another doesn't seem to work inside the SP. The database never changes from DB_A. Is this possible in a SP? If so, how? Thanks

USE DB_A
GO

ALTER PROCEDURE dbo.Delete_Login_User
@userID as VARCHAR(10)
AS
BEGIN

USE DB_B
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = @userID)
DROP USER @userID

USE DB_C
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = @userID)
DROP USER @userID

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = @userID)
DROP LOGIN @userID

END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-21 : 16:21:41

You can use sp_executesql so that the DROP statements run under the right context.

Try this:


ALTER PROCEDURE dbo.Delete_Login_User 
@userID as VARCHAR(10)
AS
BEGIN

DECLARE @sql nvarchar(100)

SET @sql = 'DROP USER ' + @userID

IF EXISTS (SELECT * FROM DB_B.sys.database_principals WHERE name = @userID)
	EXEC DB_B..sp_executesql @sql

IF EXISTS (SELECT * FROM DB_C.sys.database_principals WHERE name = @userID)
	EXEC DB_C..sp_executesql @sql

SET @sql = 'DROP LOGIN ' + @userID

IF EXISTS (SELECT * FROM master.sys.server_principals WHERE name = @userID)
	EXEC master..sp_executesql @sql

END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Wrangler
Starting Member

35 Posts

Posted - 2011-04-21 : 16:38:27

Tara, that worked perfect. Thank you!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-21 : 17:44:23

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources