June 20, 2012 at 11:42 pm
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
June 21, 2012 at 12:51 am
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:Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
June 21, 2012 at 9:06 am
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
June 22, 2012 at 1:17 am
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
June 22, 2012 at 7:41 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 22, 2012 at 7:49 am
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
June 22, 2012 at 8:17 am
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
June 22, 2012 at 8:55 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply