See what user you are connecting to linked SQL server
I wanted to share with you guys a really handy little SQL query that is extremely useful if you are troubleshooting SQL Server linked server connectivity. You may have setup a local server login to remote server login mapping which translates your Windows login to a remote user. Or, you may have a user defined in the “for a login not defined in the list above” section of the Linked Server Properties. How do you know if your user mappings are working? Let’s use a query to see what user you are connecting to linked SQL server with.
See what user you are connecting to linked SQL server
As mentioned above, your linked server properties may look similar to the screen below. Here there is an entry in the top section for local server login to remote server login mapping. So if we are logged in as the “Local Login” for instance, TESTDOMAINjdoe then we can set the remote user to be SA or another SQL login. The way the linked server properties work is if the top section is either blank or doesn’t match, then it goes to the next section which then processes how things are going to be authenticated. As you can see below, the radio button “Be made using this security context” is selected. This means that we are hard setting a SQL login as a catchall for all other logins passed over to the linked server.
SQL Query to find logins
The following SQL query will show the local user as well as the remote user being invoked.
select suser_sname() as LocalUsr, @@servername as LocalSrv,* from openquery(sqlserver,'select suser_sname(), @@servername')
Replace the sqlserver string above with your actual SQL server name. Results will look similar to the following:
Final Thoughts
Hopefully if you have been frustrated in the past on being able to see what user you are connecting to linked SQL server, this post will help with that. Look for an upcoming post on general troubleshooting steps in troubleshooting MSDTC connections in general.
