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 sysremotelogins returns a row for every remote user with access to call remote stored procedures. The users returned are for the entire SQL Server instance.
The catalog view that replaces sysremotelogins is sys.remote_logins. This view also returns one row for each remote user with permission to execute remote stored procedures.
Query Via sysremotelogins
There isn’t much to sysremotelogins. There are columns to identify the row in the view, the name of the user, the change date, and the login associated with the remote login. The only potentially complex column is the status column, but in this compatibility view, the column always returns a 0. Querying the view results in a query similar to the one provided in Listing 1.
--Listing 1 – Query for sys.sysremotelogins SELECT remoteserverid ,remoteusername ,status ,sid ,changedate FROM sysremotelogins
Query via sys.remote_logins
Moving on to sys.remote_logins, querying the catalog view doesn’t get much more difficult than with sysremotelogins. The chief difference between the two is the availability of local_principal_id in the catalog view that replaces the sid in the compatibility view. The resulting query for the catalog view is provided in Listing 2.
--Listing 2 – Query for sys.remote_logins SELECT rl.server_id AS remoteserverid , rl.remote_name AS remoteusername , 0 AS status , sp.sid , rl.modify_date AS changedate , rl.local_principal_id FROM sys.remote_logins rl LEFT JOIN sys.server_principals sp ON rl.local_principal_id = sp.principal_id
Summary
In this post, we mapped the functionality of the compatibility view sysremotelogins with the catalog view sys.remote_logins. Between the two views, the primary difference is the change in column names. With the secondary difference being related to the sid column; which now requires a join to retrieve. After reading all of this, do you see any reason to continue using sysremotelogins? Is there anything missing from this post that people continuing to use the compatibility view should know?
