March 13, 2008 at 6:18 am
I understand perfectly how to set up an application role and that it has to be activated by running the sp_setapprole . but no where can I find how to set this up in the application .
All the books on line , msdn and random searches I have done for application role finish their helpful advice with
"An Application then activates the role by using sp_setapprole stored procedure"
But how does it do this ?
What have I got to set up to get the application to run this on start up?
If I want to set up my users to use query analyzer via an application role how do I link opening/connecting to sql server and exec sp_setapprole?
Apologies if I am being really thick here but I would really appreciate some advice or advanced application role training
March 13, 2008 at 6:59 am
QA is not one of the applications it's talking about. Query analyzer is already built, etc...Even if you preloaded the script needed to activate the app role, your users would have to run it manually once they connect to the server.
The application roles stuff is intended for applications being built from the ground up. It's a developer/designer tool (ultimately). It is intended to be used when someone is coding an application and doesn't want explicit rights or fixed roles as their security model.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 13, 2008 at 7:24 am
I have seldom used application roles because (1) they have to be activated on an ad-hoc basis and therefore cannot be set from stored procedures and (2) automatically setting the user to "guest" in any outside database. These two barriers have tended to keep "application roles" for me more in the realm of curiosity basically.
March 13, 2008 at 8:00 am
You can test it in QA by running SP_setapprole, but that's not how users use it.
Your users still need to have a login/user on SQL Server, albeit with no rights. They connect, and the application first runs sp_setapprole with the password. This changes their rights and then the application functions as any other app.
The main use is that you might want to prevent users from connecting with Excel, Access, etc. to SQL Server. If they have no rights to do anything, then they can't use those apps to extract/change data. With an approle, if the password is protected, they must use your application to connect.
Finally, I never have guest on any databases. You have access or you don't.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 13, 2008 at 8:16 am
Ahhh! well I am dissapointed.
So there is no way too restrict users using QA or BIDS while allowing them full access to their other databases via ?
March 13, 2008 at 5:40 pm
you'd have to set up some kind of automation wrapper around the app (or driving the app)....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 14, 2008 at 3:19 am
Thanks for that.
Dare I ask How?
I have tried using DTS to run the script and then open the Application but I am obviously missing a crucial stage here as both steps run but don't connect QA to the server using the application role.
I am more of a DBA than a Developer as you might have guessed by now.
Lizzy
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply