June 24, 2002 at 11:59 am
Hello,
We found the nifty stored procedure that creates web reports, sp_makewebtask. The major problem is that the user needs to be a system administrator of the box when running this stored procedure. We do not want to grant our reporting guys SysAdmin rights to the box.
Can anyone suggest a work around for this?
Thank you very much!!
Melanie
June 25, 2002 at 5:11 am
From BOL
quote:
The user must have SELECT permissions to run the specified query and CREATE PROCEDURE permissions in the database in which the query will run. The SQL Server account must have permissions to write the generated HTML document to the specified location. Only the members of the sysadmin fixed server role can impersonate other users.
I read this to mean they must have SELECT rights on the source and CREATE PROCEDURE rights. And their user login account must have access rights to the Windows directory location where the fil will be output so therefore you will need to create and NT account instead of SQL Account. I have not tried but this should be right.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 25, 2002 at 8:47 am
Hello,
Thank you for your response. I'll test this further to see if the noted permissions will work in our environment.
Melanie
June 26, 2002 at 9:37 am
Hello,
Just thought I'd fill you in on how I was able to work around the sysadmin issue. I copied the procedure and modified the section where it's checking to see if the user is a member of the sysadmin role. My code now looks like this:
IF ( NOT ( is_srvrolemember('sysadmin') = 1 ) )
BEGIN
IF ( NOT ( is_member('WebTask') = 1 ) )
BEGIN
RAISERROR( 15003, -1, -1, 'WebTask' )
RETURN(1)
END
END
This works nicely and allows non-sysadmins to run the modified sproc as long as they are members of the WebTask db role. The only other thing to mention is that for the output shared directory, Everyone was granted Write permission. Then only the actual members of the WebTask role were granted full control.
Hope you find this helpful!
Melanie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply