locked
Select Column permissions (Deny) for current user RRS feed

  • Question

  • Hello,

    how to get (select) the columns from a table where the current user has no rights to change it (DENY on a column)?

    background: we use Stored procedures (Insert, Update, Delete) to Grant Exceute access to tables - now we have the need to
    deny some users to change some columns - we set the columns permission to deny for that users/groups and want to
    select them before the Update statement in the Stored procedures happens (maybe in a trigger) - if there is a deny on a column and
    the new value is different to the old value we want to raise an error (RaiseError)...

    regards
    Wednesday, February 24, 2010 10:55 AM

Answers

  • Hi madrianr,

    The following script maybe helpful:

    if exists(
         select dp.major_id
         from sys.database_permissions dp
         where grantee_principal_id=USER_ID(@UserName)
         and major_id=object_id(@TableName)
         and minor_id=0
         union all
         select dp.major_id
         from sys.database_permissions dp
         join sys.columns c on c.object_id=dp.major_id
         where grantee_principal_id=USER_ID(@UserName)
         and dp.major_id= object_id(@TableName)
         and c.name = @ColumnName
         and dp.minor_id = c.column_id
    )
         -- Your Code

    Regards,
    Tom Li - MSFT
    • Marked as answer by Tom Li - MSFT Tuesday, March 9, 2010 8:32 AM
    Tuesday, March 2, 2010 3:56 AM
  • Hello,

    that help's me a lot - thank you...

    robert
    • Marked as answer by Mad Rian Tuesday, March 2, 2010 12:29 PM
    Tuesday, March 2, 2010 12:29 PM

All replies

  • Hi madrianr,

    From your description, a user with limited privilege in your SQL Server Database system. You have denied the user to select/update data of some specified column. Now, you have the need to get the value sometimes.

    If I misunderstand, please let me know.

    In this situation, You can consider to use "EXECUTE AS" clause to switch context.
    Assume that you have a login named B who doesn’t have the privilege, and another login named A who have the privilege.
    You can use “EXECUTE AS” to switch the context from B to A using the following clause:
    EXECUTE AS LOGIN=’A’
    But you need grant the privilege of impersonate to A so that A could impersonate B before you use “EXECUTE AS”.
    GRANT IMPERSONATE ON LOGIN::A to B
    After you complete your mission, you can use “REVERT” to back to your previous context for security.

    For more information:
    "EXECUTE AS":http://technet.microsoft.com/en-us/library/ms181362.aspx
    "Context Switching":http://technet.microsoft.com/en-us/library/ms188268.aspx
    "GRANT IMPERSONATE":http://technet.microsoft.com/en-us/library/ms178640%28SQL.90%29.aspx

    Hope this helpful.

    Regards,
    Tom Li - MSFT
    Monday, March 1, 2010 5:18 AM
  • Hello Tom,

    no that is not what I need - we need a SQL-Statement where we are able to find out on which columns the current user has Deny permission on...

    we still have grant EXECUTE permission directly on stored procedure for Insert/Update but some user
    sould have the ability to change all columns except 2 -> that is why we deny changes to columns
    for the most users and want to find out in a Trigger if the value of that columns changes and the user has
    no right to do this..

    regards
    Monday, March 1, 2010 7:02 AM
  • Hi madrianr,

    The following script maybe helpful:

    if exists(
         select dp.major_id
         from sys.database_permissions dp
         where grantee_principal_id=USER_ID(@UserName)
         and major_id=object_id(@TableName)
         and minor_id=0
         union all
         select dp.major_id
         from sys.database_permissions dp
         join sys.columns c on c.object_id=dp.major_id
         where grantee_principal_id=USER_ID(@UserName)
         and dp.major_id= object_id(@TableName)
         and c.name = @ColumnName
         and dp.minor_id = c.column_id
    )
         -- Your Code

    Regards,
    Tom Li - MSFT
    • Marked as answer by Tom Li - MSFT Tuesday, March 9, 2010 8:32 AM
    Tuesday, March 2, 2010 3:56 AM
  • Hello,

    that help's me a lot - thank you...

    robert
    • Marked as answer by Mad Rian Tuesday, March 2, 2010 12:29 PM
    Tuesday, March 2, 2010 12:29 PM
  • Hi madrianr,

    From your description, a user with limited privilege in your SQL Server Database system. You have denied the user to select/update data of some specified column. Now, you have the need to get the zune value sometimes.

    If I misunderstand, please let me know.

    In this situation, You can consider to use "EXECUTE AS" clause to switch context.
    Assume that you have a login named B who doesn’t have the privilege, and another login named A who have the privilege.
    You can use “EXECUTE AS” to switch the context from B to A using the following clause:
    EXECUTE AS LOGIN=’A’
    But you need grant the privilege of impersonate to A so that A could impersonate B before you use “EXECUT AS”.
    GRANT IMPERSONATE ON LOGIN::A to B
    After you complete your mission, you can use “REVERT” to back to your previous context for security.

    For more information:
    "EXECUTE AS":http://technet.microsoft.com/en-us/library/ms181362.aspx
    "Context Switching":http://technet.microsoft.com/en-us/library/ms188268.aspx
    "GRANT IMPERSONATE":http://technet.microsoft.com/en-us/library/ms178640%28SQL.90%29.aspx

    Hope this helpful.

    Regards,
    Tom Li - MSFT

    Thanks a lot! It is the way! But I still wonder why.
    Tuesday, August 17, 2010 4:04 AM
  • Hi tomas,

    Thanks for your post.

    I recommend that you ask it in a new thread, then you will get quicker support.


    Regards, Tom Li
    Wednesday, August 18, 2010 1:39 AM