Windows authentication only allows login with sysadmin role

Question

Post reply

Windows authentication only allows login with sysadmin role

sqldriver

SSChampion

Points: 10440
More actions
April 10, 2014 at 1:08 pm

#290240

Hi,
I'm trying to set up our dev team to log in with Windows credentials, but the only security level that will allow this is sysadmin. Everything under that gets rejected, even though Connect SQL is granted. Is this normal behavior, and the only way to add users to datareader is via a SQL login? Is something gone woogy in AD?
Thanks

Viewing 15 posts - 1 through 15 (of 31 total)

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

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 1

Beatrix Kiddo

SSC-Dedicated

Points: 32407

April 11, 2014 at 2:10 am

#1705144

(dup.)

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 2

No, that's not normal. How are you doing it? (Through the GUI or by using create login [domain\user] from Windows ?)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

What do you mean "Everything under that gets rejected"? What is the exact behaviour you're seeing? What errors are you getting?

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

sqldriver SSChampion Points: 10440 More actions · Answer 4

GilaMonster (4/11/2014)
What do you mean "Everything under that gets rejected"? What is the exact behaviour you're seeing? What errors are you getting?

Hi,

I mean that any server role that is not sysadmin cannot login with Windows authentication. Getting generic 'could not connect' errors from SSMS and, from the error log:

exec xp_readerrorlog 0, 1, "error", "error", "20140409", "20140411"

LogDateProcessInfoText

2014-04-10 15:57:50.740LogonError: 18456, Severity: 14, State: 38.

2014-04-10 16:25:28.950LogonError: 18456, Severity: 14, State: 38.

2014-04-10 17:03:34.790LogonError: 18456, Severity: 14, State: 38.

2014-04-10 18:37:55.590LogonError: 18456, Severity: 14, State: 38.

2014-04-10 18:40:35.380LogonError: 18456, Severity: 14, State: 38.

2014-04-10 19:02:26.840LogonError: 18456, Severity: 14, State: 38.

To be precise, I'm on this one:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)

Apr 2 2010 15:48:46

Copyright (c) Microsoft Corporation

Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

Thanks

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 5

As I said before, how are you creating the logins and the users?

sqldriver SSChampion Points: 10440 More actions · Answer 6

Beatrix Kiddo (4/11/2014)
As I said before, how are you creating the logins and the users?

Hi,

I was working on a reply for you; I wanted to test both ways. In this case I'm not sure, because the user was created before I started working here, and I've only made changes to the settings via the GUI.

Thanks

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 7

Beatrix Kiddo

SSC-Dedicated

Points: 32407

April 11, 2014 at 7:50 am

#1705295

Intriguing. The user is mapped to a login, right?

sqldriver SSChampion Points: 10440 More actions · Answer 8

Beatrix Kiddo (4/11/2014)
Intriguing. The user is mapped to a login, right?

Yep. They've been logging in under the sysadmin role for a long time. It's only when I change to anything from bulkadmin to setupadmin that they can't. This behavior doesn't crop up when using SQL logins, though.

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 9

No, a login, not a role. I'm trying to think how to phrase this;

sqldriver SSChampion Points: 10440 More actions · Answer 10

Beatrix Kiddo (4/11/2014)
No, a login, not a role. I'm trying to think how to phrase this;

I know what you're getting at, and yes, the they're added as/mapped to Windows AD credentials.

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 11

Posted too soon!

They log into SSMS as DomainName\Username yes? If you check under Security > Logins > DomainName\Username right-click, Properties, what have they got under Server Roles? And what have they got under User Mapping (i.e. are there any databases they're mapped to?)

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 12

Beatrix Kiddo

SSC-Dedicated

Points: 32407

April 11, 2014 at 8:12 am

#1705315

(Dup!)

sqldriver SSChampion Points: 10440 More actions · Answer 13

Beatrix Kiddo (4/11/2014)
Posted too soon!
They log into SSMS as DomainName\Username yes? If you check under Security > Logins > DomainName\Username right-click, Properties, what have they got under Server Roles? And what have they got under User Mapping (i.e. are there any databases they're mapped to?)

Under server roles: public and sysadmin

Under user mapping: public is checked down the bottom, and a smattering of DBs are checked up top: all but one of them has user as dbo and default schema as dbo. The auslander is domain\user and default schema dbo.

Thanks

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 14

Thanks. It's really hard when you can't just see the instance in question :-D.

So are you saying that if you untick Sysadmin under Server Roles and save it, this prevents that person logging in at all? I wonder if they are missing a default database? In General, what is their default database?