Hi all,Having been asked to grant ddladmin / or dbowner permissions to a user so that he can look at table structures and stored procedures in a database, I looked at how this can be done in a better way. (After all the database owners do NOT want him to be able to mess about in the database at will . . . . and neither do I :D )So among my travels I came across GRANT VIEW DEFINITION ON object to user
which meet the requirements perfectly . . .although after running GRANT VIEW DEFINITION ON ALL to user
the user didn't have permissions to do so . . . Another search revealed a useful sp that did the task, butI also wanted to check if a user had permissions on the object (Like a deny) before granting the permissions . . . searching a bit further showed sp_helprotect would do the task. . . . ah not quite although it shows the user name and the permissions on an object . . . you can't pass the user name to it to get a list of the objects that the user has permissions on.Hmm! Lets have a play with sp_helprotect . . . sp_helptext sp_helprotect, reveals the text of the stored procedure, so use that text as the basis of a usp and we can then do a search by username/login. . . . not quite I get a syntax error when trying to execute the create proc.Msg 195, Level 15, State 10, Procedure VerifyPermissions, Line 330'permission_name' is not a recognized built-in function name.
This appears to be generated at these lines -- Translate ProtectTypeName and ActionFullName to full name, using Shiloh values when possible UPDATE #t1_Prots SET ProtectTypeName = CASE ProtectTypeName WHEN 'G' THEN 'Grant' WHEN 'D' THEN 'Deny' WHEN 'W' THEN 'Grant_WGO' END , ActionFullName = ISNULL( (SELECT v.name FROM sys.syspalnames v WHERE v.class = 'HPRT' AND v.value = ActionName), permission_name(ActionCategory, ActionName) )
The other thing that is interesting is doing a search in sys.all_objects for permission_name or syspalnames reveals no rows . . . if tried looking at all the system tables, procs, functions, views and can't find either of them . . . yet sp_helprotect works so they must exist.Anyone with any ideas? It would be handy to be able to get this usp working . . . although I guess it may prove easier just to do Exec ('exec mydb..sp_helprotect') into a temporary table and then delete the rows I'm not interested in before outputting the data . . . just think using the base code directly would be a neat trick though-- RegardsTony The DBA
What Permissions have been granted to a user?
Posted - 2010-07-21 : 10:20:51
