October 5, 2011 at 7:03 pm
I want to pull the "security context" information from linked servers using a query, rather than Properties > Security and looking at the setting for each linked server (we have hundreds). I am building a database to track all of our linked servers, logins & remote user accounts. Some of these are several years old, so no one knows all the context issues.
Thus far, I've found the following helpful in pulling some of the information I need--but not all:
- General information on all linked servers: exec sp_linkedservers (or select * from sys.servers)
- List users & remote logins by linked server: exec sp_helplinkedsrvlogin
The part that I can't find a query for, or find in system tables, is what security context is used for users not specifically added (not be made, use current security context, or use default of xyz, password 123), and what account is used as the default if that option is selected.
Any help will be greatly appreciated!
Thanks,
~ Clarie DeWayne
October 7, 2011 at 7:16 am
select *
from master.sys.linked_logins
where server_id = [your server id]
returns the logins you defined for the linked server.
The rows with local_principal_id <> 0 are actual local logins you can find in sys.server_principals, mapped to a remote login.
The rows with local_principal_id = 0 are wildcards that are used or not used depending on what you checked in the radio buttons of the security tab.
The radio button options are:
For a login not defined in the list above, connections will:
A) not be made
B) be made without a security context
C) be made using the login's current security context
D) be made using this security context
Let's see what the options mean:
A) No wildcard rows (local_principal_id = 0)
B) The wildcard row will be (local_principal_id = 0, uses_self_credential = 0, remote_name = NULL)
C) The wildcard row will be (local_principal_id = 0, uses_self_credential = 1, remote_name = NULL)
D) The wildcard row will be (local_principal_id = 0, uses_self_credential = 0, remote_name = 'remote_login_name')
Hope this helps
Gianluca
-- Gianluca Sartori
October 10, 2011 at 4:33 pm
This was a huge help. Thank you!
October 11, 2011 at 2:05 am
You're welcome.
Glad I could help
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply