Once upon a time there was a developer who was concerned about the security of his application’s data. So he decided to change the password for his application’s SQL login. So he logged into the database and used sp_password to change his password and he was happy.
On the other end of the hall lived another developer. This developer was not happy. His application had suddenly stopped working and he was getting angry phone calls from users. “But I didn’t change anything!” he cried. And that was true. But his application was using a linked server that mapped to the login whose password was changed by the first developer (who lived in the house that Jack built).
The second developer was very angry with the first developer.
“You can’t just go around changing passwords willy-nilly!”
The first developer got defensive.
“It’s my application and my login, how was I supposed to know you were mapped to it?”
Suddenly they stopped, and the hall got very quiet. They both turned and looked at the DBA.
“Surely there must be some way of knowing what linked servers are mapped to what logins on what servers,” they implored.
The DBA replied, equivocally, “Let me see what I can do.”
Now this DBA was a very smart DBA (and beautiful. like a princess, really). She already had a program she had written that inventoried each SQL Server instance to track things like jobs, database growth, backup locations, etc. She had even written MSRS reports against this data that listed applications on each instance, job statuses, license counts and more. She knew it wouldn’t be difficult to gather this bit of data, too. Once she knew where it was stored, that is.
So she started looking in the system views in the master database. That was where sys.servers and sys.database_principals were located, after all. It would make sense to store the linked server login mapping there, too, right? Only, it wasn’t there! Hmm. Not to be discouraged, our (smart, beautiful, princess-like) heroine looked in the msdb database. And voila! There, in the system views: sys.linked_logins.
And, after a bit of muttering about why the hell did they put it in msdb, she wrote the following code and included it in her program.
SELECT @@servername as [SQL Instance] , s.name as [Linked Server] , s.data_source as [Remote Server] , CASE l.uses_self_credential WHEN 1 THEN 'PASS-THRU' ELSE ISNULL(p.name, 'UNMAPPED LOGINS') END AS [Local Login] , CASE l.uses_self_credential WHEN 1 THEN 'PASS-THRU' ELSE l.remote_name END AS [Remote Login] FROM sys.linked_logins l JOIN sys.servers s ON s.server_id = l.server_id AND is_data_access_enabled = 1 LEFT OUTER JOIN sys.server_principals p ON p.principal_id = l.local_principal_id ORDER BY [Linked Server], [Local Login]
Now developers could know what linked servers might be impacted by a password change. Peace and harmony reigned throughout the hall. Hurrah!
And they lived happily ever after.
The End.