Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Windows authentication only allows login with sysadmin role Expand / Collapse
Author
Message
Posted Thursday, April 10, 2014 1:08 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 384, Visits: 1,516
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

Post #1560592
Posted Friday, April 11, 2014 2:10 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:56 AM
Points: 451, Visits: 1,665
No, that's not normal. How are you doing it? (Through the GUI or by using create login [domain\user] from Windows ?)

Post #1560751
Posted Friday, April 11, 2014 2:10 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:56 AM
Points: 451, Visits: 1,665
(dup.)
Post #1560752
Posted Friday, April 11, 2014 2:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:18 PM
Points: 42,449, Visits: 35,504
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 2008, MVP
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

Post #1560756
Posted Friday, April 11, 2014 7:35 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 384, Visits: 1,516
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"

LogDate ProcessInfo Text
2014-04-10 15:57:50.740 Logon Error: 18456, Severity: 14, State: 38.
2014-04-10 16:25:28.950 Logon Error: 18456, Severity: 14, State: 38.
2014-04-10 17:03:34.790 Logon Error: 18456, Severity: 14, State: 38.
2014-04-10 18:37:55.590 Logon Error: 18456, Severity: 14, State: 38.
2014-04-10 18:40:35.380 Logon Error: 18456, Severity: 14, State: 38.
2014-04-10 19:02:26.840 Logon Error: 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
Post #1560912
Posted Friday, April 11, 2014 7:43 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:56 AM
Points: 451, Visits: 1,665
As I said before, how are you creating the logins and the users?
Post #1560921
Posted Friday, April 11, 2014 7:47 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 384, Visits: 1,516
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
Post #1560923
Posted Friday, April 11, 2014 7:50 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:56 AM
Points: 451, Visits: 1,665
Intriguing. The user is mapped to a login, right?
Post #1560927
Posted Friday, April 11, 2014 8:08 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 384, Visits: 1,516
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.
Post #1560944
Posted Friday, April 11, 2014 8:10 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 2:56 AM
Points: 451, Visits: 1,665
No, a login, not a role. I'm trying to think how to phrase this;

Post #1560945
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse