Post reply

Giving a user access to read only database

  • March 23, 2006 at 9:58 am

    #90983

    I must be missing something.  I have quite a few read only databases and I need to give a user the right to query these databases.  I cannot run sp_grantdbaccess 'Test' because it is a read only database.  I am not able to check the database via EM.  I only want the user to be able to view the data in the database.  She is using Query Analyzer.

    I'm sure there is some way I can do this without making her sa that I'm missing.  Please let me know if you need any more info.

     

    Thanks for your help,

    Carrie

  • March 23, 2006 at 10:43 am

    #628412

    Just give her db_datareader at database level.

    Double click on the users name in the users folder under the database and tick the box.

    However, if you have set the database to dbo read only access then that changes things.

    ------------------------------
    The Users are always right - when I'm not wrong!

  • March 23, 2006 at 10:56 am

    #628417

    Thank you for your response.  I did try db_datareader at the database level.  I believe the database is dbo read only access.  The Read-only option is checked under properties.  When you look at the users under the database, only dbo shows. 

    I will explain our situation a little.  We have clients who have our applications on laptops that we provide.  They have a MSDE version of SQL Server on their laptops.  The application automatically log ships their data to us which is then restored as read-only databases on our production server.  We have Web applications that access the databases so the users can view the data.  I just noticed that the login for the application has sa privileges.  Maybe that is the only way you can set it up. 

    I have a Data Analyst who needs to look at the data for data integrity.  She wants to set up scripts to view the data in the read-only databases.  She doesn't have very much experience with SQL Server. I don't want to give her sa rights.  I also looked at Server roles and none seem to provide the level of security I'm looking for.

    Thanks again for any help.

    Carrie

     

  • March 24, 2006 at 1:06 pm

    #628732

    You will need to take the database out of read-only mode, add the user & permissions, then put it back in read-only mode.  Don't do this if the database is used in log shipping though because it will "break" it.

  • March 24, 2006 at 1:15 pm

    #628735

    Thanks for the response. It is being used for log shipping.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply