How to give triiger execution Permission

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Security » How to give triiger execution Permission

How to give triiger execution Permission

Rate Topic

Display Mode

Topic Options

Author

Message

vs.satheesh

Posted Wednesday, June 20, 2012 11:42 PM

Valued Member

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:47 AM
Points: 53, Visits: 296

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

derek.colley

Posted Thursday, June 21, 2012 12:51 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:19 AM
Points: 496, Visits: 584

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.

Post #1319109

opc.three

Posted Thursday, June 21, 2012 9:06 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 6,722, Visits: 11,765

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1319381

vs.satheesh

Posted Friday, June 22, 2012 1:17 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:47 AM
Points: 53, Visits: 296

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

Sean Lange

Posted Friday, June 22, 2012 7:41 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:15 PM
Points: 8,618, Visits: 8,259

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

Post #1319939

opc.three

Posted Friday, June 22, 2012 7:49 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 6,722, Visits: 11,765

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1319946

vs.satheesh

Posted Friday, June 22, 2012 8:17 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:47 AM
Points: 53, Visits: 296

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

opc.three

Posted Friday, June 22, 2012 8:55 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 6,722, Visits: 11,765

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 Smile

Post #1320009

« Prev Topic | Next Topic »

Permissions