February 17, 2004 at 10:18 am
Please help!!!
I created user account. This user is db_datareader and db_datawriter.
He should not be allowed to create and run DTS packages from
How can I deny rights to create and run DTS packages?
February 17, 2004 at 10:53 am
These two roles are database specific roles. Is the login in a sysadmin group?
I am going to suggest a way to prevent people from saving dts packages on your server. I tried it and then found a SQL Magazine article about it (the article is near the bottom):
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=40249
You remove the execute permission on sp_add_dtspackage in the MSDB database from public. Sysadmins will be able to add packages, but not ordinary users.
Russel Loski, MCSD
Russel Loski, MCSE Business Intelligence, Data Platform
February 17, 2004 at 12:53 pm
Thanks for your quick response. I got how to prohibit save and execute DTS packages. I do not know how to restrict creating of packages and run them without saving.
February 17, 2004 at 11:02 pm
Perhaps I'm missing something but if the accounts you mentioned are Production accounts that only have read and write respectively, how are your users able to login with them and use them to facilitate the DTS problem?
Your developers/users shouldn't have anything but read or write to the particular tables they have need to access. This combined with the previous solution offered should keep them from saving a DTS package to the Production server.
If you are trying to prevent them from connecting to a Production server to read data into a DTS for transfer to another environment, you will have a hard time doing so unless you take away their read capability to the Production database. As long as they only have read-only on the database, they shouldn't be able to save the data from a DTS to the Production system whether they can save the package itself or not as they won't have write access to the Production database. This should be especially true if you haven't given them the passwords to the two DB accounts you mentioned.
Hope that helps.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply