How To See Server Level Logins From SQL

You can use the next query to see the server level logins, the other way is from SSMS and drill down on security, logins and the login you want. This query return all logins;

 
SELECT 
   SERVERSS.NAME                                                AS LOGINNAME,
   SERVERSS.TYPE_DESC                                           AS LOGINTYPE, 
   SERVERSS.DEFAULT_DATABASE_NAME                               AS DEFAULTDBNAME,
   CASE LOGINS.SYSADMIN       WHEN 1 THEN 'YES' ELSE 'NO' END   AS SYSADMIN,
   CASE LOGINS.SECURITYADMIN  WHEN 1 THEN 'YES' ELSE 'NO' END   AS SECURITYADMIN,
   CASE LOGINS.SERVERADMIN    WHEN 1 THEN 'YES' ELSE 'NO' END   AS SERVERADMIN, 
   CASE LOGINS.SETUPADMIN     WHEN 1 THEN 'YES' ELSE 'NO' END   AS SETUPADMIN, 
   CASE LOGINS.PROCESSADMIN   WHEN 1 THEN 'YES' ELSE 'NO' END   AS PROCESSADMIN, 
   CASE LOGINS.DISKADMIN      WHEN 1 THEN 'YES' ELSE 'NO' END   AS DISKADMIN, 
   CASE LOGINS.DBCREATOR      WHEN 1 THEN 'YES' ELSE 'NO' END   AS DBCREATOR,
   CASE LOGINS.BULKADMIN      WHEN 1 THEN 'YES' ELSE 'NO' END   AS BULKADMIN
FROM SYS.SERVER_PRINCIPALS SERVERSS  JOIN MASTER..SYSLOGINS LOGINS ON SERVERSS.SID=LOGINS.SID 
WHERE SERVERSS.TYPE  <> 'R' AND SERVERSS.NAME NOT LIKE '##%'

Where filter exclude TYPE = R

 
S = SQL login
U = Windows login
G = Windows group
R = Server role
C = Login mapped to a certificate
K = Login mapped to an asymmetric key

Where filter exclude too servers with prefix and postfix ##. Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when SQL Server is installed, and should not be deleted.

 
##MS_SQLResourceSigningCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLAuthenticatorCertificate##
##MS_AgentSigningCertificate##
##MS_PolicyEventProcessingLogin##
##MS_PolicySigningCertificate##
##MS_PolicyTsqlExecutionLogin##

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s