• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

Order of security Privileges

If someone has an individual windows login to SQL Server that is assigned db_owner privileges against a particular database and is also part of another windows group that does not have dbo privileges against the same database - what permissions takes preference?  
0
BravehearT-1326
Asked:
BravehearT-1326
  • 2
1 Solution
 
ptjcbCommented:
Define "does not have". That is vague.

Have the permissions been explicitly denied?
Have the permissions been explicitly revoked?

A Windows account has been granted (I assume that what you mean by assigned) db_owner rights to a database. The account would still have the rights to the database, unless those rights were explicitly denied or revoked for the Windows group.

The GRANT removes the denied or revoked permission at the level it is granted, but the denial at the another level still applies.

For example, if your Windows account (A) needed to see a view, but the Windows group was explicitly denied access to the database, you could grant A permission to see the view. A would see the view, but have no other permissions in the database.

If, in your opening statement, you had explicitly denied permission to the group, then the denial takes precedence. For example, if you gave A db_owner rights, but the Windows group only has select and denies insert, delete, and update. A would only be able to select.

The revoke statement removes a previously granted or denied permission at that level.



0
 
BravehearT-1326Author Commented:
Sorry for the hazy question...

What I was getting at is if an individual user account is created and the DB_Owner privilege is assigned to that account for a particular database at the time of the login creation and the windows group (which the user is a member of) account does not have this DB_Owner privilege what would take preference.

Going on what you posted the individual account would still have db_owner privilege against the database in question. So the highest privilege would take effect.
0
 
ptjcbCommented:
Yes, the user would have the rights, unless you have explicitly denied or revoked the permissions.

The Windows group has no permissions. The Windows group does not matter. He or she has been granted the permissions.

No one else in the Windows group may be able to do anything in that database. That depends on what rights you have given to PUBLIC (a different issue) in that database.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now