Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

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).
0
w3brang3r
Asked:
w3brang3r
  • 2
1 Solution
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now