GRANT EXECUTE ... TO [blah] AS [dbo] - What does the "AS DBO" do exactly?

  • SoHelpMeCodd

    SSCertifiable

    Points: 5747

    Resurrecting an old thread.
    [AS principal] sets the grantor_principal_id in sys.database_permissions. As others mentioned, if the caller (a database_principal ) does not specify GRANT ... [AS principal], sys.database_permissions' grantor_principal_id will be set to the caller's principal_id. If that principal_id is subsequently dropped from a database (e.g. a member of db_owner role changed positions or left the company), the permissions that were granted (AS that principal_id) will also be dropped. Perhaps that loss of granted permissions is not a good thing.... 

    To avoid such a loss of permissions, a member of the db_owner role should consider using GRANT ... AS dbo. Ownership chaining and permissions are still applicable. We are prevented from running DROP USER [dbo] (you will receive Msg 15150 "Cannot drop the user 'dbo'."). Thus whenever a GRANT AS [dbo] is performed, those granted permissions will not be removed (outside of an explicit REVOKE). Perhaps that retention of granted permissions is a good thing....

Viewing post 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply