How to remove / delete multiple logins

Question

How to remove / delete multiple logins

koln

SSCrazy

Points: 2189
More actions
March 29, 2010 at 10:47 am

#139962

I deleted the database, but now have 100+ logins left on the SQL Server. Is there some kind of script I can run, that deletes them all ? I dont want to do one by one.
Thank you

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 1

you would want to write a cursor or any row operator that will loop through sysxlogins, generating a syntax using sp_droplogin, and then executing them either separately or in the script itself

koln SSCrazy Points: 2189 More actions · Answer 2

..thank you for the tip..I've done an exhaustive search on the Internet for this type of script, but have not found an example...I would think that this type of script is very common among the SQL community...do you have any idea where I could find an example ?

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Post removed as it did not apply to SQL 2005

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

george sibbald SSC Guru Points: 104210 More actions · Answer 4

presuming this is SQL2005 the above will not work in SQL2005.

As this is a one off exercise you can reverse engineer the SQL and run the results -- AFTER EDITING OUT IDS INSTALLED BY SQL

select 'drop login ['+name+']' from master.sys.server_principals

where type in ('S','G','U')

and name != 'sa'

--and default_database_name = 'yourdb'

uncomment the dafault db if you can use that to refine the drop.

Interestingly there seems no obvious way to eliminate the SQL2005 installed accounts and builtin/admin if it is there, even sp_help_revlogin includes these.

so as I say go through the results before running it.

---------------------------------------------------------------------

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Was there only one database on the server?

I would be careful about removing the logins. George and Ron provide some nice scripts. I would not execute the code from either of them until you have verified that the account does not belong to a different database other than the one you dusted.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

george sibbald SSC Guru Points: 104210 More actions · Answer 6

Apologies Ron, reading my post back I realise it sounds a bit brusque, which I did not intend.

I should have just said my code was SQL2005 compatible.

---------------------------------------------------------------------

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

george sibbald

No apologies necessary ... no offense taken .... you ARE correct my posted code would NOT work in SQL 2005.

Thanks for pointing out that fact.

Who knows how many others might read this forum and go off attempting to use what I had posted and then wondering why it did not work.

You did the community a good turn.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]