What is executing my Stored Procedure?

• grahamc

  SSCertifiable

  Points: 6762

  More actions

  October 7, 2010 at 4:07 am

  #1233379

  barry.smallman (10/7/2010)

  ---

  Hi,

  Yes, I tried amending the SP to query the sys.dm_exec_connections DMV as suggested in the reply by Lowell!!

  When the stored procedure failed to run this morning, I started investigating why it couldn't find the DMV, only to discover that this is a SQL Server 2000 instance!

  (I have an excuse - I've only been here a week, so I'm still learning what instances exist)

  I shall have to look into using the server side trace approach, but that will take some investigation, as I have never set one up before.

  Looked at the windows scheduled tasks? More likely an isql command if 2000

• Lowell

  SSC Guru

  Points: 323504

  More actions

  October 7, 2010 at 4:20 am

  #1233386

  barry.smallman (10/7/2010)

  ---

  Hi,

  Yes, I tried amending the SP to query the sys.dm_exec_connections DMV as suggested in the reply by Lowell!!

  When the stored procedure failed to run this morning, I started investigating why it couldn't find the DMV, only to discover that this is a SQL Server 2000 instance!

  (I have an excuse - I've only been here a week, so I'm still learning what instances exist)

  I shall have to look into using the server side trace approach, but that will take some investigation, as I have never set one up before.

  Barry most of the query i posted does not need the dmv; i only included it to get the IP address.

  even then, if it is being called by windows task as someone suggested, you'll only end up with a bit of info to help track it down.

  /*--example results

  EventDate dbname hostname applicationname procedurename userid unsername suserid susername Is_ServerAdmin_Sysadmin Is_DB_owner Is_DDL_Admin Is_DB_Datareader ORIGINAL_LOGIN

  2010-10-07 06:18:11.227 master STORMDEV Microsoft SQL Server Management Studio - Query NULL 1 dbo 261 Stormdev\Lowell 1 1 1 1 Stormdev\Lowell

  */

  --this should work in SQL2000

  SELECT

  getdate() as EventDate,

  DB_NAME() As dbname,

  HOST_NAME() As hostname,

  APP_NAME() as applicationname,

  OBJECT_NAME(@@PROCID) as procedurename,

  USER_ID() as userid,

  USER_NAME() as unsername,

  SUSER_ID() as suserid,

  SUSER_SNAME() as susername,

  IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

  IS_MEMBER('db_owner') AS [Is_DB_owner],

  IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

  IS_MEMBER('db_datareader') AS [Is_DB_Datareader],

  ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• barryFS

  SSC Eights!

  Points: 972

  More actions

  October 7, 2010 at 4:36 am

  #1233395

  Thanks for that Lowell.

  I have added the SELECT STMT to the SP.

  I shall check it again tomorrow am to see what it gives me.

  P.S. It didn't like the 'ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]' so I removed that.

• Grant Fritchey

  SSC Guru

  Points: 398694

  More actions

  October 7, 2010 at 5:31 am

  #1233419

  barry.smallman (10/7/2010)

  ---

  Hi,

  Yes, I tried amending the SP to query the sys.dm_exec_connections DMV as suggested in the reply by Lowell!!

  When the stored procedure failed to run this morning, I started investigating why it couldn't find the DMV, only to discover that this is a SQL Server 2000 instance!

  (I have an excuse - I've only been here a week, so I'm still learning what instances exist)

  I shall have to look into using the server side trace approach, but that will take some investigation, as I have never set one up before.

  To set up the server side trace, you don't have to write it from scratch. You can set up a trace in Profiler and then generate a script from there. That's really one of the easiest ways to get the server side trace set up. Just remember to put a filter in for your procedure.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Rick.Cornell.SME

  SSCrazy Eights

  Points: 8597

  More actions

  October 8, 2010 at 12:17 pm

  #1234440

  Thats what I am thinking, Windows Scheduled Task?