Verifying the GRANT statement

  • I am working on a test script for the DBAs to run after they run a development script which creates a table and then grants access to a role. It must point out any discrepancy between what the first script was supposed to do and what it actually did. Verifying that the table exists is no problem. However, how do I verify that the GRANT statements were properly executed. That is, assume:

    GRANT select on NewTable to ExistingRole;

    how do I test that ExistingRole indeed has select, and only select, access to the table? I could, I suppose, take on the role and try to select from the table, but I am hoping for a more general method.

    And "make sure the first script executed without error" is not going to fly. That was my original suggestion and the response was several withering glares cast in my direction.:cool:

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

  • You can use the output from one of these commands.

    exec sp_helprotect

    select * from sys.database_permissions

    select * from sys.fn_builtin_permissions(DEFAULT)

  • The system sp was just what I needed. Specifically:

    exec sp_helprotect

    @Name = 'TableName',

    @UserName = 'RoleName',

    @PermissionArea = 'o';

    Thanks a lot.

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

Viewing 3 posts - 1 through 2 (of 2 total)

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