August 3, 2012 at 2:55 am
A customer's database loses data from one specific table when a particular user logs on to the Application. I have created a trigger on that table to log various bits of information as How, when why etc. The trigger includes this
DECLARE @LASTCOMMAND NVARCHAR(max)
--get he last command by the current spid:
SELECT @LASTCOMMAND = DEST.TEXT
FROM sys.[dm_exec_connections] SDEC
CROSS APPLY sys.[dm_exec_sql_text](SDEC.[most_recent_sql_handle])
AS DEST
WHERE SDEC.[most_recent_session_id] = @@SPID[/size]
This work ok when the table is updated from Management Studio, but fails when the table is updated via the application with a message about not having permissions to do this. Can anyone advise on permisions please?
August 3, 2012 at 9:02 am
The permissions are those of the user executing the code, so whoever updates the table has their permissions used.
I doubt most users have access to sys.dm_exec_connections. I would suggest you use a trace instead of a trigger to trap what is happening. You can set up a server side trace to get this.
Depending on what you are looking for, you could also use the default trace to watch activity (http://www.sqlservercentral.com/articles/64547/)
August 3, 2012 at 9:08 am
Thanks for you reply.
A trace is impractible because this problem has only occured twice in three months.
August 3, 2012 at 9:33 am
In that case, you could grant permissions to the login to get to the DMVs, but I wouldn't leave them there permanently.
August 7, 2012 at 2:31 am
I am having problems granting permissions when the trigger is fired via the application software. The application is multi user and any user could cause the trigger to fire. I have set permisions for the master login that the application uses to no avail
August 7, 2012 at 4:10 am
How you are authenticating from application.
SQL Server Authentication or Windows Authentication ?
August 7, 2012 at 4:58 am
SQL Server Authentication
August 7, 2012 at 5:54 am
Check with the permission granted to user throwing the error.
Requires VIEW SERVER STATE permission on the server
August 7, 2012 at 6:54 am
Thank you
I have already tried this but it doesn't solve the permissions problem unfortunately.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy