Drop user - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Drop user

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2010-11-24 : 09:07:52

Hi,

I want to create a sp that allows the user to drop users from the database.

declare @username varchar(max)

select @username= USER_NAME from USERS
WHERE USER_NAME = 'username'

select @userid = USER_ID from USERS
WHERE USER_NAME = 'ALDIWANF'

if @username is not null
USE Db1
DROP USER @username
ELSE print 'user not available'

Its not liking the syntax, any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-24 : 09:33:12

you need to use dynamic sql like

EXEC('DROP USER ' + @username)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2010-11-24 : 09:34:27

Thanks for that,

I get the following error:

The database principal owns a schema in the database, and cannot be dropped.

I tried to use ALTER Authorization script but that doesnt work, how would i be able to alter users roles?

Thanks

quote:
Originally posted by visakh16

you need to use dynamic sql like

EXEC('DROP USER' + @username)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources