SQL Server Performance Forum – Threads Archive

drop user in several databases – usp?

I want to drop users in several databases (but not all!). In pseudo code it’s like: use database1 go if exists (select name from sys.database_principals where upper(name) = ‘name1todrop’)
drop user name1todrop go if exists (select name from sys.database_principals where upper(name) = ‘name2todrop’)
drop user name2todrop go use database 2 go if exists (select name from sys.database_principals where upper(name) = ‘name1todrop’)
drop user name1todrop go . . This is working fine. But now I want to make a stored procedure out of it. I can’t use the ‘use database’ (a USE database statement is not allowed in a procedure, function or trigger.) The drop user only works for the current database. But how to make it current in a stored procedure? The databasename is not allowed in the syntax…. Any help or suggestions are welcome!

Try using dynamic dynamic SQL, by doing the following: 1. Passing the database name into a variable(by selecting d/b name from sys.databases)
2. Concatenate the variable to a string (something like "use "[email protected]
3. Use a cursor if you want to repeat it for more than one d/b Hope this helps Satya

Referhttp://www.sommarskog.se/dyn-search.html FYI. Regards Hemantgiri S. Goswami
MS SQL Server MVP
————————-
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami http://hemantgirisgoswami.blogspot.com

Thank you very much for the information!
That will keep me busy for a while…..

You can try the following..
exec sp_msforeachdb "if exists (select name from ?.sys.database_principals where upper(name) = ‘name2todrop’) exec sp_dropuser ‘name2todrop’"
Note: sp_dropuser is deprecated feature, it will not work in future versions of sql server. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>