Jesper M. Christensen

SharePoint and Security

SQL script to check users auth. method for databases


I was testing different authentication methods the other day and needed to check which types of authentication methods was used to my Microsoft SQL Server databases by my test-users. Because I needed to double-check that my .NET application was authenticating with Kerberos, and wanted to see this from the SQL server itself, I made this script that enumerates the connections, users and the authentication method used:
 
SELECT DB_NAME(dbid) AS DatabaseName, loginame AS LoginName, sys.dm_exec_connections.auth_scheme as AuthMethod
FROM sys.sysprocesses
JOIN sys.dm_exec_connections
ON sys.sysprocesses.spid=sys.dm_exec_connections.session_id
WHERE dbid > 0
GROUP BY dbid, loginame, spid,sys.dm_exec_connections.auth_scheme
 
The output will give you something like this:
 
DatabaseName

LoginName

AuthMethod

master

DOMAIN\administrator

NTLM

SharePoint_Config

DOMAIN\SPConfigAcct

KERBEROS

SharePoint_Config

DOMAIN\SPConfigAcct

KERBEROS

SharePoint_Config

DOMAIN\SPConfigAcct

KERBEROS

SharePoint_Config

DOMAIN\SPContentPoolAcct

KERBEROS

WSS_Search_WSS1

DOMAIN\SPSearchAcct

KERBEROS

WSS_Search_WSS1

DOMAIN\SPSearchAcct

KERBEROS

SharedServices_DB

DOMAIN\SPConfigAcct

KERBEROS

SharedServices_DB

DOMAIN\SPSearchAcct

KERBEROS

SharedServices_Search_DB      

DOMAIN\SPSearchAcct

KERBEROS

WSS_Content_intranet

DOMAIN\SPContentPoolAcct

KERBEROS

WSS_Content_intranet

DOMAIN\SPContentPoolAcct

KERBEROS

CompanyDatabase

DOMAIN\administrator                       

KERBEROS
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

%d bloggers like this: