November 21, 2006 at 12:33 pm
Is there a way to know who is executing a stored proc from within that proc? We want to log activity, who is running what, when, how many times, etc.
November 21, 2006 at 12:41 pm
IF EXISTS(SELECT * FROM dbo.SysObjects WHERE Name = '' AND XType = 'U' AND USER_NAME(uid) = 'dbo')
DROP TABLE dbo.ProcActivity
GO
CREATE TABLE dbo.ProcActivity
(
DateActivity DATETIME NOT NULL CONSTRAINT D_ProcActivity_DateActivity DEFAULT (GETDATE()) CONSTRAINT PK_ProcActivity_DateActivity PRIMARY KEY CLUSTERED
, UserName VARCHAR(50) NOT NULL CONSTRAINT D_ProcActivity_UserName DEFAULT (USER_NAME())
, ProcName SysName NOT NULL
)
GO
--THIS LINE WILL ONLY WORK INSIDE A STORED PROC OR A TRIGGER
INSERT INTO dbo.ProcActivity (ProcName) SELECT OBJECT_NAME(@@PROCID) AS ProcName
GO
--DROP TABLE dbo.ProcActivity
Then query that table as needed to do your reports.
The insert line would have to be placed in each procedure.
November 21, 2006 at 2:41 pm
Elegant solution. I modified it after creating the table separately:
CREATE procedure usp_testUserID
as
INSERT INTO dbo.ProcActivity (ProcName) SELECT OBJECT_NAME(@@PROCID) AS ProcName
GO
Is there any way to get the network userid? When I run this proc inside a job it gives the name of the stored procedure in ProcName (very nice), but it gives me dbo in UserName, just like when I run the proc in Query Analyzer. I was expecting the SQL Agent User ID. Undoubtedly this has to do with the way we set up things. Nevertheless, can we access the real user ID?
November 22, 2006 at 12:44 am
The only way that i know(still now) is execute xp_cmdshell 'net name' and insert the data in a temp table, then get the user name from that table..
Hope there will be some other effective way..
November 22, 2006 at 6:46 am
What about SYSTEM_USER or HOST_NAME() ?
November 22, 2006 at 7:13 am
What authentication mode are you using?
What login are you using in the connection string??
I use the user_name method and it doesn't give dbo for all the users (for me only actually). All other users list the correct user names.
November 22, 2006 at 11:34 am
Thanks for y'alls reply.
Phil, I think SYSTEM_USER is what we were looking for. We'll know for sure when different users execute a stored proc and we log whodunnit with SYSTEM_USER.
Ninja, Windows Authentication is our standard here. Again, we may see that user_name will work when others that are not in the sysadmin group start to execute the proc. Currently when any of the members of the sysadmin group do a "SELECT USER_NAME", we get "dbo". We want to be able to get the network ID so that we can see who is really executing the proc.
Actually, this whole thing is for Disaster Recovery. We want to benchmark that certain queries can be run by certain users, then after the DRP restore, we want the same users to be able to execute those same queries.
November 22, 2006 at 11:42 am
Cool, good luck with the rest of that project!
November 22, 2006 at 4:48 pm
Carlos,
Had a somewhat similiar problem as yours. In my table I defined a column as 'User_Name' with a default value of (rtrim(suser_sname())) this way I did not have to add the code to my T-SQL statements. In modifying my stored procedures used:
IF EXISTS (select xtype from dbo.sysobjects WHERE Id = OBJECT_ID('PerformanceStats'))
Begin
..... here is where I put the code to insert into the table -
Did this so when I collected enough data all I had to do was to rename the table 'PerformanceStats' or delete the table. This was done just in case the extra work slowed down the database response. And of course it would save me from having to edit and delete the additional T-SQL from each and every stored procedure I had added it to.
suser_sname() - returned my login name even though I am a member of the DBO group.
November 27, 2006 at 8:23 am
Thanks, bitbucket! It's a simple solution and thanks for telling us about it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply