January 7, 2008 at 12:11 pm
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
January 7, 2008 at 12:22 pm
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)
January 7, 2008 at 1:55 pm
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