Activation not working

Hi There

Everything works 100% until i activate my sp.

I specify to execute as 'dbo' in the alter queue statement. I also define the activated sp to execute as 'dbo'.

But i keep getting permission errors from my activated sp. i have tried to excute as 'sa' , i have even tried to write a job that excutes to stored procedure but it also get weird errors. Bottom line if i exceute the sp in management studio logged in as sa it works , but thorugh activation or a job nothing works, as 'dbo' or 'sa'.

?

Thanx

Hi there.

It seems the statement in my sp that has a permission problem is the get conversation group sattement.

So my activation on the queue does not work when i stipulate execute as 'dbo' or OWNER. I cannot assign execute as 'sa' i get the following error "The server principal "sa" is not able to access the database "OLTP1PRO" under the current security context". I have tried not to stipulate the execute as so taht it defaults to a sysadmin.

But i always get this error:

The user does not have permission to perform this action.

So i tried the following.

CREATE LOGIN QueueReader with password = 'T#ST123'

CREATE USER QueueReader for login QueueReader

grant receive on [ewx.co.za/Queue/ewx_sb_central_queue] to QueueReader

grant execute on dbo.CENTRAL_Queue_Processor to QueueReader

And chanegd the execute as to QueueReader, but is till get the same error.

I dunno what else to try, no matter hwat i do i cannot execute the get conversation group statement ?

Please help ?

|||anyone?|||

What error do you get if you manually invoke the procedure, from Management Studio, under the QueueReader user context?

executeasuser'QueueReader';

go

exec dbo.CENTRAL_Queue_Processor;

go

revert;

go

|||

Hi Remus

I get the same error as before. The user does not have permission to perform this action. Error number 297.

I have done more investigation, judging by the erro line number line it seems that the QueueReader user cannot read from

sys.dm_broker_activated_tasks.

However i am expecting these errors, the activated stored procedure has many inserts etc. SO basically i want the activated sp to run as dbo, i do not want to have to setup alot of permission for the user.

My biggest question is why does EXECUTE as 'dbo' or OWNER not work ? How can i get permission errors when it executes as dbo ?

So i make QueueReader dbo of the database and i still get the user doesnot have permission error, so it seems only sysadmin can select from the sys. views, however i am still stumped , since the dbo of the database is a sysadmin. If i login as the user who is the dbo i can execute it. But if i say execute as dbo i get the permission error, i am totally stumped ?

Thanx

|||

Ok so it seems that if i reference sis.dm views in my activated sp i have to make the execute as user a sysadmin, or grant that user select on the sys.dm views , is this correct?

However i am still interested to know why execute as dbo does nto work when the dbo is also a sysadmin?

Thanx Remus you rfeedabck will be greatly appreciated.

|||

Hi Remus

Lastly, it also mentions in Roger Wolters book that if you do not stipulate execute as for your activated sp it will execute in the backgrouond as a sysadmin. However if i do not stipulate execute as i still get the permission error ? So not sure what is happening there?

Thanx

|||

Execute as user is restricted to database context. sysadmin is a server context concept. An untrusted database cannot extend the context to the server. This topic is covered in my blog in these posts: http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx, http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx and http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

The simplest solution is to mark the database as trustworthy.

HTH,
~ Remus

|||

Thanx Remus

The links were worth reading and the trustworthy worked.

Just as a matter of interrest , what is the best practice in a prouction environment when the activation proc requires sys.dm views. Do you make the queue reader user a sysadmin (must be a bad idea), or make the dbo of the database a sysadmin, or simply grant the user permission to the sys.dm views (probably best) ? In gerneral when the actvation proc must use these views what would you reccomend ?

|||

The safest option is code signing (as in the exaple in my link). Getting code siging righ is really hard, so if you can get away with trustworthy bit, is much more easier. sysadmin is not a requirement, the appropiate priviledges needed to be granted are AUTHENTICATE SERVER to the database dbo and VIEW SERVER STATE to the execute as user.

HTH,
~ Remus

|||

HI Remus

Great thanx , i thnk i will stick to trustworthy and the permission you specified, thanx very much

Activation not working

Hi There

Everything works 100% until i activate my sp.

I specify to execute as 'dbo' in the alter queue statement. I also define the activated sp to execute as 'dbo'.

But i keep getting permission errors from my activated sp. i have tried to excute as 'sa' , i have even tried to write a job that excutes to stored procedure but it also get weird errors. Bottom line if i exceute the sp in management studio logged in as sa it works , but thorugh activation or a job nothing works, as 'dbo' or 'sa'.

?

Thanx

Hi there.

It seems the statement in my sp that has a permission problem is the get conversation group sattement.

So my activation on the queue does not work when i stipulate execute as 'dbo' or OWNER. I cannot assign execute as 'sa' i get the following error "The server principal "sa" is not able to access the database "OLTP1PRO" under the current security context". I have tried not to stipulate the execute as so taht it defaults to a sysadmin.

But i always get this error:

The user does not have permission to perform this action.

So i tried the following.

CREATE LOGIN QueueReader with password = 'T#ST123'

CREATE USER QueueReader for login QueueReader

grant receive on [ewx.co.za/Queue/ewx_sb_central_queue] to QueueReader

grant execute on dbo.CENTRAL_Queue_Processor to QueueReader

And chanegd the execute as to QueueReader, but is till get the same error.

I dunno what else to try, no matter hwat i do i cannot execute the get conversation group statement ?

Please help ?

|||anyone?|||

What error do you get if you manually invoke the procedure, from Management Studio, under the QueueReader user context?

execute as user 'QueueReader';

go

exec dbo.CENTRAL_Queue_Processor;

go

revert;

go

|||

Hi Remus

I get the same error as before. The user does not have permission to perform this action. Error number 297.

I have done more investigation, judging by the erro line number line it seems that the QueueReader user cannot read from

sys.dm_broker_activated_tasks.

However i am expecting these errors, the activated stored procedure has many inserts etc. SO basically i want the activated sp to run as dbo, i do not want to have to setup alot of permission for the user.

My biggest question is why does EXECUTE as 'dbo' or OWNER not work ? How can i get permission errors when it executes as dbo ?

So i make QueueReader dbo of the database and i still get the user doesnot have permission error, so it seems only sysadmin can select from the sys. views, however i am still stumped , since the dbo of the database is a sysadmin. If i login as the user who is the dbo i can execute it. But if i say execute as dbo i get the permission error, i am totally stumped ?

Thanx

|||

Ok so it seems that if i reference sis.dm views in my activated sp i have to make the execute as user a sysadmin, or grant that user select on the sys.dm views , is this correct?

However i am still interested to know why execute as dbo does nto work when the dbo is also a sysadmin?

Thanx Remus you rfeedabck will be greatly appreciated.

|||

Hi Remus

Lastly, it also mentions in Roger Wolters book that if you do not stipulate execute as for your activated sp it will execute in the backgrouond as a sysadmin. However if i do not stipulate execute as i still get the permission error ? So not sure what is happening there?

Thanx

|||

Execute as user is restricted to database context. sysadmin is a server context concept. An untrusted database cannot extend the context to the server. This topic is covered in my blog in these posts: http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx, http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx and http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

The simplest solution is to mark the database as trustworthy.

HTH,
~ Remus

|||

Thanx Remus

The links were worth reading and the trustworthy worked.

Just as a matter of interrest , what is the best practice in a prouction environment when the activation proc requires sys.dm views. Do you make the queue reader user a sysadmin (must be a bad idea), or make the dbo of the database a sysadmin, or simply grant the user permission to the sys.dm views (probably best) ? In gerneral when the actvation proc must use these views what would you reccomend ?

|||

The safest option is code signing (as in the exaple in my link). Getting code siging righ is really hard, so if you can get away with trustworthy bit, is much more easier. sysadmin is not a requirement, the appropiate priviledges needed to be granted are AUTHENTICATE SERVER to the database dbo and VIEW SERVER STATE to the execute as user.

HTH,
~ Remus

|||

HI Remus

Great thanx , i thnk i will stick to trustworthy and the permission you specified, thanx very much

Accessing Report Parameters Class through Web Service

I am using ASP .NET along to render my reports. I've extended the web
service class such that I specify the report folder, and I get all
reports and their respective parameters. I then dyncamically create
the controls (such as datepicker, textbox, etc...) based on the
Parameter Name, and Type. The drop down list is the one giving me the
hardest time though.
My problem is this: I have a Report with 3 parameters. The first 2
are dates. I can retrive all info on them just fine. The name,
prompt, and type (datetime). The third parameter (AppUserKey) however
is a dropdown list of all the users. I wrote a stored procedure that
does this called spGetAppUsers. Here is the structure of this
parameter as per the RDL file:
</ReportParameters>
...
...
<ReportParameter Name="AppUserKey">
<DataType>Integer</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>spGetAppUsers</DataSetName>
<ValueField>AppUserKey</ValueField>
</DataSetReference>
</DefaultValue>
<Prompt>AppUserKey</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>spGetAppUsers</DataSetName>
<ValueField>AppUserKey</ValueField>
<LabelField>AppUserName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
I can get the Name, and even from the ValidValues, the ValueField and
LabelField, however, I want the <DataSetName> entry.
This is so that after I dynamically create the drop down list, I'm
able to populate it via the stored procedure. Recall that the
ReportParameter class is defined here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_ir_5zec.asp
ReportParameter::QueryParameter is a boolean property. Doesn't
actually return the name. Just whether or not you are using a query.
ReportParameter::DefaultValues is a string but is empty, since the
report hasn't executed the stored procedure (makes sense).
I want this to be a generic solution. Hence all this trouble to get
all the "givens" from the Report, and to reconstruct it in ASP .NET.
I need that DataSetName!!! :-S
Any help is appreciated.
Thanks for your time,
RoyThere are 2 ways to do what I want:
1. Instead of trying to get the stored procedure of my query based
parameter, I should've set forRendering = True in my
GetReportParameters function. That will actually return the query
based parameter set. Then I can populate the drop down list with that.
2. Load the .RDL (report definition in XML) in memory and use XPATH to
get the name of the parameter:
Dim reportDefinition As Byte() = Nothing
Dim doc As New System.Xml.XmlDocument
Try
reportDefinition = rs.GetReportDefinition(ReportsFolder &
strReportName)
Dim stream As New MemoryStream(reportDefinition)
doc.Load(stream)
In this scenario, assuming you have forRedering=True or False, you
still need to know the name of the parameter. So you'ld probably get
then name of the parameter and then check the value of QueryParameter
to see if it's true.
Then base your XPATH query on the following XML:
"/ReportParameter/ValidValues/DataSetReference/DataSetName[@.text]"
Or something similar. I forget. The above is proabably wrong and
could be written in a million different ways.
<ReportParameter Name="AppUserKey">
<DataType>Integer</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>spGetAppUsers</DataSetName>
<ValueField>AppUserKey</ValueField>
</DataSetReference>
</DefaultValue>
<Prompt>AppUserKey</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>spGetAppUsers</DataSetName>
<ValueField>AppUserKey</ValueField>
<LabelField>AppUserName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
</ReportParameters>
Roy Assaly wrote:
> I am using ASP .NET along to render my reports. I've extended the
web
> service class such that I specify the report folder, and I get all
> reports and their respective parameters. I then dyncamically create
> the controls (such as datepicker, textbox, etc...) based on the
> Parameter Name, and Type. The drop down list is the one giving me
the
> hardest time though.
> My problem is this: I have a Report with 3 parameters. The first 2
> are dates. I can retrive all info on them just fine. The name,
> prompt, and type (datetime). The third parameter (AppUserKey)
however
> is a dropdown list of all the users. I wrote a stored procedure that
> does this called spGetAppUsers. Here is the structure of this
> parameter as per the RDL file:
> </ReportParameters>
> ...
> ...
> <ReportParameter Name="AppUserKey">
> <DataType>Integer</DataType>
> <DefaultValue>
> <DataSetReference>
> <DataSetName>spGetAppUsers</DataSetName>
> <ValueField>AppUserKey</ValueField>
> </DataSetReference>
> </DefaultValue>
> <Prompt>AppUserKey</Prompt>
> <ValidValues>
> <DataSetReference>
> <DataSetName>spGetAppUsers</DataSetName>
> <ValueField>AppUserKey</ValueField>
> <LabelField>AppUserName</LabelField>
> </DataSetReference>
> </ValidValues>
> </ReportParameter>
> </ReportParameters>
> I can get the Name, and even from the ValidValues, the ValueField and
> LabelField, however, I want the <DataSetName> entry.
> This is so that after I dynamically create the drop down list, I'm
> able to populate it via the stored procedure. Recall that the
> ReportParameter class is defined here:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_ir_5zec.asp
> ReportParameter::QueryParameter is a boolean property. Doesn't
> actually return the name. Just whether or not you are using a query.
> ReportParameter::DefaultValues is a string but is empty, since the
> report hasn't executed the stored procedure (makes sense).
> I want this to be a generic solution. Hence all this trouble to get
> all the "givens" from the Report, and to reconstruct it in ASP .NET.
> I need that DataSetName!!! :-S
> Any help is appreciated.
> Thanks for your time,
> Roy