SQL Query for Logins

59

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

Thank you. More than one of you had the answer I was looking for:

SELECT * FROM syslogins 

This question is tagged with sql sql-server

~ Asked on 2008-08-31 23:57:08

9 Answers


61

Is this what you're after?

select * from master.syslogins

~ Answered on 2008-09-01 00:07:08


35

On SQL Azure as of 2012;

logins:

SELECT * from master.sys.sql_logins

users:

SELECT * from master.sys.sysusers

~ Answered on 2012-11-18 02:08:04


18

EXEC sp_helplogins

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

EXEC sp_helplogins @LoginNamePattern='fred'

~ Answered on 2008-09-01 00:00:55


5

Starting with SQL 2008, you should use sys.server_principals instead of sys.syslogins, which has been deprecated.

~ Answered on 2017-03-02 22:22:19


4

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

~ Answered on 2008-09-01 00:05:03


4

@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

~ Answered on 2008-09-01 00:03:48


3

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

~ Answered on 2009-07-29 15:33:35


2

Select * From Master..SysUsers Where IsSqlUser = 1

~ Answered on 2008-09-01 00:03:29


1

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.

~ Answered on 2008-09-01 00:04:00


Most Viewed Questions: