Users are unable to connect to SQL Server 2005 unless they are in the sysadmin group

Question

We are running SQL Server 2005 in a Windows 2003 domain and I have a situation where some of my users are unable to connect to the SQL Server unless they are a member of the sysadmin group.  Any attempts by these users to login result in a login failed,

Error: 18456, Severity: 14, State: 11

Which indicates that it is a valid user who does not have access to this SQL Server.

I have been able to narrow the failures down to the following situation:

Create a user, TestUser1, as a member of 1 domain local group TestGroup1

Give TestGroup1 access to SQL (standard public access to master)

All good.  Login succeeds.

Add TestUser1 to another domain local group TestGroup2

Attempt to login to SQL Server -> login failed.

Add the user explicitly -> login failed

Add one of the groups to sysadmin -> login succeeds

It seems that as long as the user is a member of more than one AD group, and none of those groups is a member of the sysadmin server role then the user is unable to login.  Obviously having all of the users as sysadmin isn't a workable solution, has anyone seen this issue before?

I have been able to replicate a similar situation in our test domain, but in that case the issue is resolved by adding the users explicitly to SQL Server (still not an ideal solution).

Interestingly, if I run the same test in our test domain but use global groups, it works.  But unfortunately the network admin tells me the groups must stay as local.

Any help would be greatly appreciated.

Regards,

Daniel Watkins

Answer 1

  From your description it is unclear if you created a login or a user for the Windows group and it may be possible that you were creating users instead of logins by mistake.

 

  Creating a user grants access to the database where the user was created, creating a login grants access to the SQL Server instance. For example:

 

  -- grant access to the server to a Windows group

CREATE LOGIN [domain\group_name] FROM WINDOWS

go

 

  Please, let us know if this information helped to solve your problem. If not, we will probably need to ask you for a more detailed description of your configuration and your scripts so we can reproduce the failure in the lab.

 

  Thanks a lot,

-Raul Garcia

  SDE/T

  SQL Server Engine

Answer 2

Sorry I wasn't clear.  All users are Windows users.  The groups/users were added to SQL Server using the GUI, so Connect->Security->Logins->New Login-> search for the domain group/user, then in User Mapping, grant membership to the public role in master and click ok.

So I assume this is equivalent to CREATE LOGIN. 

I'm afraid I've been lazy and just used the GUI while running these tests.

 

Thanks for the help,

Daniel

Answer 3

Problem solved.  After I deleted everything and recreated the test environment I discovered that someone had set one of the security groups to Deny access to the server. 

Given that the users can access the server if they were given SA access, it didn't occur to me to go back and check that setting.

Thanks for your help.

Sideline: Does anyone know if that's a change in SQL 2005?  I thought a deny override all access, even SA?

Regards,

Daniel

Answer 4

  That explains it. Sysadmin members are not subject to any permission checks as they are considered owners of the system therefore any denied permission (like this particular case) is always ignored. This behavior also existed in older versions of SQL Server.

 

  Thanks a lot,

-Raul Garcia

  SDE/T

  SQL Server Engine

Answer 5

I'm having a similar problem and need to find a way to create a sysadmin account in SQL 2005 programatically. I'm looking for a way to do what the Surface Area Configuration utility does when you select "Add New Administrator". Is this interface exposed any where?

 

Thanks.

Answer 6

You can simply create a login:

CREATE LOGIN Alice...

and then make this login a member of the sysadmin role:

sp_addsrvrolemember ('X', 'sysadmin')

Thanks
Laurentiu

Answer 7

I was having this same problem and couldn't connect to sql server 2005 unless the user I was connecting with was a 'sysadmin'.  None of the above corrections helped me resolve my problem, but they did lead me in the right direction as to what was going wrong.  For some reason the TSQL TCP Endpoint for the 'public' role was not enabled and thus would not allow any sort of tcp connection to connect to my SQL Server instance.  I'm guessing by default that every login is a member of the 'public' role so anyone attempting to login as a non-sysadmin would be denied because no connection was allowed over tcp.  From what I've read the sysadmin role is automatically granted access and thus is never checked which would explain why I would be able to login when I had sysadmin and public roles mapped to my login.  To check if this could be your problem, login to sql server using enterprise manager and locate the public role.  Right click, go to properties then highlight the permissions tab.  Make sure there is a TCP endpoint enabled and that it has connect privileges. 

   Just for clarification this problem arose when I was using an app pool on my web server and the only way that the service account running the app pool could connect to my database on my database server was when the service account had sysadmin privileges on my SQL Server instance.  Enabling the TSQL TCP endpoint for the 'public' role fixed my problem.