This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects.  You can find a list of all of the deprecated views in the introduction post.

The compatibility view sysoledbusers returns a row for every login that is mapped to a linked server.  If there is no access control defined for a linked server, no records will be shown for the linked server through this compatibility view.

The catalog view sys.linked_logins is available to replace the compatibility view.  Similarly, the output for sys.linked_logins returns one row for every login mapped to a linked server.

Query Via sysoledbusers

As with a number of compatibility views, querying sysoledbusers is fairly simple.  There are no status, or bitwise, columns and nothing that needs to be translated for use.  The only item worthy of noting is the rmtpassword column; which always will return a NULL value.  The query in Listing 1 provides an example of a typical query against sysoledbusers.

--Listing 1 – Query for sys.sysoledbusers
SELECT rmtsrvid
,rmtloginame
,rmtpassword
,loginsid
,status
,changedate
FROM sysoledbusers

Query via sys.linked_logins

Using sys.linked_logins as a replacement to sysoledbusers is relatively simple.  For the most part, the differences between the two views is limited to changes in the names of some of the columns.  There isn’t a “defaults to NULL” password column, since that information was removed from the compatibility view.  The only column that needs any additional logic is the loginsid which needs to be retrieved from sys.server_principals.  In the new catalog views, sid is no longer the primary key value for logins, this has been replaced with principal_id.  The resulting querying to retrieve information from sys.linked_logins in a fashion similar to sysoledbusers is provided in Listing 2.

--Listing 2 – Query for sys.linked_logins
SELECT
r.server_id AS rmtsrvid
,remote_name AS rmtloginame
,null AS rmtpassword
,p.sid AS loginsid
,r.uses_self_credential AS status
,r.modify_date AS changedate
,r.local_principal_id
FROM sys.linked_logins r
LEFT JOIN sys.server_principals p ON r.local_principal_id = p.principal_id

Summary

In this post, we compared the compatibility view sysoledbusers with the catalog view sys.linked_logins.  The differences between the two are slight with the primary difference in the name of some of the columns.  After reading all of this, do you see any reason to continue using sysoledbusers?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Related posts:

1. Lost in Translation – Deprecated System Tables – sysmembers
2. Lost in Translation – Deprecated System Tables – sysindexkeys
3. Lost in Translation – Deprecated System Tables – sysmessages