| Author |
Topic |
|
hrishi_des
Yak Posting Veteran
76 Posts |
 Posted - 2007-07-04 : 06:32:58
|
| Hi,How to i find out if any user has execute permission on a stored procedure for e.g. xp_instance_regread?Many Thanks,HrishikeshWhen solution is simple, God is answering…. |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-07-04 : 06:36:15
|
| [code]EXEC sp_helprotect 'MyProc', 'User1'[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2007-07-04 : 06:38:26
|
| Hi,Thanks for a quick reply, but there was one more function like fn_permission which does it for logged in user?I am not able to locate exact function name.Many ThanksWhen solution is simple, God is answering…. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-07-04 : 06:43:35
|
| Do you mean PERMISSIONS() function? Surely it returns permissions on object, but it returns them as bitmap so you have to decipher the bitmap values to understand which permission you have or don't have.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2007-07-04 : 06:45:43
|
| Hi Harsh,I want to check if a user has permission to execute xp_instance_regread extended sp, how do i check that?Many Thanks,HrishikeshWhen solution is simple, God is answering…. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-07-04 : 06:50:59
|
| [code]if permissions(object_id('master..xp_instance_regread ')) & 32 = 32print 'User has execute permission'elseprint 'User does not have execute permission'[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2007-07-04 : 07:31:34
|
| Hi,Hi,how abt this?SELECT * FROM fn_my_permissions('master..xp_instance_regread', 'OBJECT') where permission_name = 'EXECUTE'Kindly advise on its advantages and disadvantages.Many Thanks,HrishikeshWhen solution is simple, God is answering…. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-07-04 : 07:37:37
|
| What is fn_my_permissions()? What are the contents of it? and what's wrong with the earlier solutions I gave?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2007-07-04 : 07:46:21
|
| Hi Harsh,Please don't get me wrong, there is nothing wrong with your solution, i just happend to find alternate solution. You can find more information about it at http://msdn2.microsoft.com/en-us/library/ms176097.aspxMany Thanks,HrishikeshWhen solution is simple, God is answering…. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-07-04 : 07:59:09
|
Oh..I am still stuck on old ways of doing things (SQL 2000).  My Bad, didn't notice it is SQL 2005 forum.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2007-07-05 : 01:33:49
|
| Hi Harsh,To give you some idea about the problem i was facing, We wanted to find out default database data file directory i.e. specified in Database Setting property of server, Only way i knew was reading registry using master..xp_instance_regread extended stored procedure. But on client site there could be a situation in which the user may not be granted execute permission on some extended stored procedures and also microsoft may change the behaviour of these function as it is undocumented. So to check user permission on that sp i was using..SELECT * FROM fn_my_permissions('master..xp_instance_regread', 'OBJECT') where permission_name = 'EXECUTE'So kindly tell me if this is ok if i am on SQL server 2005 or also if u can suggest any other elegant way to know default database data file directoryMany Thanks,HrishikeshWhen solution is simple, God is answering…. |
|
|
|
|
Permission on a Stored Procedure