How to drop users programmatically through a sp

Hi,

I want to drop users programmatically, is this possible? I tried

create PROCEDURE [dbo].[sp_DelAllUnusedLogins] AS
BEGIN
SET NOCOUNT ON;
DECLARE @.UserName nvarchar(128)

DECLARE user_cursor CURSOR FOR
select name from sys.sysusers where status=12 and name
not in ('NT AUTHORITY\SYSTEM','dbo')

OPEN user_cursor

FETCH NEXT FROM user_cursor INTO @.UserName
WHILE @.@.FETCH_STATUS = 0
BEGIN

DROP USER @.UserName
FETCH NEXT FROM user_cursor INTO @.UserName

END

CLOSE user_cursor
DEALLOCATE user_cursor
END

But @.UserName isn't accepted..
Okay, I found
sp_dropuser
;) Which works fine

|||

Sp_dropuser is deprecated and will be removed in a future release. You can use the DROP USER statement within dynamic SQL to drop the user from a procedure. sp_dropuser does in fact do the same thing: it calls DROP USER.

Thanks
Laurentiu

|||

How do I use drop user in dynamic SQL?

I tried:
declare @.User varchar(100);
select @.User='SomeExistingUser';
drop user @.User;

This will produce the error:
Meldung 102, Ebene 15, Status 1, Zeile 3
Falsche Syntax in der N?he von '@.User'.

=>Invalid syntax near '@.User'

|||

YOu have embed the whole thing in an execution context like:

declare @.Statement varchar(100);
select @.Statement ='DROP USER + ' SomeExistingUser';
EXEC(@.Statement);

HTH, Jens Suessmeyer.

http://www.slqserver2005.de

|||

Here's the actual code that we execute within sp_dropuser:

set @.stmtU = 'drop user ' + quotename(@.name_in_db, ']')

-- drop the owner
exec (@.stmtU)

You need to use quotename to not allow SQL Injection.

Thanks
Laurentiu