2

I have two C# apps with Windows Auth to Microsoft SQL Server 2008 R2.

For every app:

  • SQL login based on Windows Group is created
  • SQL server login is mapped to SQL database user
  • user is member of a role
  • role has granted permissions to tables, views, procedures, etc.

Question is:

If I add a single windows user as a member to both windows groups (with are "mapped" to different SQL logins, which are mapped to different SQL users, which belongs to different roles and so on..) - could user run both apps without any problems or only one of them will work properly?

Improve this question
1
  • This belongs on Database Administrators SE. Flagged for migration.
    – Thomas Stringer
    Jun 15, 2012 at 17:59

1 Answer 1

Reset to default
3

If I am understanding your question correctly, then yes the user will be able to run both applications with database connection issues.

If User1 is part of Windows Groups Group1 and Group2, and Group1 is mapped as a SQL Server login, as well as Group2 is mapped to a different SQL Server login. And if Group1 is mapped to a database user in Database1 and Group2 is mapped as a database user in Database2, then User1 should have access to both Database1 and Database2. And if Group1 and Group2 are both mapped to TheOnlyDatabase, then the user will have the accumulations of both database users' permissions.

This will return only one of the database users (as expected):

select user_name() as database_user_name

This will return 1 for both queries if the users is a member of both Windows Groups:

select is_member('Domain\Group1') as member_of_group_1

select is_member('Domain\Group2') as member_of_group_2
Improve this answer
4
  • 90% match :) There is only one Database. Group1 and Group2 are mappped to "TheOnlyDatabase".
    – huhu78
    Jun 15, 2012 at 18:05
  • Another words.. Group1 can run ProcA, ProcB, ProcC, Group2 can run ProcD, ProcE, ProcF. App1 uses ProcA, ProcB, ProcC, App2 uses ProcD, ProcE, ProcF. Users of App1 are members of Group1, users of App2 are members of Group2. Some of users should be users of App1 and App2. Will it works if I make them members of Group1 and Group2?
    – huhu78
    Jun 15, 2012 at 18:10
  • @huhu78 Ok, I see now. Yes, I believe it should be the accumulation of the rights of users that the users have. So User1 using App1 and App2 should be able to run all of those procs.
    – Thomas Stringer
    Jun 15, 2012 at 18:33
  • 1
    That is correct. Permissions are always cumulative, so the windows user will have the sum of the permissions based on the two sql logins from the security groups. It's no different than file permissions based on two different groups. The only thing to watch out for is if one of the groups was denied permissions to an object that was needed by the other group. Deny will always take precedence, so if group1 was denied permissions to table t1 but app2 required the use of t1, then your user that is a member of both groups would not be able to run app2.
    – sqlbattsman
    Jun 17, 2012 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.