How to track SP

Question

Post reply

How to track SP

niladri.primalink

SSCommitted

Points: 1751
More actions
May 7, 2013 at 1:54 am

#394728

Hi All,
as a newbie database admin I am facing challenge. I need to track an SP. the SP name is structuralcheck_mfs and the database name is AP_Bureau.
I want to check who has run this SP for last 7 days. what is the creation date and the modification date. I also want to track their IP address.
Please help!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

You can run the following statement in order to find out the procedure's creation date, last modified date and creator:

select create_date, modify_date, suser_sname(principal_id) from sys.objects where name = 'structuralcheck_mfs'

Notice that if the procedure was written by a member of the sysadmin server role, then you won't be able to see who wrote it.

If you want to know who is using the procedure, when it is used and the client's IP, you'll need to create a server side trace or work with profiler or extended events in order to save this data when the procedure is activated. You need to prepare this in advance, so if you want to know who used it in the past, and you didn't use one of those tools, then you can't get this information.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

niladri.primalink SSCommitted Points: 1751 More actions · Answer 2

Hi SSC,

thanks for your reply. I need to check how many times my query has been modified and from which IP as the users are using the same user name so I need to track the IP address.

please help!

Lowell SSC Guru Points: 323518 More actions · Answer 3

you cannot find past events if they were not captured by some sort of auditing that YOU added...If You don't have a previous auditing setup like Adi Cohn mentioned, you can only set it up for the future events.

i would say the easiest way is to modify the procedure itself to capture that information...especially if you need the IP address; that will not be available outside of the conenction, and SQL2008 and above added the new ConnectionProperty() functions that can help.

first, you need an audit capture table, and everyone needs INSERT permissions to the table itself:

CREATE TABLE [dbo].[ProcedureAudit] (

[EventDate] DATETIME NOT NULL,

[DBName] NVARCHAR(128) NULL,

[CurrentUser] NVARCHAR(128) NULL,

[HostName] NVARCHAR(128) NULL,

[ApplicationName] NVARCHAR(128) NULL,

[ProcedureName] NVARCHAR(128) NULL,

[Userid] SMALLINT NULL,

[UserName] NVARCHAR(128) NULL,

[sUserid] INT NULL,

[sUserName] NVARCHAR(128) NULL,

[Is_ServerAdmin_Sysadmin] INT NULL,

[Is_DB_owner] INT NULL,

[Is_DDL_Admin] INT NULL,

[Is_DB_Datareader] INT NULL,

[ORIGINAL_LOGIN] NVARCHAR(4000) NULL,

[net_transport] SQL_VARIANT NULL,

[protocol_type] SQL_VARIANT NULL,

[auth_scheme] SQL_VARIANT NULL,

[local_net_address] SQL_VARIANT NULL,

[local_tcp_port] SQL_VARIANT NULL,

[client_net_address] SQL_VARIANT NULL,

[physical_net_transport] SQL_VARIANT NULL)

--make sure the procedure will be able to insert for any users permissions schema.

GO GRANT INSERT ON [ProcedureAudit] TO PUBLIC

:

Then inside the procedure, just add this:

--for 2008 and above:

--the auditing snippet below works fine in a

--login trigger,

--database trigger

--or any stored procedure.

INSERT INTO [dbo].[ProcedureAudit](EventDate,DBName,CurrentUser,HostName,ApplicationName,ProcedureName,Userid,UserName,sUserid,sUserName,Is_ServerAdmin_Sysadmin,Is_DB_owner,Is_DDL_Admin,Is_DB_Datareader,ORIGINAL_LOGIN,net_transport,protocol_type,auth_scheme,local_net_address,local_tcp_port,client_net_address,physical_net_transport)

SELECT

getdate() AS EventDate,

DB_NAME() AS DBName,

CURRENT_USER AS CurrentUser,

HOST_NAME() AS HostName,

APP_NAME() AS ApplicationName,

OBJECT_NAME(@@PROCID) AS ProcedureName,

USER_ID() AS Userid,

USER_NAME() AS UserName,

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],

ConnectionProperty('net_transport') AS 'net_transport',

ConnectionProperty('protocol_type') AS 'protocol_type',

ConnectionProperty('auth_scheme') AS 'auth_scheme',

ConnectionProperty('local_net_address') AS 'local_net_address',

ConnectionProperty('local_tcp_port') AS 'local_tcp_port',

ConnectionProperty('client_net_address') AS 'client_net_address',

ConnectionProperty('physical_net_transport') AS 'physical_net_transport'

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!

niladri.primalink SSCommitted Points: 1751 More actions · Answer 4

thanks Lowell,

I am going to do that.

Hope it helps me a lot

Lowell SSC Guru Points: 323518 More actions · Answer 5

niladri.primalink (5/7/2013)
Hi SSC,
thanks for your reply. I need to check how many times my query has been modified and from which IP as the users are using the same user name so I need to track the IP address.
please help!

whoops, i may have misread teh question; i thought you wanted to know who RAN the proc, not who modified it.

you mean when someone modified the code itself?

that's captured in the default trace, if not too much time has passed:

the schema changes history report can help, but you will not have what specifically changed in the code: also no IP address is captured.

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!

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 6

You can also get that info from default trace file which SQL server maintains.

Open .trc file from your ...\MSSQL\LOG folder and search for the stored procedure which you are looking for.

But default trace gets rewriten in time, so you will get only recent chnages.