w3brang3r
asked on
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).
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).
ASKER
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....
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....
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!!!)
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!!!)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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).