SQL

See what user you are connecting to linked SQL server

I wanted to share with you guys a really handy little SQL query that helps to see what user you are connecting to linked SQL server...

Highlights

  •  Or, you may have a user defined in the “for a login not defined in the list above” section of the Linked Server Properties.
  • 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.
  • 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.

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.

LSsecurity01

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:

LSsecurity02

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.

Subscribe to VirtualizationHowto via Email 🔔

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Brandon Lee

Brandon Lee is the Senior Writer, Engineer and owner at Virtualizationhowto.com and has over two decades of experience in Information Technology. Having worked for numerous Fortune 500 companies as well as in various industries, Brandon has extensive experience in various IT segments and is a strong advocate for open source technologies. Brandon holds many industry certifications, loves the outdoors and spending time with family.

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.