May 20, 2010 at 6:05 pm
I need to drop login as well as drop the same user from all the databases.
(Login name and user name are same)
I tried to drop the login but the user is still exsisting in all the databases.
May 20, 2010 at 6:09 pm
Correct, dropping a login doesn't remove the user from the database. You need to go through each database and find all the orphaned users and drop them at the database level.
I consider this one of the things that Microsoft need to fix. If you drop the login via Managment Studio, it will warn you that that the users aren't being deleted.
Leo
Striving to provide a better service
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 20, 2010 at 6:15 pm
yes.
can you provide any script for drop the user from all the databases?
i have 400+ databases.The login have read access to all the databases.
May 20, 2010 at 7:24 pm
You can just genereate the commands for each database by running this in master. Then run the output in master. Just replace UserName with the name in the database. If you have been considtant this will work.
If you haven't you will need to put together (or serach the net) some code to ID orphaned users, then addapt the code to generate the script.
select 'EXEC ' + Name + '..sp_dropuser ' + char(39) + 'UserName' + char(39) from sysdatabases
Leo
Striving to provide a better service.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
April 4, 2011 at 8:16 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply