Application Role - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Application Role

Author

Topic

RobVG
Starting Member

42 Posts

Posted - 2004-07-05 : 16:13:52

It seems you guys have little patients when dealing with approle questions so I'll try to keep this brief.

Can you use an approle directly from Excel? You can connect with "Get External Data" and use the public role(?) but I don't see how you could execute the "sp_setapprole" from Excel?

I'm starting to think when BOL mentions using an approle with Excel, they mean using a custom app that uses Excel- to make the connection.
Is this even close?

RobVG

RobVG
Starting Member

42 Posts

Posted - 2004-07-06 : 13:08:07

Aha! I knew it. Nobody knows how to use an application role. ;)

People have tried.

This guy thought that Excel (MS query) shouldn't be part of the public role:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=3928&SearchTerms=application,role
Looks to me like it is- and should be.

And this guy used the ADO command object to pass the Exec sp_setapprole to the db.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=8431&SearchTerms=application,role
Nifty but it didn't work for him either.(wonder what db he connected to?)

You guys even come up with (cool) workarounds to get past using application roles. Check out what the "The Chadinator" had to say:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12138&SearchTerms=application,role

And now maybe w'ere getting down to the nitty gritty.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11986&SearchTerms=application,role

From AdjarnMark:

quote:
If everybody using your application is going to have the same database permissions, then why bother with an AppRole? Why not just create a SQL User that has the permissions you want them to have, and have your application connect with that User ID? It seems that would also get around the concerns that "Jeffrey" raised in the article mentioned above.

From nr:

quote:
I wonder if you really want to use an apllication role though.
You are basically saying that anyone who can load the application can use it.
You might want to consider mapping all the users nt accounts to a role and using nt security if you have an nt environment.

Now back to my question, how do you execute sp_setapprole from Excel....I just don't care anymore. Hopefully there will only be a few questions on the test.

Have a nice day

RobVG