How to drop login

Hi all,
when i execute the sp, EXEC master..sp_droplogin 'Martin'
it returns error:
Login 'Martin' is aliased or mapped to a user in one or more database(s).
Drop the user or alias before dropping the login.
I know that the user 'Martin' has been granted to different DB. If I have to
drop the login, the login should be revoked the DB access before issue the
sp_droplogin.
So, is there a command than can drop the login directly, i mean no matter
there are any DB associated with the login?
Just similar to the Enterprise manager, under Security -> Logins ->
highlight the login and right click "delete"
It will remove all associated database with the login and then drop the
login.
Thanks in advance!
MartinBehind the scenes, EM is dropping the user from the databases to which (s)he
had been granted access, and then it drops the login. To script it, you
would have to loop through all DB's for that user and do the drops, followed
by sp_droplogin.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
Hi all,
when i execute the sp, EXEC master..sp_droplogin 'Martin'
it returns error:
Login 'Martin' is aliased or mapped to a user in one or more database(s).
Drop the user or alias before dropping the login.
I know that the user 'Martin' has been granted to different DB. If I have to
drop the login, the login should be revoked the DB access before issue the
sp_droplogin.
So, is there a command than can drop the login directly, i mean no matter
there are any DB associated with the login?
Just similar to the Enterprise manager, under Security -> Logins ->
highlight the login and right click "delete"
It will remove all associated database with the login and then drop the
login.
Thanks in advance!
Martin|||Hi
You can run SQL Server Profiler to see what is going on behind the scenes
when you delete the LOGIN via EM
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> when i execute the sp, EXEC master..sp_droplogin 'Martin'
> it returns error:
> Login 'Martin' is aliased or mapped to a user in one or more database(s).
> Drop the user or alias before dropping the login.
> I know that the user 'Martin' has been granted to different DB. If I have
> to drop the login, the login should be revoked the DB access before issue
> the sp_droplogin.
> So, is there a command than can drop the login directly, i mean no matter
> there are any DB associated with the login?
> Just similar to the Enterprise manager, under Security -> Logins ->
> highlight the login and right click "delete"
> It will remove all associated database with the login and then drop the
> login.
> Thanks in advance!
> Martin
>
>|||oh, no shortcut...bad to know the fact... :p
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:um7DtsTyHHA.4276@.TK2MSFTNGP05.phx.gbl...
> Behind the scenes, EM is dropping the user from the databases to which
> (s)he
> had been granted access, and then it drops the login. To script it, you
> would have to loop through all DB's for that user and do the drops,
> followed
> by sp_droplogin.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> when i execute the sp, EXEC master..sp_droplogin 'Martin'
> it returns error:
> Login 'Martin' is aliased or mapped to a user in one or more database(s).
> Drop the user or alias before dropping the login.
> I know that the user 'Martin' has been granted to different DB. If I have
> to
> drop the login, the login should be revoked the DB access before issue the
> sp_droplogin.
> So, is there a command than can drop the login directly, i mean no matter
> there are any DB associated with the login?
> Just similar to the Enterprise manager, under Security -> Logins ->
> highlight the login and right click "delete"
> It will remove all associated database with the login and then drop the
> login.
> Thanks in advance!
> Martin
>
>|||woo, this absolutely new to me.....sounds great...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ufvFgxTyHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Hi
> You can run SQL Server Profiler to see what is going on behind the scenes
> when you delete the LOGIN via EM
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
>