SQL SERVER equivalent for Oracle DBA_USERS

Question

I'm working on conversion of Oracle queries to MSSQL queries. Following is query for Oracle -

SELECT USERNAME FROM DBA_USERS WHERE USERNAME = 'TESTUSER';

But in MSSQL, DBA_USERS is invalid object is thrown. Please suggest what is equivalent of the same in MSSQL.

Answer 1

The database management views are the newest addition to the SQL Server family as of 2005 and newer and replace a lot of tables that were previously used in older versions of SQL Server. A lot of old sys schema tables have comments which point this out as is stated on the page for sys.syslogins.

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Instead you would use the following database management views:

• sys.server_principals or sys.sql_logins (instead of sys.syslogins)
• sys.database_principals (instead of sys.sysusers)

There is a mapping page which I have linked in the comment, which helps you find the newer views for the relevant data.

All links point to Microsoft Pages in the official SQL Docs

General Catalog Views

There is a list of System Catalog Views (Transact-SQL) that are focused on various areas of SQL Server.

The sys.database_principals, sys.server_principals and sys.sql_logins for example are all part of the Security Catalog Views which can be found on the overview page.

Recommendation

If you are just starting your SQL Server journey, then I would recommend learning the new views, as the old ones are deprecated and could become unsupported.

Answer 2

The sys schema is your friend for these kind of queries. Specifically sys.sysusers will give you the list of all Users of the current database.

You can also get a list of all server Logins using sys.syslogins.

In SQL Server, a server Login is first created, and then mapped to the databases that Login has access to. Each database that a Login is mapped to has a User created within that database for that Login.

---

Per @DanGuzman's comment, the non-deprecated equivalent sys views are the following:

1. sys.database_principals (replaces sys.sysusers)
2. sys.sql_logins (replaces sys.syslogins)