1. domain\user has permissions to connect (authenticate) to a SQL server using domain\security_group_1 which is not mapped to any DBs.
  2. domain\user is also a part of domain\security_group_2 which is an orphaned database principal in all DBs on same SQL server ( meaning domain\security_group_2 corresponding login is not available to authenticate on the SQL server ) with DML permissions on the DB level.

In this case it has been observed that domain\user can connect to the SQL server and is able to perform DML operations even though its individual object principal (server/DB principal) does not have access to perform such operations by itself.

Does this sound like a security flaw within SQL server?

  • 1
    The login has access through security_group_1, and their sid will map to a user within security_group_2, which will give them the rights and permissions into those databases. This is how security is meant to work. If you do not want the login to have access then you would need to put explicit deny permissions on those databases for that particular login. – Nic Aug 9 '17 at 22:22
  • ok .. I have the DBs replicated to 3 rd node via AWAG , now the domain\ User needs access to the DB in 3rd node using domain\security_group _2 , in this case how I do I prevent the user from not accessing data on primary node while granting him access in secondary node , by the way the domain\security_group_1 on primary node is used for granting him access to AG dashboard but not any DBs in particular . – SQLequal Aug 9 '17 at 22:28
  • Basically you're using the group login for too many things. Put them as part of a new group and give them specific rights. If you only want them to access the 3rd node - which has no actual meaning in an AG - then only create their login there. DENY CONNECT to the databases if you absolutely must use the same security group for multiple unrelated security items. – Sean Gallardy - Retired User Aug 10 '17 at 2:34
  • domain\security_group_1 on primary is to grant access to Dashboard for Availability group, domain\security_group _2 is to grant access to read-only DBs on third node , but since the DBs are replicated from Primary , you cannot map domain\security_group _2 on 3 rd node (secondary) to the dbs in read-only mode and hence they need to be created as a orphan users in primary . now , denying access to DBs on primary applies denies access to domain\user irrespective of which AD group they belong to in secondary . – SQLequal Aug 10 '17 at 16:40
  • Also DENY CONNECT prevents connecting domain\user to primary via domain\security_group _1 which is necessary for the users to get access to the AG Dashboard on primary. – SQLequal Aug 10 '17 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.