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

How to give triiger execution Permission Expand / Collapse
Author
Message
Posted Wednesday, June 20, 2012 11:42 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 25, 2014 3:28 AM
Points: 61, Visits: 401
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
Post #1319084
Posted Thursday, June 21, 2012 12:51 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
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??!

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.
Post #1319109
Posted Thursday, June 21, 2012 9:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
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
Post #1319381
Posted Friday, June 22, 2012 1:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 25, 2014 3:28 AM
Points: 61, Visits: 401
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
Post #1319791
Posted Friday, June 22, 2012 7:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 13,007, Visits: 12,421
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 Moden's 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)
Post #1319939
Posted Friday, June 22, 2012 7:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
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
Post #1319946
Posted Friday, June 22, 2012 8:17 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 25, 2014 3:28 AM
Points: 61, Visits: 401
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
Post #1319963
Posted Friday, June 22, 2012 8:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:44 PM
Points: 7,107, Visits: 12,661
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
Post #1320009
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse