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

Accidental duplicate results...

ALTER PROCEDURE discussions_GetTopics
(@.board_id as int)
AS

SELECT discussions_Topics.*, discussions_Posts.*, user_1.UserName AS Topic_Author_Username,
user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_ID
FROM discussions_Topics INNER JOIN
discussions_Posts ON discussions_Topics.topic_id = discussions_Posts.topic_id INNER JOIN
aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN
aspnet_Users AS user_2 ON user_1.UserId = discussions_Posts.poster_id
WHERE (discussions_Topics.board_id = @.board_id)

I am simply trying to return a result for each topic, that has user info for both the author of the topic and the author of the last post (user_1, user_2)

The problem is, it will return multiple datarows with the same topic, and each of them have a different last post author.. when there can only be one last poster... idk.. im confused.. help?

Try:

ALTER PROCEDURE discussions_GetTopics(@.board_idas int)ASSELECT discussions_Topics.*, discussions_Posts.*, user_1.UserNameAS Topic_Author_Username, user_1.UserIdAS Topic_Author_ID, user_2.UserNameAS Post_Author_Username, user_2.UserIdAS Post_Author_IDFROM discussions_TopicsINNERJOIN discussions_PostsON discussions_Topics.topic_id = discussions_Posts.topic_idINNERJOIN aspnet_UsersAS user_1ON user_1.UserId = discussions_Topics.topic_posterINNERJOIN aspnet_UsersAS user_2ON user_2.UserId = discussions_Topics.last_poster_idWHERE (discussions_Topics.board_id = @.board_id)

|||

SELECT discussions_Topics.*, discussions_Posts.*, user_1.UserName AS Topic_Author_Username,
user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_ID
FROM discussions_Topics INNER JOIN
discussions_Posts ON discussions_Posts.topic_id = discussions_Topics.topic_id INNER JOIN
aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN
aspnet_Users AS user_2 ON user_2.UserId = discussions_Posts.poster_id
WHERE (discussions_Topics.board_id = @.board_id)

In the Topics table, I did not have a field for "last_poster_id". I only have a "last_post_id" to use to search the "Posts" table to find the author of the post.

The problem with this query is that it returns a seperate topic for each user that has posted in the topic as the last poster. any help?

|||

WAIT!

GOT IT!

ALTER PROCEDURE discussions_GetTopics
(@.board_id as int)
AS

SELECT discussions_Topics.*, discussions_Posts.*, user_1.UserName AS Topic_Author_Username,
user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_ID
FROM discussions_Topics INNER JOIN
discussions_Posts ON discussions_Posts.post_id = discussions_Topics.topic_last_post_id INNER JOIN
aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN
aspnet_Users AS user_2 ON user_2.UserId = discussions_Posts.poster_id
WHERE (discussions_Topics.board_id = @.board_id)

Dumb noob mistake.. lol... im still learning...