InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 10:40:37 GMT20http://www.sqlservercentral.com/Forums/Topic471368-462-1.aspxAgreed - I don't think this was badloy worded, especially keeping in mind that wording a question so that the answer is essentially given to you defeats the purpose. Finally - a question posed with no stated assumptions (IMHO) should be reviewed with no stated assumptions. There's nothing in the wording pointing towards HOW it was invoked (with or without the default settings), so I'm not sure how you could jump there. As a matter of fact - making a "hidden assumption" would make the question substantially more unfair. Again - in my opinion...Wed, 19 Mar 2008 12:13:55 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic471368-462-1.aspxI don't think the question was poorly worded. The question was, in my opinion, written to make you think, what are the possible answers? How many ways can something be done? Using the statement, invoke the application role, how many ways can it be invoked? Based on that, how many ways can you revert back to the original context? This is elementary problem solving.I will agree, that there have been some questions that have been poorly written and/or actually had no correct answers. This isn't one of them, IMHO.:cool:Wed, 19 Mar 2008 11:59:33 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic471368-462-1.aspxI agree with Joshua. If a non-default setting was chosen when the role was set this should have been specified either in the question on in the answer by specifing that the cookie was set. This is another example of a poorly designed question.Wed, 19 Mar 2008 11:54:13 GMTMark D Powellhttp://www.sqlservercentral.com/Forums/Topic471368-462-1.aspxA database administrator's primary job is to protect the data. Making assumptions is a very good way to put the data at risk. Like Sgt. Joe Friday used to say in Dragnet, "All we want are the facts, ma'am". I deal in facts and data, not assumptions and generalities.Wed, 19 Mar 2008 11:49:00 GMTJoshua M Perryhttp://www.sqlservercentral.com/Forums/Topic471368-462-1.aspxThe question was vague, make some assumptions. Again, how would YOU invoke the application role to accomplish testing of the role? How many ways can you invoke the application role? Given the various ways it can be done, what ways are available to revert to the original context? Given all the available options that can be used, there are, therefore two ways to revert back; disconnect and reconnect and sp_unsetapprole.:cool:Wed, 19 Mar 2008 11:22:10 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic471368-462-1.aspxThe questions still says:[font="Courier New"]You are testing an application role in SQL Server 2005. You connect with SSMS, invoke the role, and then execute various queries and stored procedures. What can you do to return to your normal account permissions?[/font]If the questions said:[font="Courier New"]You are testing an application role in SQL Server 2005. You connect with SSMS, invoke the role [b]with the cookie option[/b], and then execute various queries and stored procedures. What can you do to return to your normal account permissions?[/font]then the answer would have been correct. As database administrators, we deal with many different application configurations. Saying that you can revert using sp_unsetapprole without first setting the cookie is like saying you can code against the CLR without first setting the CLR to enabled. You need to follow the proper steps for everything to work.If I were to tell someone that they can just run sp_setapprole and the run sp_unsetapprole to revert to their original context, I would have a very upset developer when he could not revert.Wed, 19 Mar 2008 10:36:37 GMTJoshua M Perryhttp://www.sqlservercentral.com/Forums/Topic471368-462-1.aspxYes, you invoke the approle is vague, but purposeful. Put yourself in the position asked. If you are testing an application role, how would you invoke it? Would you do it with or without using a cookie? I put myself in that position, guess what, I'm going to set it up so I can revert. It's vague, but if you think about it, the question is asking how can you get back to your original state. There are TWO ways, disconnect and reconnect or sp_unsetapprole. You, as the individual doing the testing get to setup how that happens.:cool:Wed, 19 Mar 2008 10:28:03 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic471368-462-1.aspxYou are correct that you must use sp_setapprole to invoke the app role, but if you do this without setting the cookie you [b]cannot revert[/b] since the cookie is what allows you to revert. Where does it say anything about setting the cookie option? That's why you can only reconnect to revert to the original context. If you don't believe me just try it yourself.Wed, 19 Mar 2008 10:23:17 GMTJoshua M Perryhttp://www.sqlservercentral.com/Forums/Topic471368-462-1.aspxJoshua - Read the whole question. It clearly states that you invoke the approle. How do you do that? You use sp_setapprole, therefore you are setting everything up to be able to revert using sp_unsetapprole.Please, tell me if there is ANY other way to invoke an application role with out using sp_setapprole, as I am not aware of it.:cool:Wed, 19 Mar 2008 10:20:26 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic471368-462-1.aspxAgain, setting the cookie is an option that is required to revert. By default this option is set to false. The question is about how to revert and the cookie is an important piece. Without the cookie you cannot revert to the original context.Wed, 19 Mar 2008 10:17:32 GMTJoshua M Perryhttp://www.sqlservercentral.com/Forums/Topic471368-462-1.aspxJoshua - from the question itself:[quote]You are testing an application role in SQL Server 2005. You connect with SSMS, [b]invoke the role[/b], and then execute various queries and stored procedures[/quote]You can only "invoke the role" with sp_setapprole. The question is worded just so that it doesn't steer you to the answer. No - it doesn't specifically mention sp_setapprole, but that's on purpose, and IMO - not incorrect at all.Besides - it covers both alternatives. if the cookie isn't set, then you have to disconnect and reconnect to get your permissions; if the cookie is set, then you can use sp_unsetapprole.Wed, 19 Mar 2008 10:13:58 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic471368-462-1.aspxJoshua,The issue is that the ”invoke the role” statement in the question is vague and I belive assumes that the @fCreateCookie and @cookie arguments were used.-KevinWed, 19 Mar 2008 10:13:11 GMTkevin.l.williamshttp://www.sqlservercentral.com/Forums/Topic471368-462-1.aspxI did, and here is the question again with emphasis added.[quote]"You are testing an application role in SQL Server 2005. You connect with SSMS, [b]invoke the role[/b], and then execute various queries and stored procedures. What can you do to return to your normal account permissions? (select all that apply)"[/quote]It clearly states that the approle is invoked, this means you had to use the sp_setapprole to invoke the role for testing. Maybe the question could have been clearer if written as such:[quote]"You are testing an application role in SQL Server 2005. You connect with SSMS, invoke the role [b]using sp_setapprole[/b], and then execute various queries and stored procedures. What can you do to return to your normal account permissions? (select all that apply)"[/quote]Sometimes you do have to read between the lines.:cool:Wed, 19 Mar 2008 10:12:33 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic471368-462-1.aspxThe question never says anything about actually using sp_setapprole to create the cookie, so you can't use sp_unsetapprole to revert. I think you need to read my post again. You have to specify the option to create the cookie to be able to revert.EXEC sp_setapprole 'user', 'password', @fCreateCookie = true, @cookie = @cookie OUTPUTEXEC sp_unsetapprole @cookieWed, 19 Mar 2008 10:06:54 GMTJoshua M Perryhttp://www.sqlservercentral.com/Forums/Topic471368-462-1.aspx[quote][b]Joshua Perry (3/19/2008)[/b][hr]The answer is absolutely wrong. You can't use sp_unsetapprole because the cookie was never set using sp_setapprole. If you activate the app role before setting the cookie using sp_setapprole, you can only get the original context by disconnecting and reconnecting. sp_unsetapprole only works if you first use sp_setapprole to create the cookie.[font="Courier New"]"You are testing an application role in SQL Server 2005. You connect with SSMS, invoke the role, and then execute various queries and stored procedures. What can you do to return to your normal account permissions? (select all that apply)"[/font][/quote]Actually, you invoke the role using sp_setapprole, so you can use sp_unsetapprole to revert to your original context. If there is another way to invoke an application role without using sp_setapprole, I'd like to know what it is.:cool:Wed, 19 Mar 2008 09:58:54 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic471368-462-1.aspxThe answer is absolutely wrong. You can't use sp_unsetapprole because the cookie was never set using sp_setapprole. If you activate the app role before setting the cookie using sp_setapprole, you can only get the original context by disconnecting and reconnecting. sp_unsetapprole only works if you first use sp_setapprole to create the cookie.[font="Courier New"]"You are testing an application role in SQL Server 2005. You connect with SSMS, invoke the role, and then execute various queries and stored procedures. What can you do to return to your normal account permissions? (select all that apply)"[/font]Wed, 19 Mar 2008 08:53:07 GMTJoshua M Perryhttp://www.sqlservercentral.com/Forums/Topic471368-462-1.aspxComments posted to this topic are about the item [B]<A HREF="/questions/Security/62254/">Application Roles</A>[/B]Tue, 18 Mar 2008 22:43:29 GMTSite Owners