September 2, 2002 at 7:35 am
I'm tyring to put some T-Sql together to drop users and then delete the logins from a Db.
I issue a Exec sp_dropuser 'MyUser' which works fine.
When executing sp_droplogin 'MyLogin'
I get the following error message:-
Server: Msg 15175, Level 16, State 1, Procedure sp_droplogin, Line 88
Login 'MyLogin' is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login.
What am I missing ? Suggestions welcome.
Thanks
Andy
September 2, 2002 at 8:17 am
Examine the output and/or source of sp_helplogins 'MyLogin' for some ideas.
- Troy King
- Troy King
September 2, 2002 at 9:27 am
Good idea.
The problem is probably that your user is known in another database too (except for the one where you dropped it).
sp_helplogins should point you in the right direction.
September 3, 2002 at 3:42 am
A simple way is to utilize one of the MS system sp's to cycle thru the DB's.
sp_MSForEachDB '
USE ?
EXEC sp_dropuser ''MyUser'' '
This will go thru all the databases and remove the user from each, then you should have no issues dropping the user account when done.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
September 3, 2002 at 5:03 am
I dont think (have to dbl check!) that if you've got the user aliased some place that sp_dropuser will do it, might need sp_dropalias. Note also that sp_dropuser is deprecated, it just calls the recommended sp_revokedbaccess. And finally, Antares is right that the foreach provides a quick way to do it - as with all "quick" methods please use appropriate caution!.
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply