Learn how to a build a cloud-first strategyRegister Now


Order of security Privileges

Posted on 2007-10-02
Medium Priority
Last Modified: 2008-03-06
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?  
Question by:BravehearT-1326
  • 2
LVL 27

Expert Comment

ID: 19997890
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.


Author Comment

ID: 19998198
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.
LVL 27

Accepted Solution

ptjcb earned 200 total points
ID: 19998341
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.


Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question