September 20, 2004 at 10:20 am
I've been trying to clean up a database that has privileges granted to individual users and convert these to use roles instead. I just ran into a case where a user was granted access to execute a stored proc that issued an "update statistics" command on a table. This would run fine with the permissions granted to the user, but when I created a role and added the user to the role and granted permission to execute the stored proc to the role, the execution of the stored proc now fails. Is there some fundamental difference in how permissions are granted to users vs roles? Why can't a role execute the "update statistics" command?
Thanks In Advance!!!
Brian
September 20, 2004 at 10:37 am
Did you change any object ownerships ?
September 20, 2004 at 12:06 pm
The user might own things that the role wouldn't.
September 20, 2004 at 2:51 pm
Sure it can be different. The best example is sp_adduser that some apps use provide a front-end add user functionality. sp_adduser is marked as obsolete by Microsoft and it is recommended to use sp_grantdbaccess that is role-oriented. But since my vendor's app uses sp_adduser I discovered the following: sp_adduser only worked if the user who runs it THE DBO no matter what permission you would give to other users or roles. This procedure does not work even for a member of db_owner group. If you open the stored procedure it says explicitly in the code: if not a DBO then exit with error:
-- In Hydra only the user dbo can do this --
if (not is_member('dbo') = 1)
begin
-- AUDIT FAILED SECURITY CHECK --
dbcc auditevent (109, 1, 0, @loginame, @name_in_db, @grpname , NULL)
raiserror(15247,-1,-1)
return (1)
end
So it is possible that no matter what permissions are the internal code may contain checks for certain roles or users
Yelena
Regards,Yelena Varsha
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply