July 2, 2014 at 7:20 am
I would like to provide the db_datareader and db_executor role to a particular SQL Server Login in a database
But, I would like to avoid any INSERT's, UPDATE's or DELETE's that may happen by calling the stored procedures
I tried assigning the db_denydatawriter role but it doesn't seem to be doing the trick as the INSERT's, UPDATE's and DELETE's were still working
Is there any way to provide the db_datareader and db_executor role but avoid any DML actions.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 2, 2014 at 7:37 am
Kingston Dhasian (7/2/2014)
I would like to provide the db_datareader and db_executor role to a particular SQL Server Login in a databaseBut, I would like to avoid any INSERT's, UPDATE's or DELETE's that may happen by calling the stored procedures
I tried assigning the db_denydatawriter role but it doesn't seem to be doing the trick as the INSERT's, UPDATE's and DELETE's were still working
Is there any way to provide the db_datareader and db_executor role but avoid any DML actions.
I am a bit confused here. You want this person to be able to execute a stored proc that modifies data but you don't want that person to be able to execute the commands in the stored proc? What do you expect the outcome to be?
_______________________________________________________________
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/
July 2, 2014 at 7:40 am
Sean Lange (7/2/2014)
Kingston Dhasian (7/2/2014)
I would like to provide the db_datareader and db_executor role to a particular SQL Server Login in a databaseBut, I would like to avoid any INSERT's, UPDATE's or DELETE's that may happen by calling the stored procedures
I tried assigning the db_denydatawriter role but it doesn't seem to be doing the trick as the INSERT's, UPDATE's and DELETE's were still working
Is there any way to provide the db_datareader and db_executor role but avoid any DML actions.
I am a bit confused here. You want this person to be able to execute a stored proc that modifies data but you don't want that person to be able to execute the commands in the stored proc? What do you expect the outcome to be?
I want the person to be able to execute a stored procedure that doesnot modify data.
If the stored procedure modifies data, the person should not be able to execute it.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 2, 2014 at 7:49 am
Kingston Dhasian (7/2/2014)
Sean Lange (7/2/2014)
Kingston Dhasian (7/2/2014)
I would like to provide the db_datareader and db_executor role to a particular SQL Server Login in a databaseBut, I would like to avoid any INSERT's, UPDATE's or DELETE's that may happen by calling the stored procedures
I tried assigning the db_denydatawriter role but it doesn't seem to be doing the trick as the INSERT's, UPDATE's and DELETE's were still working
Is there any way to provide the db_datareader and db_executor role but avoid any DML actions.
I am a bit confused here. You want this person to be able to execute a stored proc that modifies data but you don't want that person to be able to execute the commands in the stored proc? What do you expect the outcome to be?
I want the person to be able to execute a stored procedure that doesnot modify data.
If the stored procedure modifies data, the person should not be able to execute it.
It doesn't quite work like that. You could deny them permission on procedures you don't want them to execute but sql is not going to evaluate the code to determine if there is dml inside or not. That is kind of the point of a stored procedure. You can allow a user to perform some actions that they otherwise would not be able to do.
_______________________________________________________________
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/
July 2, 2014 at 8:02 am
Sean Lange (7/2/2014)
Kingston Dhasian (7/2/2014)
Sean Lange (7/2/2014)
Kingston Dhasian (7/2/2014)
I would like to provide the db_datareader and db_executor role to a particular SQL Server Login in a databaseBut, I would like to avoid any INSERT's, UPDATE's or DELETE's that may happen by calling the stored procedures
I tried assigning the db_denydatawriter role but it doesn't seem to be doing the trick as the INSERT's, UPDATE's and DELETE's were still working
Is there any way to provide the db_datareader and db_executor role but avoid any DML actions.
I am a bit confused here. You want this person to be able to execute a stored proc that modifies data but you don't want that person to be able to execute the commands in the stored proc? What do you expect the outcome to be?
I want the person to be able to execute a stored procedure that doesnot modify data.
If the stored procedure modifies data, the person should not be able to execute it.
It doesn't quite work like that. You could deny them permission on procedures you don't want them to execute but sql is not going to evaluate the code to determine if there is dml inside or not. That is kind of the point of a stored procedure. You can allow a user to perform some actions that they otherwise would not be able to do.
I understand that SQL Server will not evaluate the code to determine if there is dml inside or not. But, I tried assigning the db_denydatawriter[/i] role to the login. Isn't the db_denydatawriter supposed to take precedence over the db_executor role? I expected some sort of error that would stop the person from executing the particular stored procedure.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 2, 2014 at 8:06 am
Kingston Dhasian (7/2/2014)
Sean Lange (7/2/2014)
Kingston Dhasian (7/2/2014)
Sean Lange (7/2/2014)
Kingston Dhasian (7/2/2014)
I would like to provide the db_datareader and db_executor role to a particular SQL Server Login in a databaseBut, I would like to avoid any INSERT's, UPDATE's or DELETE's that may happen by calling the stored procedures
I tried assigning the db_denydatawriter role but it doesn't seem to be doing the trick as the INSERT's, UPDATE's and DELETE's were still working
Is there any way to provide the db_datareader and db_executor role but avoid any DML actions.
I am a bit confused here. You want this person to be able to execute a stored proc that modifies data but you don't want that person to be able to execute the commands in the stored proc? What do you expect the outcome to be?
I want the person to be able to execute a stored procedure that doesnot modify data.
If the stored procedure modifies data, the person should not be able to execute it.
It doesn't quite work like that. You could deny them permission on procedures you don't want them to execute but sql is not going to evaluate the code to determine if there is dml inside or not. That is kind of the point of a stored procedure. You can allow a user to perform some actions that they otherwise would not be able to do.
I understand that SQL Server will not evaluate the code to determine if there is dml inside or not. But, I tried assigning the db_denydatawriter[/i] role to the login. Isn't the db_denydatawriter supposed to take precedence over the db_executor role? I expected some sort of error that would stop the person from executing the particular stored procedure.
No that is the point I was making in my last post. Allowing them execute permission on a stored procedure will allow them to do some things they normally can't do. The permission check is "does this person have execute permission for this procedure?". It does not check each and every statement inside the procedure when they are executed.
_______________________________________________________________
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/
July 2, 2014 at 8:54 am
Sean Lange (7/2/2014)
Kingston Dhasian (7/2/2014)
Sean Lange (7/2/2014)
Kingston Dhasian (7/2/2014)
Sean Lange (7/2/2014)
Kingston Dhasian (7/2/2014)
I would like to provide the db_datareader and db_executor role to a particular SQL Server Login in a databaseBut, I would like to avoid any INSERT's, UPDATE's or DELETE's that may happen by calling the stored procedures
I tried assigning the db_denydatawriter role but it doesn't seem to be doing the trick as the INSERT's, UPDATE's and DELETE's were still working
Is there any way to provide the db_datareader and db_executor role but avoid any DML actions.
I am a bit confused here. You want this person to be able to execute a stored proc that modifies data but you don't want that person to be able to execute the commands in the stored proc? What do you expect the outcome to be?
I want the person to be able to execute a stored procedure that doesnot modify data.
If the stored procedure modifies data, the person should not be able to execute it.
It doesn't quite work like that. You could deny them permission on procedures you don't want them to execute but sql is not going to evaluate the code to determine if there is dml inside or not. That is kind of the point of a stored procedure. You can allow a user to perform some actions that they otherwise would not be able to do.
I understand that SQL Server will not evaluate the code to determine if there is dml inside or not. But, I tried assigning the db_denydatawriter[/i] role to the login. Isn't the db_denydatawriter supposed to take precedence over the db_executor role? I expected some sort of error that would stop the person from executing the particular stored procedure.
No that is the point I was making in my last post. Allowing them execute permission on a stored procedure will allow them to do some things they normally can't do. The permission check is "does this person have execute permission for this procedure?". It does not check each and every statement inside the procedure when they are executed.
Oh. OK. I now understand better. I will handle this on procedure level as you mentioned. Thanks a lot for the clarification.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 2, 2014 at 10:49 am
No problem. Glad that helped. 😀
_______________________________________________________________
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/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply