Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    9

    Unanswered: Read only User on data base

    I'm sure this is a really easy one but I want to create a user with read only access on a sql 2000 database using SQL authentication.

    I'm building a little pilot DB system and I have a trainee developer who I want to built me some queries for crystal reports. But I dont want them to be able to amend/delete data or tables etc.

    I've created the user but how to I set it to read only. Also will the user still have read only rights if the user has access to the database via enterprize manager/Query analysier.

    thanks,

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Read only User on data base

    Grant the user as db_datareader. He or she will can only read data and uses EM and QA.

  3. #3
    Join Date
    Nov 2003
    Posts
    9
    I've Granted the user the db_datareader permission and within EM everything is ok the user has read only access but within QA, logging in with the correct SQL Ath. user name and password he still has permission to Drop tables via the object browser --- very strange or have I done something wrong !

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is the id in a particular role?

    How many roles do you have?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2003
    Posts
    9
    The User has been created within the Public and DB_datareader database role only.

    Do I need to add him to another db role or server role?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Nope. Nothing wrong. You just have to change the password on the account he is using in Query Analyzer. Basically, this user has access to two separate accounts. One with the permissions you want him to have (NT Authenticated), and a second (the SQL authenticated one) with permissions you do not want him to have.

    Naturally, before you go changing passwords, you have to ask where the other (SQL authenticated) account is used (maybe in some application that needs to update data?). In this case, changing the password could be prohibitive, and you may have to resort to asking the user to stop using that account. Ahh, politics.

Posting Permissions

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