Bunch of power users authenticating via AD roup using SSMS to various DBs with read, write, db_owner. Standard stuff that usually works. But this group obtains access errors such as Errors 4064.
Default DB is correct, permissions applied correctly, but only when sysadmin granted will access work. (know this is not acceptable) There is another AD Group they are members which might cause the default DB conflict, and cannot find any Deny's.
Understand what every permission, role and action does. But why would sysadmin permit access where a correctly deployed db_datareader does not. Any ideas?
It depends. When logging into SSMS, you can specify a db to initially connect to. The master db is typically the default db for AD group, so make sure the "guest" account is master is set up correctly and there are no DENY permissions there that would affect these users trying to log in.
Usually master db although I have used TempDB before. Both were set correctly for this scenario
Due to the users multiple group membership they have to explicitly choose the default DB teach time they connect using SSMS as the will not work as each group has a different default DB. Despite ensuring this is all correct, only sysadmin will permit them access
Since they're coming in using an AD group, it shouldn't be a mis-matching SID issue.
From your SSMS, can you successfully run an "EXECUTE AS LOGIN = [<name_in_their_ad_group>]" and then issue a USE db_name and run a statement? If you can, then it's an issue in their connection string/method. If not, then it really is a permissions issue of some sort.
Thanks for the tips. All failed I'm afraid and spent hours troubleshooting further.
Failed connection attempts moved to throwing a Error: 18456, Severity: 14, State: 11.
States 11 & 12 mean that SQL Server was able to authenticate you, but weren't able to validate with the underlying Windows permissions. It could be that the Windows login has no profile or that permissions could not be checked due to UAC.
The brief update is, experienced the issues in the below articles.
Solution: After using many scripts, SSMS, checking UAC, .. etc. Discovered the AD Group had Status | Settings | DENY. Had not seen it before, don't know how it changed to this, and many scripts did not show. Script that did worked is below.
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c3e0713c-b4e6-400e-9ba2-448cd5bf3cb8/tokenbased-server-access-validation-failed-with-an-infrastructure-error?forum=sqlsecurity
SELECT pr.principal_id, pr.name, pr.type_desc,
pe.state_desc, pe.permission_name
FROM sys.server_principals AS pr
JOIN sys.server_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id;
Hi,
If you are facing SQL Error 4064, then there is a manual way present to fix this issue. By running this code: ALTER LOGIN [test] WITH DEFAULT_DATABASE = master.