Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Trigger permissions Expand / Collapse
Author
Message
Posted Friday, August 3, 2012 2:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:44 AM
Points: 5, Visits: 61
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?
Post #1339693
Posted Friday, August 3, 2012 9:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:16 PM
Points: 31,284, Visits: 15,748
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/)








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1339902
Posted Friday, August 3, 2012 9:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:44 AM
Points: 5, Visits: 61
Thanks for you reply.
A trace is impractible because this problem has only occured twice in three months.
Post #1339909
Posted Friday, August 3, 2012 9:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:16 PM
Points: 31,284, Visits: 15,748
In that case, you could grant permissions to the login to get to the DMVs, but I wouldn't leave them there permanently.








Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1339926
Posted Tuesday, August 7, 2012 2:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:44 AM
Points: 5, Visits: 61
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
Post #1341064
Posted Tuesday, August 7, 2012 4:10 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 4, 2014 12:10 AM
Points: 18, Visits: 162
How you are authenticating from application.
SQL Server Authentication or Windows Authentication ?
Post #1341122
Posted Tuesday, August 7, 2012 4:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:44 AM
Points: 5, Visits: 61
SQL Server Authentication
Post #1341149
Posted Tuesday, August 7, 2012 5:54 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 4, 2014 12:10 AM
Points: 18, Visits: 162
Check with the permission granted to user throwing the error.
Requires VIEW SERVER STATE permission on the server
Post #1341176
Posted Tuesday, August 7, 2012 6:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:44 AM
Points: 5, Visits: 61
Thank you
I have already tried this but it doesn't solve the permissions problem unfortunately.
Post #1341210
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse