DB roles

I am creating a new user. I would like to give read only access just for the tables in a database. I had assigned only public and db_Datareader roles to this user. With these roles the user could able to see the script of the SPs and also the DTS packages. Also with the above roles the user could able to create new DTS packages and SPs. Is it possible to deny the user to look at the sps and ability to open the DTS packages created by some other users.

What I need to do is create a role with just table data read access so that they could just select the data only nothing more than that.
Also another role with dataread and ability to create the DTS packages from other servers by accessing this data. Anotherthing we need is With this role the users could create Database schema.

This is an urgent request. Please advise me ASAP.

ThanksHi,
Anyone could please give the solution for this. I did not get any responses since yesterday.
Thanks|||If I read the last couple of sentences correctly, you need tow roles:

Role 1:
Read-only access to tables in database
No ability to create DTS packages
No ability to read SPs

Role 2:
Read-only access to tables in database
Can Create DTS packages
Can create own DB Schema (?)

I believe that the ability to create DTS packages is a privilege granted to the Public role in the msdb database (which is an unpleasant default in my opinion). You can remove the privilege by revoking execute on the following SP to Public:

sp_add_DTSPackage

You might also consider the following:

sp_enum_DTSPackages

You will then have to create a custom role with the privileges "added back" to fulfill your second requirement. I wasn't certain by what you meant with the need to "create own db schema". Did you mean the ability to create tables?

Regards,

hmscott|||Yes. I Need Role1 as follows

Role 1:
Read-only access to tables in database
No ability to create or Read DTS packages
No ability to create or Read SPs

Role 2:
Read-only access to tables in database

Can Create DTS packages from other servers means to import data onto ther user's server from the tables from my server.

Can create own DB Schema Means if we give just read only access to the tables only on my server, could they be able to get create ER diagrams using ERWIN or some other tool?

I could not find either of these two procedure (sp_add_DTSPackage,sp_enum_DTSPackages) in BOL.

What I understood is if we have to revoke the execute permission on sp_add_DTSPackage the syntax would be
Revoke EXEC ON sp_add_DTSPackage TO RoleName
Is this right syntax.

Do we have to revoke for the public role or should I delete the public role (which will be assigned to a new user as a default) from the new user I created. If I delete this role will it have an effect on any other permissions the user has.
I am not sure what sp_enum_DTSPackages does.

Also I need to deny the permission to open the existing DTS packages by the user and modify them. What would be the solution.

I did not get what you meant by "You will then have to create a custom role with the privileges "added back" to fulfill your second requirement"

I am thinking of creating several custom roles like report user role, Developer role etc.
When we create new users, always public role will be assigned as default. Is is necessary to have this role for every user or we can delete this role.

I think I put too many questions. Please advise me.

Thanks|||Help from anyone??|||With packages you got it right, except replace REVOKE with DENY.

As per modifying SPs, I think you may have to DENY on SYSOBJECTS, unless you play with SYSCOMMENTS and such (can't test it now, Yukon is everywhere ;))|||How about if I delete the public role for the new user(which will be assigned as a default). Then the user can't add any DTS pages. I just want to make sure whether this will have any effects.

Thanks|||You can't. PUBLIC is (as you mentioned correctly) a default role that every user has to belong to.|||select choosen database properties from Enterprises manager go to permissions tab..there you can define create permissions for users/roles for table/view/store proc etc..