locked
User doesn't get delete permissions RRS feed

  • Question

  • Running SQL Server 2012 enterprise.

    I created a login for a windows user and then made them part of the db_owner group in a database.  The login can't delete from the table and many permissions are missing when I run:

    execute as user='mydomain\theuser';
    select * from fn_my_permissions('[thedatabase].[dbo].[thetable]','OBJECT')
    revert;

    I tried deleting the user and logins and then re-creating the login and granted it sysadmin.  It still is missing a bunch of permissions.  My login, also a sysadmin user, has all the permissions.  There are no extra permissions on the table in question.

    Any ideas?

    Sunday, September 23, 2012 1:05 PM

Answers

  • What happens when you do EXECUTE AS LOGIN rather than EXECUTE AS USER? When you impersonate a database user, you are sandboxed into the current database, and there can be some unexpected behaviours.

    When you are a sysadmin member, all permission checks are bypassed, so it is a little spooky that the DELETE does not work in this case, but if you are only testing with EXECUTE AS USER, that explain things.

    I was thinking that if you are a db_owner member all permission checks in the database are bypassed, but this may not be the case. Have you checked sys.database_permissions if there are any rows with state = D (as in DENY). Also check sys.database_role_members for membership in db_denydatawriter.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by JustinRush Sunday, September 23, 2012 9:19 PM
    Sunday, September 23, 2012 2:27 PM

All replies

  • What happens when you do EXECUTE AS LOGIN rather than EXECUTE AS USER? When you impersonate a database user, you are sandboxed into the current database, and there can be some unexpected behaviours.

    When you are a sysadmin member, all permission checks are bypassed, so it is a little spooky that the DELETE does not work in this case, but if you are only testing with EXECUTE AS USER, that explain things.

    I was thinking that if you are a db_owner member all permission checks in the database are bypassed, but this may not be the case. Have you checked sys.database_permissions if there are any rows with state = D (as in DENY). Also check sys.database_role_members for membership in db_denydatawriter.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by JustinRush Sunday, September 23, 2012 9:19 PM
    Sunday, September 23, 2012 2:27 PM
  • execute as login works.  Hopefully it works for the end user.  Thanks!

    BTW, there aren't any Deny blocks outside of the diagram sps.

    • Edited by JustinRush Sunday, September 23, 2012 9:21 PM
    Sunday, September 23, 2012 9:20 PM