SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to give triiger execution Permission


How to give triiger execution Permission

Author
Message
vs.satheesh
vs.satheesh
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 644
Hi All

I create a Role and Users in sql server following manner.


Step 1 :

I created a role name call "DEVELOER"

Step 2:

Under this role I created a two users.I given Select update and delete insert permission this "Develper" role.


Step 3:

I given Stored Procedure Exection and Creaton Permission also..

Above point all the condition working fine..


Buty my question is in this user following condition not woking


1) DML and DDL Trigger is not woking.Please Give me a command?


Thank You
derek.colley
derek.colley
SSC Eights!
SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)SSC Eights! (866 reputation)

Group: General Forum Members
Points: 866 Visits: 603
Look at what the triggers are doing. If they are executing SPs or ad-hoc SQL code using commands for which you haven't given permissions, the triggers will fail.

Please post the trigger definition code for more assistance.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!Crazy

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14839 Visits: 14396
The exception message should help track down the problem. Is it something like 'execute permission denied' or 'insert denied on...'? Also, are you using any system stored procedures (e.g. xp_cmdshell) or doing any dynamic SQL (e.g. using EXEC() or sp_executesql) in your trigger? If you post the exception message and trigger code we may be able to provide additional guidance.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
vs.satheesh
vs.satheesh
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 644
I am using follwing trigger


GO
/****** Object: Trigger [dbo].[Audit_updateAuthor_Locking_Details] Script Date: 06/22/2012 12:43:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter Trigger [dbo].[Audit_updateAuthor_Locking_Details] ON [dbo].[Author_Locking_Details] FOR UPDATE AS
BEGIN
SET NOCOUNT ON
DECLARE @IP varchar(20)
SELECT @IP=client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID
INSERT into AUDIT_Author_Locking_Details SELECT 'Updated','Old',getdate(),user_name(), @IP,* FROM Deleted
END
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25881 Visits: 17510
But what is the error message?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14839 Visits: 14396
OK, I am thinking you are seeing this error:

Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.


By default, users cannot select from sys.dm_exec_connections. You would need to grant your users VIEW SERVER STATE in order to use the DMV, which I would not recommend. You have the option of signing your trigger with a certificate. It is not a trivial process the first time you do it, but after you do it once it's a piece of cake to understand and maintain. Here is the tutorial I would recommend following:

http://msdn.microsoft.com/en-us/library/bb283630(v=sql.90).aspx

Do not worry that it says "Stored Procedure", I am pretty sure you can sign triggers as well in the exact name manner. I have used certs to elevate a database user's permission level but never to grant server-level perms. The documentation says it is possible. From the article:

You can create a certificate in the master database to allow server-level permissions


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
vs.satheesh
vs.satheesh
SSC-Enthusiastic
SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)SSC-Enthusiastic (187 reputation)

Group: General Forum Members
Points: 187 Visits: 644
Thank You . I am getting same error. but time being i am solved to change the server STATE . but i will do that certificate .


Any Problem in SERVER STATE changes?



One more thing i am new DBA in my concern.I want your mail id . if i will face any problem like this i will ask clarification . My mail id is vs.satheesh@gmail.com.


Once again Thank You
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14839 Visits: 14396
vs.satheesh (6/22/2012)
Thank You . I am getting same error. but time being i am solved to change the server STATE . but i will do that certificate .

Any Problem in SERVER STATE changes?

With this permission it is possible for users to run resource-intense DMFs which could negatively impact server performance, e.g.

SELECT  *
FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'detailed');



So, think carefully before granting this permission to all users.

One more thing i am new DBA in my concern.I want your mail id . if i will face any problem like this i will ask clarification . My mail id is vs.satheesh@gmail.com.

Feel free to send me a Personal Message using the PM link in my posts. If I have time, I will try to answer your queries, but I think you will have a much broader audience if you continued to post questions in the appropriate forums on this site. If you are looking for assistance with specific project work also feel free to send me a PM, and we can work out a fair rate.

Once again Thank You

You're welcome :-)

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search