August 30, 2004 at 2:34 pm
I have an Issue with Permissions on a table, somehow changes are being made(we've tried by locking out all users from modify abilities), most likely changes are from within a stored procedure. Is there anyway to determine on a given table a way to know who is requesting the operation (ie. insert, update, Delete).
Thanks
August 31, 2004 at 5:33 am
You could set up a trigger to output details to a table of whose process is running although not the specific stored procedure or application. The following from BOL :
This example returns the process ID, login name, and user name for the current user process.
SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'
August 31, 2004 at 6:31 pm
Actually, you can capture what proc the spid is running:
DBCC INPUTBUFFER(@@spid)
put that in a trigger and you should be able to figure this out. Don't the "Dependencies" show up on Enterprise Manager.
cl
Signature is NULL
September 1, 2004 at 8:20 pm
Try this SQL will give you ALL tables and what SPs access the table
SELECT so.name, sob.name as 'Stored Procedure name' FROM sysobjects so LEFT OUTER JOIN (sysobjects sob left outer join sysdepends on sob.id = sysdepends.id) on sysdepends.depid = so.id WHERE so.xtype = 'u' AND sob.xtype = 'p'AND NOT so.name = 'dtproperties' GROUP BY so.name, sob.name ORDER BY so.name
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply