Home / server / Questions / 40908
In Process
Frode Lillerud
Asked: 2009-07-15 23:36:07 +0800 CST

Deleting Security->Logins on SQL 2005 that are not mapped to a database user?

  • 772

In my demoenvironment I juggle lots of SQL databases. Each database has lots of Users, which are linked to separate Security->Logins on the server.

Over time I keep ending up with many (like hundreds) of orphans Security->Logins that are not linked to any users in any of the databases.

Is there any SQL statements I can run to drop the unused Logins - i.e. the logins that have no mappings to database users?

sql-server

2 Answers

  1. i would suggest building a select statement that ties the individual database sysusers table to the master..sysxlogins.

    some of the system procs you want to explore are sp_MShasdbaccess and sp_validatelogins.

    • 1

  2. Use sp_droplogin (or Management Studio)

    Is there a subtext to your question i.e. how to you drop only logins that aren't linked to a database user? If so I think (though I'd try it to be sure) that sp_droplogin will only delete logins that are not mapped to a db user and will fail if they are mapped.

    JR

    • 0