October 29, 2013 at 8:24 am
Hi all, bit of a poser that has been bugging me.
I have a sql instance that has this odd problem - creating a login from windows give it rights, check it has connect and public rights as well as dbo of the default database...
However... it gets a login error - 18456 with state 11 in the logs.
Now, where it gets fun is if I grant it SYSADMIN fixed server role, it works just fine... I don't really want to give it sysadmin...
Any ideas? Might someone have mucked about with the public role somehow and 'secured' the server?
Microsoft SQL Server 2008 (SP3) - 10.0.5826.0 (Intel X86) Sep 17 2012 17:27:22 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)
October 29, 2013 at 8:46 am
Look at this blog post:
October 29, 2013 at 9:33 am
In SQL 2008 R2, you may get some more details in the errorlog.
The database he has access to is not in RESTRICTED_USER mode, by chance, is it?
October 29, 2013 at 10:14 am
Thanks for the replies, and the link.
It's looking like a SID lookup error, no AD related errors in the server logs though.
DBS all ok.
Elsewhere I found the hint to grant:
GRANT CONNECT SQL TO [domain\useraccount]
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO [domain\useraccount]
Once this was done the login could get to the server... but not see any databases. But could quite happily use the ones to which I had granted access.
Turns out someone had done something clever to lock down the public role. Eventually getting to the bottom of it.
:@
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply