August 13, 2013 at 7:19 am
Hi ,
Can someone please explain what is the purpose of execute permission? What is the drawback of granting execute? Does it cause lot of performance overhead or does it make the database/ table insecure ?
I added a trigger on one of the tables in sql server (trigger on insert). But the insert fails when i enter the details on the webpage and hit enter.. The error that i get is - execute permission denied.
I can get the execute permission but I want to access all the pitfalls before i go ahead with that.
August 13, 2013 at 7:43 am
kanchankalra85 (8/13/2013)
Hi ,Can someone please explain what is the purpose of execute permission? What is the drawback of granting execute? Does it cause lot of performance overhead or does it make the database/ table insecure ?
I added a trigger on one of the tables in sql server (trigger on insert). But the insert fails when i enter the details on the webpage and hit enter.. The error that i get is - execute permission denied.
I can get the execute permission but I want to access all the pitfalls before i go ahead with that.
Does your trigger call a stored proc or a scalar function?
The point of the execute permission is to allow a login to execute a stored proc or a scalar function. It is exactly the opposite of making your database insecure. It prevents a login from executing code that you don't them to.
http://technet.microsoft.com/en-us/library/ms188371.aspx
_______________________________________________________________
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/
August 13, 2013 at 7:57 am
kanchankalra85 (8/13/2013)
Hi ,Can someone please explain what is the purpose of execute permission? What is the drawback of granting execute? Does it cause lot of performance overhead or does it make the database/ table insecure ?
I added a trigger on one of the tables in sql server (trigger on insert). But the insert fails when i enter the details on the webpage and hit enter.. The error that i get is - execute permission denied.
I can get the execute permission but I want to access all the pitfalls before i go ahead with that.
Granting execute on the single SP\function is much better security wise than just providing DBO. Granting specific execute permission is the correct way to proceed
August 13, 2013 at 8:07 am
Thanks for the info Sean.
The trigger checks the count of records in the parent table. If the count is greater than a specified number; it sends an email to the concerned person.
Also, in this case, how/ what permission do i grant to the trigger ?
August 13, 2013 at 8:11 am
kanchankalra85 (8/13/2013)
Thanks for the info Sean.The trigger checks the count of records in the parent table. If the count is greater than a specified number; it sends an email to the concerned person.
Also, in this case, how/ what permission do i grant to the trigger ?
I assume by sending an email you mean you are using a proc for that? Keep in mind that you don't grant permission to a trigger, you grant permission to a user. Often, in a trigger you will use EXECUTE AS to avoid permission issues because any number of people might be causing the trigger to fire.
http://msdn.microsoft.com/en-us/library/ms181362.aspx
_______________________________________________________________
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/
August 13, 2013 at 9:01 am
Sean,
As of now, i have directly embedded the code to send the email to user in the trigger and am not calling a stored procedure..
Do you think it would be better to call a stored procedure inside the trigger to send the email.
It is something like this :
IF EXISTS
(SELECT COUNT(*) CNT FROM tableA
WHERE CREATE_TIMESTAMP < DATEADD(MINUTE, -15, CURRENT_TIMESTAMP))
GROUP BY COMPANY_ID HAVING COUNT(*) > 100)
BEGIN
EXEC msdb.dbo.sp_send_dbmail @profile_name='kktest',
@recipients='sample@gmail.com,
@subject='Test message',
@body='This is the body of the test message. _
count is greater than 100.'
END
And I got that EXECUTE AS part .. i tried adding WITH EXECUTE AS 'dbmailuser' in the alter trigger but i got an error :
Cannot execute as the user 'dbmailuser', because it doesnt not exist or you do not have permission.
August 13, 2013 at 9:13 am
kanchankalra85 (8/13/2013)
Sean,As of now, i have directly embedded the code to send the email to user in the trigger and am not calling a stored procedure..
Umm...
You are calling a stored proc. Not one you created, but a system one. Right there...you see it...the only line in your trigger.
--->>> EXEC msdb.dbo.sp_send_dbmail <<<---
And I got that EXECUTE AS part .. i tried adding WITH EXECUTE AS 'dbmailuser' in the alter trigger but i got an error :
Cannot execute as the user 'dbmailuser', because it doesnt not exist or you do not have permission.
That is most likely because that user doesn't exist. 😉
_______________________________________________________________
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/
August 13, 2013 at 9:27 am
Thanks a lot Sean.. That answers almost all the queries i had at this point of time.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply