Wednesday, March 7, 2012

Application Role Usage

I am new to the security part of SQL server and I'm having a problem with
using the application role while running a query from Excel. I wrote the sp
I'm using and it works fine but I can't get the application role to work. I
have set it up in EM but I'm not sure how to put it in the code in Microsoft
query. Do I have to put the exec sp_setapprole 'role', 'password' statement
in the sp or before I execute the procedure. I tried putting it before and i
t
did not work, maybe I had the wrong syntax or something. Below is what I
wrote:
Exec sp_setapprole 'role', 'password'
GO
Exec GetDefectReport
GOThis should work I think...do you get an error message?
Did you give the approle execute permission on the SP (maybe underlying
tables?)
Does the sp return data for Excel to display (I take it, that's what it has
to do...)?
Lee-Z
"A.B." <AB@.discussions.microsoft.com> wrote in message
news:4C25B4B8-FEC4-49C4-9276-F5B0AF2A6FD1@.microsoft.com...
>I am new to the security part of SQL server and I'm having a problem with
> using the application role while running a query from Excel. I wrote the
> sp
> I'm using and it works fine but I can't get the application role to work.
> I
> have set it up in EM but I'm not sure how to put it in the code in
> Microsoft
> query. Do I have to put the exec sp_setapprole 'role', 'password'
> statement
> in the sp or before I execute the procedure. I tried putting it before and
> it
> did not work, maybe I had the wrong syntax or something. Below is what I
> wrote:
> Exec sp_setapprole 'role', 'password'
> GO
> Exec GetDefectReport
> GO|||The error message is that the syntax is wrong around GO. I wrote the sp in
the query analyzer and now i am calling it from Excel using Microsoft Query.
"Lee-Z" wrote:

> This should work I think...do you get an error message?
> Did you give the approle execute permission on the SP (maybe underlying
> tables?)
> Does the sp return data for Excel to display (I take it, that's what it ha
s
> to do...)?
> Lee-Z
>
> "A.B." <AB@.discussions.microsoft.com> wrote in message
> news:4C25B4B8-FEC4-49C4-9276-F5B0AF2A6FD1@.microsoft.com...
>
>|||have never tried approle with MS Query, but try to execute the sp_SetAppRole
statement in menu "File"-> "Execute SQL" from MS-Query (leave out the GO
part). Make sure you select your database in the dropdown box.
After that you should be able to get data from your original query (leave
out the "GO" here as well)...
good luck
Lee-Z
"A.B." <AB@.discussions.microsoft.com> wrote in message
news:1FDE810C-6F0F-4E7C-8537-E5987C88D297@.microsoft.com...[vbcol=seagreen]
> The error message is that the syntax is wrong around GO. I wrote the sp in
> the query analyzer and now i am calling it from Excel using Microsoft
> Query.
> "Lee-Z" wrote:
>|||You are not going to be able to use Microsoft Query and get
application roles to perform reliably. Application roles are
connection-specific, and the tools open additonal connections under
the covers in order to speed up connections. This is an issue even if
you code data access in ADO code (which is the usual way to go about
it). The following article, "SQL application role errors with OLE DB
resource pooling" describes the problem and the workaround:
http://support.microsoft.com/defaul...b;en-us;Q229564
--Mary
On Fri, 12 Aug 2005 06:59:20 -0700, "A.B."
<AB@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The error message is that the syntax is wrong around GO. I wrote the sp in
>the query analyzer and now i am calling it from Excel using Microsoft Query
.
>"Lee-Z" wrote:
>|||Thanks for the help Lee and Mary
"Mary Chipman [MSFT]" wrote:

> You are not going to be able to use Microsoft Query and get
> application roles to perform reliably. Application roles are
> connection-specific, and the tools open additonal connections under
> the covers in order to speed up connections. This is an issue even if
> you code data access in ADO code (which is the usual way to go about
> it). The following article, "SQL application role errors with OLE DB
> resource pooling" describes the problem and the workaround:
> http://support.microsoft.com/defaul...b;en-us;Q229564
> --Mary
> On Fri, 12 Aug 2005 06:59:20 -0700, "A.B."
> <AB@.discussions.microsoft.com> wrote:
>
>

No comments:

Post a Comment