How do you distinguish between windows servers and logins?

Question

SQL Server supports two types of logins:

• Server login
• Windows login

How do you check if a particular login has one type or another?

I assume that checking the slash in the username is an unreliable way to do this ...

Answer 1

Take a look at the type_desc column.

select *
from sys.server_principals

https://msdn.microsoft.com/en-us/library/ms188786.aspx

Answer 2

This seems to do the job ...

SELECT name
FROM sys.sql_logins
WHERE name = ?

If you return a string, the user is the login or group of the server; otherwise, it’s Windows login.

UPDATE : see Sean's answer for a more reliable solution.

Answer 3

There is a column in the sys.syslogins directory view isntnameif its value is set to one of its Windows login, if 0 is its SQL Server login.

Select name 
     ,CASE 
           WHEN isntname = 1 THEN 'Windows Login'
           WHEN isntname = 0 THEN 'SQLServer Login'
     END  AS LoginType
 from sys.syslogins

OR

SELECT Name 
       ,type_desc LoginType
FROM sys.server_principals