| Author |
Topic |
|
ssunny
Posting Yak Master
133 Posts |
 Posted - 2008-06-03 : 11:06:29
|
| Hi All,Is there any way to find out what are the store procs. being used by database user 'xyz'.basically I am trying to figure out the role of a user 'xyz' (what it does in db) and store procs. used by it in it's external calls to database.Thanks in advance. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-03 : 11:09:22
|
| Can't you find database level and object level permission with sp_helplogins'xyz' or sp_helpuser'xyz' & sp_helprotect? |
 |
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2008-06-03 : 11:30:39
|
| Thanks for the reply.sp_helplogins and sp_helpuser gives me an overview of the user account 'xyz'.But what I need to find out is what are the store procs used by 'xyz' to make a db calls from front application.I think in other words which are the store procs given execute permission to user 'xyz'.Is there any DBCC command for that?Thanks. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-03 : 11:35:25
|
| Did you check with sp_helprotect in Booksonline? Double click user xyz in database level and see all stored procs . |
|
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2008-06-03 : 15:28:47
|
| thanks sodeep,can i use sql profiler to find out how many sp are running by login name 'xyz'? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-03 : 15:34:00
|
| Yes you can, filter trace by login name. |
|
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2008-06-03 : 15:40:22
|
| so what would be my Template name in that case??I'm not that good at profiler.pleae help me out.thanks. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-03 : 15:46:51
|
| You can choose template like TSQL_SP or default one. Make sure you filter by login name . |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-03 : 16:48:33
|
| You set events to trace and filter, then save it as your template. |
|
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2008-06-04 : 10:18:45
|
| Guys I had both SQL Standard and TSQL_Sps traces last night filtered by login name xyz.In both cases what I got was queries issued by user xyz. I did not get any store proc names. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-04 : 10:22:59
|
| you should use check stored procedure events like sp:completed and others to see stored procedure.It won't come if they haven't run while you are running profiler. |
|
|
|
ssunny
Posting Yak Master
133 Posts |
Posted - 2008-06-05 : 15:00:18
|
| I did check all the sp events like sp:starting, sp:complete but all I got was queries not the sp names.But I think that's enough for now.Thanks to all who helped me out. I really appreciate your help. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-06-05 : 15:22:57
|
| Let me rephrase it:"you should use check stored procedure events like sp:completed and others to see stored procedure.It won't come if they haven't run while you are running profiler" |
|
|
|
|
find out sp used by a db user