Results 1 to 9 of 9

Thread: DB roles

  1. #1
    Join Date
    Aug 2004
    Posts
    24

    Unanswered: 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.

    Thanks
    Last edited by arunaraj; 09-29-04 at 00:03.

  2. #2
    Join Date
    Aug 2004
    Posts
    24
    Hi,
    Anyone could please give the solution for this. I did not get any responses since yesterday.
    Thanks

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    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
    Last edited by hmscott; 09-29-04 at 11:04.
    Have you hugged your backup today?

  4. #4
    Join Date
    Aug 2004
    Posts
    24
    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

  5. #5
    Join Date
    Aug 2004
    Posts
    24
    Help from anyone????
    Last edited by arunaraj; 09-29-04 at 13:12.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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 )
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Aug 2004
    Posts
    24
    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

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can't. PUBLIC is (as you mentioned correctly) a default role that every user has to belong to.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Dec 2003
    Location
    Delhi INDIA
    Posts
    58
    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..
    Deep..

    MCDBA
    ------------------------------------------
    How much data you can afford to lose??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •