http://www.sqlservercentral.com/blogs/stratesql/2012/11/29/lost-in-translation-deprecated-system-tables-sysremotelogins/

Printed 2014/12/22 02:19AM

Lost in Translation – Deprecated System Tables – sysremotelogins

By StrateSQL, 2012/11/29

4957867646_2f2478fd69_m5 This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objectsYou 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?


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.