SQL Authentication Mode on users-CodePudding

for auditors we need some query that will how for each user which Authentication Mode is used. So, we have 100 SQL user and some of them are using SQL login and some of them are Windows login.

So is there any option to write query to show for example:

User1 - Authentication Mode - Windows User2 - Authentication Mode - SQL Login

Thank you for any suggestions.

CodePudding user response：

It seems likely that what you want to know is about the logins to the server, rather than the users. You can get this information from the sys.server_principals view, like so:

select 
    sp.name, sp.type_desc as 'authentication mode'
from sys.server_principals sp
where sp.type in ('S', 'U');

If you need to do it at the database user level, then sys.users has the sid column that you can use to join to sys.server_principals and get the type.