SQL Query for Logins

Question

What is the SQL query to select all of the MSSQL Server's logins?

Thanks, more then one of you had the answer I was looking for

SELECT * FROM syslogins

Answer 1

EXEC sp_helplogins

You can also pass an "@LoginNamePattern" parameter to get information about a specific login:

EXEC sp_helplogins @LoginNamePattern='fred'

Answer 2

Select * From Master..SysUsers Where IsSqlUser = 1

Answer 3

@allain, @GateKiller your query selects users not logins
To select logins you can use this query:

SELECT name FROM master..sysxlogins WHERE sid IS NOT NULL

In MSSQL2005/2008 syslogins table is used insted of sysxlogins

Answer 4

Have a look in the syslogins or sysusers tables in the master schema. Not sure if this still still around in more recent MSSQL versions though. In MSSQL 2005 there are views called sys.syslogins and sys.sysusers.

Answer 5

Selecting from sysusers will get you information about users on the selected database, not logins on the server.

Answer 6

Is this what you're after?

select * from master..syslogins

Answer 7

-- Este Query te muestra los Roles que tiene cada Login

USE MASTER SELECT DISTINCT SYSL.LOGINNAME, SYSL.DBNAME AS DEFAULTDB, SYSL.SECURITYADMIN, SYSL.SERVERADMIN, SYSL.SETUPADMIN, SYSL.PROCESSADMIN, SYSL.DISKADMIN, SYSL.DBCREATOR, SYSL.BULKADMIN FROM SYSLOGINS AS SYSL INNER JOIN SYSXLOGINS AS SYSX ON SYSL.SID = SYSX.SID ORDER BY SYSL.LOGINNAME

-- Este Stored Procedure Te muestra la Información de cada Login

-- y las bases de datos a las que tiene acceso

Exec SP_helpLogins

Answer 8

sp_helplogins will give you the logins along with the DBs and the rights on them.