October 22, 2009 at 3:24 am
i have 6000 logins in one of the server.in this i need only 30 logins now.how can i delete it in one stretch without disturbing the needed 30 logins.please helpme
October 22, 2009 at 5:06 am
shanila_minnu (10/22/2009)
i have 6000 logins in one of the server.in this i need only 30 logins now.how can i delete it in one stretch without disturbing the needed 30 logins.please helpme
Can you give more details, are these logins or/and users, are they in multiple databases, are there groups as well. do they have corresponding schemas. do any of these logins own any objects.
There are a lot of things that you need to consider.
Can you uniquely identify the logins that you want to remove.
the easiest way to remove the logins is to create a script which removes all the logins except the 30 logins that you want to keep. that isnt that hard but you need to make sure that the logins/users/schemas that you want to remove, will have no negative affect.
the other cheap way of doing it, is to open the explorer details view in ssms, navigate to logins, then you can multi-select the logins you want and right-click to script as delete to a new query window
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 22, 2009 at 5:29 am
use the below query to get all desired logins
select 'DROP LOGIN [ '+name+'] GO ' from master..syslogins
where name not in ('','',,,)
in where caluse specify user which you dont want.
when u execute u will script for dropping the users copy the script and run it
IMP try to test on test server then on production server
regards
ramu
October 22, 2009 at 6:04 am
Silverfox (10/22/2009)
shanila_minnu (10/22/2009)
i have 6000 logins in one of the server.in this i need only 30 logins now.how can i delete it in one stretch without disturbing the needed 30 logins.please helpmeCan you give more details, are these logins or/and users, are they in multiple databases, are there groups as well. do they have corresponding schemas. do any of these logins own any objects.
There are a lot of things that you need to consider.
Can you uniquely identify the logins that you want to remove.
the easiest way to remove the logins is to create a script which removes all the logins except the 30 logins that you want to keep. that isnt that hard but you need to make sure that the logins/users/schemas that you want to remove, will have no negative affect.
the other cheap way of doing it, is to open the explorer details view in ssms, navigate to logins, then you can multi-select the logins you want and right-click to script as delete to a new query window
October 22, 2009 at 6:08 am
fro where can i find the explorer view in ssms...pls can u help me for that...i just want to delete all the related details of that 6000 logins...
October 22, 2009 at 6:12 am
can you give me the script to delete thel ogins
October 22, 2009 at 6:37 am
select 'DROP LOGIN [ '+name+'] GO ' from master..syslogins
where name not in ('domain\raj','raju')
this Tsql will give drop login script for all user except which you will filter out.
copy it and run the script in new window. i hope i am clear
regards
ramu
October 22, 2009 at 7:06 am
shanila_minnu (10/22/2009)
fro where can i find the explorer view in ssms...pls can u help me for that...i just want to delete all the related details of that 6000 logins...
Press F7 and that will being up the object explorer details
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 22, 2009 at 7:11 am
ramuvanparti (10/22/2009)
select 'DROP LOGIN [ '+name+'] GO ' from master..sysloginswhere name not in ('domain\raj','raju')
this Tsql will give drop login script for all user except which you will filter out.
copy it and run the script in new window. i hope i am clear
regards
ramu
how does that drop the associated user or schema ?, taking into account you would be creating orphaned users, if the drop login, didnt remove the matching user(if there is one). and if you had schema owned objects ?.
without knowing more about the logins, running a script to drop over 5000 logins without checking anything seems....
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply