Order of security Privileges

Posted on 2007-10-02
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
    LVL 27

    Expert Comment

    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

    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

    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

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    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…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now