Understanding the INFORMATION_SCHEMA.USER View in SQL Server

The INFORMATION_SCHEMA.USER view is a name that crops up in a few forum threads, but if you type it into SQL Server Management Studio you’ll quickly discover it doesn’t exist. The confusion usually stems from mixing up the ANSI‑standard INFORMATION_SCHEMA collection with SQL Server’s proprietary catalog views. In this article we’ll unpack what INFORMATION_SCHEMA really offers, why there isn’t a USER view, and the best ways to retrieve user and role information in a SQL Server database.

What Is INFORMATION_SCHEMA?

INFORMATION_SCHEMA is a set of ANSI‑defined read‑only views that expose metadata about database objects in a portable way. Things like TABLES, COLUMNS, CONSTRAINT_COLUMN_USAGE, and SCHEMATA live under the INFORMATION_SCHEMA schema, allowing developers to write queries that work across many relational platforms without worrying about vendor‑specific catalog tables.

SQL Server implements most of the standard views, but it also adds a handful of extensions that are not part of the ANSI spec. These extensions live outside INFORMATION_SCHEMA—for example, the sys schema contains a rich set of catalog views that expose everything from index statistics to security principals.

Why There Isn’t a USER View

In the ANSI standard there is no concept of a “user” object at the database level; security is left to the implementation. Consequently, the SQL Server team never added an INFORMATION_SCHEMA.USER view. If you search the official documentation you’ll see that the closest standard views are INFORMATION_SCHEMA.SCHEMATA and INFORMATION_SCHEMA.TABLE_PRIVILEGES, which describe schemas and the permissions granted on them, but not the principals themselves.

Because user and role information is highly engine‑specific, Microsoft chose to expose it through the sys.database_principals and sys.server_principals catalog views instead. These provide far more detail—type of principal, authentication method, default schema, and so on—than a generic INFORMATION_SCHEMA view could reasonably offer.

How to List Database Users the Standard Way

If your goal is to see who can connect to a particular database, query the sys.database_principals view. A simple, readable query looks like this:

• SELECT name, type_desc, authentication_type_desc, default_schema_name FROM sys.database_principals WHERE principal_id > 0 AND is_fixed_role = 0 ORDER BY name;

This returns the user name, whether it’s a SQL login, Windows group, or certificate, plus the default schema each user belongs to. For server‑level logins, switch to sys.server_principals:

• SELECT name, type_desc, authentication_type_desc FROM sys.server_principals WHERE principal_id > 0 ORDER BY name;

Both queries exclude built‑in principals (such as dbo) unless you explicitly request them. Adding a JOIN to sys.database_role_members lets you see which users belong to which roles, which is handy for auditing permissions.

When to Use sys.* Views vs. INFORMATION_SCHEMA

Use INFORMATION_SCHEMA when you need a portable way to discover tables, columns, or constraints across different RDBMS platforms. It’s perfect for scripts that must run on SQL Server, PostgreSQL, MySQL, or Oracle without modification.

Reach for the sys catalog when you need details that are specific to SQL Server—security principals, index fragmentation, execution plans, and internal object IDs. The sys views also tend to be more up‑to‑date with the latest engine features, whereas INFORMATION_SCHEMA is intentionally static to preserve compatibility.

Common Pitfalls When Querying User Information

One frequent mistake is assuming that every login listed in sys.server_principals also appears in sys.database_principals. A server login can exist without any associated user in a particular database, which means the login can’t actually access that database until a user is created or mapped.

Another trap is overlooking role memberships. A user may have no explicit permissions but inherit them through a role like db_datareader. Always check sys.database_role_members in conjunction with the principals view to get the full picture.

Finally, remember that the INFORMATION_SCHEMA set does not reflect temporary objects or objects hidden by row‑level security. If you need a comprehensive inventory, combine INFORMATION_SCHEMA with the appropriate sys views.

Quick Reference Cheat Sheet

• List database users: SELECT name, type_desc FROM sys.database_principals;
• List server logins: SELECT name, type_desc FROM sys.server_principals;
• Find role memberships: SELECT rp.name AS Role, mp.name AS Member FROM sys.database_role_members drm JOIN sys.database_principals rp ON drm.role_principal_id = rp.principal_id JOIN sys.database_principals mp ON drm.member_principal_id = mp.principal_id;
• Portable table metadata: Use SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YourTable';

FAQ

Is there ever a legitimate INFORMATION_SCHEMA.USER view in SQL Server?

No. SQL Server’s implementation of INFORMATION_SCHEMA follows the ANSI spec, which does not define a USER view. Security principals are exposed through the sys schema instead.

Can I create my own INFORMATION_SCHEMA.USER view?

Technically you could create a user‑defined view named INFORMATION_SCHEMA.USER, but it would be a non‑standard object and could cause confusion or compatibility issues with tools that expect only the built‑in INFORMATION_SCHEMA views.

Which view should I use to audit permissions on a specific table?

Start with INFORMATION_SCHEMA.TABLE_PRIVILEGES for a portable list of grants, then join to sys.database_principals to translate principal IDs into readable names and to see role‑based permissions.

Do temporary tables appear in INFORMATION_SCHEMA?

No. Temporary tables live in the tempdb database and are not listed in the standard INFORMATION_SCHEMA views of the current database. Use tempdb.sys.objects if you need to inspect them.