July 14, 2011 at 10:54 pm
Fellas, Here again. I created one Windows group G1 and put user U1 inside it. Created DB Login as well as DB user G1. Added it to newly created role R1 [which has db_datareader permissions only]. Now the user G1 cannot create any structure nor can update. However when i created one procedure from Admin account one each for select, insert, update and delete. And provided exec permissions to user G1, the user was able to exec each procedure without any issues. Also I created one new procedure inside which there was a temp table creation, even that also got executed without any issues. So my question is that no matter what if we provide a user just datareader properties and give grant access to any procedure [no matter what operation it is doing], that user will be able to execute it even if the procedure demands acticities more than that of datareader's boundaries ???? Hope my scenario is not confusing.
July 14, 2011 at 11:08 pm
Unless the proc is performing dynamic sql, and if the proc is in the dbo schema, yes.
This is twofold. First, for the dynamic SQL, the dynamic component will then execute under the original caller's security context.
Secondly is a property called inheritance. The DBO proc which is allowed to execute is then run as the DBO user from there on in, and has access to all the dbo schema objects.
This is a mainstay of proper SQL security, allowing execute permissions on procedures to logins that will do things the user cannot do directly.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 15, 2011 at 12:07 am
Thanks Craig. That clarifies all my doubts.
July 16, 2011 at 8:00 pm
sqlnaive (7/14/2011)
Fellas
I did not know that everyone on this forum was a fellow? :hehe:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 18, 2011 at 2:49 am
Welsh, Sorry but it was not meant to hurt you or anyone. It was just out of affection for all respected people here in this forum.
July 18, 2011 at 5:53 am
sqlnaive (7/18/2011)
Welsh, Sorry but it was not meant to hurt you or anyone. It was just out of affection for all respected people here in this forum.
I was just joking. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply