locked
How can I find from a single query that a user has which permission on a specific table RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi All,

    How can I find from a single query that a user  has which permission on a specific table. I have a Table named "Property_Address" and a User "ABC" doing update on this table. How can I revoke permissions on this table from this user and how can I find which permissions this user have on Property_Address Table.

    Any help would be appreciate.

    Thursday, April 7, 2011 4:38 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    There is simple function can do this:

    Execute as User = 'ABC'

Select *
From fn_my_permissions('dbo.Property_Address','object')

revert
    Thursday, April 7, 2011 9:26 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Steven's query will confirm the permission. And HelloZishan's query might return a role name that you know 'ABC' is a member of. But 'ABC' could be getting permission because they are a member of a fixed database role like db_datareader. Since db_datareader doesn't have an explicit grant, you won't see it. User 'ABC' might also get permissions because the 'ABC' login is a member of a Windows AD group that you don't even know about.

    But regardless of how the permission originates, to stop it, consider executing:

    DENY UPDATE ON OBJECT::dbo.Property_address TO ABC;

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Thursday, April 7, 2011 4:22 PM
    Answerer

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    I used to following script to find out the database permissions for all users:

    use

    TrainingProclaim

    GO

    SELECT

    state_desc

    + ' ' + permission_name +

    ' on ['

    + SYS.SCHEMAS.name + '].[' + SYS.OBJECTS.name +

    ']

    to ['

    + SYS.DATABASE_PRINCIPALS.name +

    ']'

    COLLATE

    LATIN1_General_CI_AS as [Permissions T-SQL]

    FROM

    SYS.DATABASE_PERMISSIONS JOIN

    sys.objects

    ON

    SYS.DATABASE_PERMISSIONS.major_id = sys.objects.

    OBJECT_ID

    JOIN

    SYS.SCHEMAS

    ON

    SYS.OBJECTS.SCHEMA_ID = SYS.SCHEMAS.

    SCHEMA_ID

    JOIN

    SYS.DATABASE_PRINCIPALS

    ON

    SYS.DATABASE_PERMISSIONS.grantee_principal_id = SYS.DATABASE_PRINCIPALS.principal_id

    order

    by [Permissions T-SQL]

    GO

    Regards,

    

    Thursday, April 7, 2011 5:15 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    There is simple function can do this:

    Execute as User = 'ABC'

Select *
From fn_my_permissions('dbo.Property_Address','object')

revert
    Thursday, April 7, 2011 9:26 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Steven's query will confirm the permission. And HelloZishan's query might return a role name that you know 'ABC' is a member of. But 'ABC' could be getting permission because they are a member of a fixed database role like db_datareader. Since db_datareader doesn't have an explicit grant, you won't see it. User 'ABC' might also get permissions because the 'ABC' login is a member of a Windows AD group that you don't even know about.

    But regardless of how the permission originates, to stop it, consider executing:

    DENY UPDATE ON OBJECT::dbo.Property_address TO ABC;

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Thursday, April 7, 2011 4:22 PM
    Answerer