Drop Login from all databases that has the login name

I need a generic script that drops a particular user from all the databases(which consists the user). I tried drop login username, but it does not delete from databases, it just deletes the login entry from the MAIN SECURITY folder of the SQL Instance.

If I have a login names 'LOGINABC', which has access to 15 out of 15databases in a instance.

When I execute the generic script, it should drop the login entry from main SECURITY>LOGINS under the SQL Instance and also from the 15 Databases (which will be under the Database>Security>Users).
w3brang3rAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DcpKingCommented:
Try wrapping this line in a stored procedure:
EXECUTE sp_msforeachdb 'USE ? DROP LOGIN login_name'
You might try wrapping the code within the ticks in a try/catch block, and so print a message that it succeeded or failed in dropping each login (use the question mark instead of a variable holding the name of the database).
0
w3brang3rAuthor Commented:
Need a ready-made solution please.

Note: while manual operation, the main login is dropped using DROP LOGIN LOGINABC, but coming to each an every DB, it is DROP USER LOGINABC.

Hope you got the difference....
0
DcpKingCommented:
I didn't think that this was that difficult to write!

create procedure dbo.dropusers
( @strusername varchar(32) )
as
Begin
      EXECUTE sp_msforeachdb 'USE ? DROP USER login_name'
End

BTW, handle this with care: I haven't tested it (I can't, really, in work!!!)
0
LCSandman8301Commented:
create procedure dbo.dropusers
( @strusername varchar(32) )
as
Begin
      declare @cmd nvarchar(4000)
      set @cmd = 'USE ? IF EXISTS (select 1 from sysusers where name = '''+@strusername+''') BEGIN DROP USER '+@STRUSERNAME+' END'
      EXECUTE sp_msforeachdb @cmd
      set @cmd = 'DROP LOGIN' + @strusername
      exec sp_executesql @cmd
End
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.