locked
Permissions to Linked Server (via guest?) RRS feed

  • Question

  • Hi All,

    I've been attempting to Linked Server to one of our production servers (2005), creation of the link works when I set the permissions level to "Current User"  (I'm an admin on all servers).

    Scenario A:  I created a link an instance of 2012 running on my local PC.  I can see all dbs/tables/data on the production server through that link.

    Scenario B:  On a Server running 2012, I set up a link just like on my local instance.  I could only see some db's and tables.  The same thing happened on our beta server running 2005.

    I noticed on the DB's I could not see, account [Guest] was missing.  After adding [Guest] I could then see the tables (on the production server), but not data.  Adding read permissions to [Guest] let me see the data.

    I'd rather not give [guest] global permissions, is there a fix or suitable workaround?

    Thanks for any advice!

    Mac McCaskie


    Friday, January 11, 2013 8:26 PM

Answers

  • On the servers where you are an admin (which I think is all of them), are connecting to SQL Server using Windows Authentication, and connecting because you have a login under your own name, which is a member of the sysadmin fixed server role? You should.

    But if you are relying on a SQL Server Windows Authentication login for BUILTIN\ADMINISTRATORS then your problem is that your connection is not using your local administrator credentials. Then it would try to get you in as a guest, which is apparently working.

    So, if this second scenario sounds correct, resolve it by connecting to the SQL Server and adding a login for your domain user account, so you don't need administrator credentials.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Mac_McCaskie Saturday, January 12, 2013 4:18 PM
    Friday, January 11, 2013 9:01 PM
  • To figure out who the remote SQL Server thinks you are, execute:

    SELECT SUSER_SNAME() AS YourLogin, USER_NAME() AS YourUser;

    (I think) you hope to see your domain user account and then dbo.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Mac_McCaskie Saturday, January 12, 2013 4:18 PM
    Friday, January 11, 2013 11:55 PM

All replies

  • On the servers where you are an admin (which I think is all of them), are connecting to SQL Server using Windows Authentication, and connecting because you have a login under your own name, which is a member of the sysadmin fixed server role? You should.

    But if you are relying on a SQL Server Windows Authentication login for BUILTIN\ADMINISTRATORS then your problem is that your connection is not using your local administrator credentials. Then it would try to get you in as a guest, which is apparently working.

    So, if this second scenario sounds correct, resolve it by connecting to the SQL Server and adding a login for your domain user account, so you don't need administrator credentials.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Mac_McCaskie Saturday, January 12, 2013 4:18 PM
    Friday, January 11, 2013 9:01 PM
  • Rick, thanks for the reply.

    You're reply is making me think, and I hate it when that happens.  Help me understand this.  We do the 1st scenario but it's acting like #2.

    We log in via Windows Authentication and have matching login ID's on all sql servers (<domain>\<username>).  The db admin's are in an AD group <domain>\ASD Admins, which is how IT manages our permissions.  Although some of us have assigned ourselves sysAdmin rights on the sql servers, not all have and have only public assigned (server level) - but effective rights are God-Like.

    Would I be correct to suspect our permissions in Active directory need to be changed?  If so, changed to what?

    Thanks

    Mac McCaskie

    Friday, January 11, 2013 10:05 PM
  • When you connect to a linked server, you are connected to it, just as if you were connected to directly.

    You can connect to the server with the same credentials as you connected to the source server, or you have have a login mapping set up. The mapping is always to an SQL login.

    So if you connect to server A on which you are sysadmin, and from A run a query on query B where you are not sysadmin, you will not be sysadmin just because you come from server B.

    Things may get a little muddled if you are on SQL 2005 and rely on BUILTIN\ADMINISTRATORS. I don't know if can happen, but possibly the admin token may be lost in the hop. (It can certainly happen that you cannot connect to server B at all because of the double hop that people run into.)

    Note that BUILTIN\ADMINISTRATORS is not granted access to SQL server by default in SQL 2008, and in any case it is nothing you should rely on. SQL Server permissions should be granted in SQL Server. However, you can add a Windows Group to sysadmin, and then control membership in that group through the AD.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, January 11, 2013 10:50 PM
  • To figure out who the remote SQL Server thinks you are, execute:

    SELECT SUSER_SNAME() AS YourLogin, USER_NAME() AS YourUser;

    (I think) you hope to see your domain user account and then dbo.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Mac_McCaskie Saturday, January 12, 2013 4:18 PM
    Friday, January 11, 2013 11:55 PM
  • Thank you Rick and Erland,

    I'll put the windows group in sysAdmin and see if that fix's it.

    Mac McCaskie

    Saturday, January 12, 2013 4:20 PM