April 25, 2005 at 6:48 pm
I have a user that I only want to give access to all the user tables. I gave this user db_datareader and db_datawriter. To test this access, I logged in as the user and found out that I was able to access the Data Transformation Server (create new package, etc..) and management job (create new job). How do I remove this user access to Data Transformation Server and Managmenet Job section?
Please advice.
Thanks.
April 27, 2005 at 5:19 am
The below should do the trick
--Tighten permissions on jobs procedures to prevent low privilege users from submitting or managing
--jobs in the event that the SQL Agent service is ever activated.
USE msdb
REVOKE execute on sp_add_job to public
REVOKE execute on sp_add_jobstep to public
REVOKE execute on sp_add_jobserver to public
REVOKE execute on sp_start_job to public
GO
--Tighten permissions on the web tasks table to keep malicious users from creating or altering tasks.
USE msdb
REVOKE update on mswebtasks to public
REVOKE insert on mswebtasks to public
GO
--Tighten permissions on the Data Transformation Services (DTS) package connection table so that
--malicious users cannot affect DTS packages.
USE msdb
REVOKE select on RTblDBMProps to public
REVOKE update on RTblDBMProps to public
REVOKE insert on RTblDBMProps to public
REVOKE delete on RTblDBMProps to public
GO
Mandip
April 27, 2005 at 12:11 pm
Thanks for the reply Mandip.
One question... If I revoke this using sa, would that revoke all databases permission? Can I specific which database I want to revoke? Thanks again.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply