"Control Server" permission and connecting to SQL 2005 from Linux

  • Hi,

    I have a user that is trying to connect from Linux 2.6.9-34 to a SQL 2005 database using a FreeTDS driver. I created a login on the server and mapped it to a user in the database, giving the user "Connect" permission and leaving the login with the default "Connect SQL" permission. The user received an error "Unable to connect to data source Error: invalid". Being a member of the sysadmin server role allowed them to connect no problem. I obviously don't want them to be a sysadmin so I removed them from this role and gave the account the "Control Server" permission. They could still connect to the database. I then looked at MSDN to find the 18 permissions that "Control Server" comprises, not including itself. I revoked the "Control Server" permission and granted the other 18 permissions one by one. With all 18 permissions granted, the user still could not connect. Does anybody know why this would be?

    Thanks in advance.

    P.S. Here are the 18 permissions:

    GRANT ADMINISTER BULK OPERATIONS TO [mydomain\NTuser]

    GRANT ALTER ANY CONNECTION TO [mydomain\NTuser]

    GRANT ALTER ANY CREDENTIAL TO [mydomain\NTuser]

    GRANT ALTER ANY DATABASE TO [mydomain\NTuser]

    GRANT ALTER ANY ENDPOINT TO [mydomain\NTuser]

    GRANT ALTER ANY EVENT NOTIFICATION TO [mydomain\NTuser]

    GRANT ALTER ANY LINKED SERVER TO [mydomain\NTuser]

    GRANT ALTER ANY LOGIN TO [mydomain\NTuser]

    GRANT ALTER RESOURCES TO [mydomain\NTuser]

    GRANT ALTER SERVER STATE TO [mydomain\NTuser]

    GRANT ALTER SETTINGS TO [mydomain\NTuser]

    GRANT ALTER TRACE TO [mydomain\NTuser]

    GRANT AUTHENTICATE SERVER TO [mydomain\NTuser]

    GRANT CONNECT SQL TO [mydomain\NTuser]

    GRANT EXTERNAL ACCESS ASSEMBLY TO [mydomain\NTuser]

    GRANT SHUTDOWN TO [mydomain\NTuser]

    GRANT UNSAFE ASSEMBLY TO [mydomain\NTuser]

    GRANT VIEW ANY DEFINITION TO [mydomain\NTuser]

  • Control Server = sysadmin. They're the same

    What default db is the login set to? Does the login have a mapped user in that db? Is the default db in restricted user mode?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hi,

    The default database is the user database (in multi user mode) that is being accessed and there is a user mapped, which is a member of db_owner.

  • Strange.

    Can you try and connect from management studio with that login (without the control server privilidge) and see if you can connect. If not, see exactly what error is given.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply