August 18, 2004 at 9:27 pm
Hi,
We have sql server 2000 and currently we have one of our projects running using a database in it. I am the admin for this and I have full access. How ever I would like to include another user who is at a remote place but in our network, to grant read only access to only one of the databases that is being used for the project. The work of that person would be to view the data in the data base and use sql queries to generate reports.
Ours is an intranet network. How should I go about giving read only access to this. Is it that I should install sql server with sql query analyzer on her machine and create a user login to our sql server with read only access to only one data base, or anything else I should do?
Thanks,
-UwinIwin.
August 19, 2004 at 7:56 am
in a nutshell that would be the requirement...
August 19, 2004 at 8:35 am
Sounds like you're on the right track.
1) As far as client tools, sounds like you're right on. You only need to install the SQL Server client tools, so when presented with the option, only do that.
2) Grant the login access to SQL Server.
3) Grant the login a user account in the database.
Here's where you can deviate...
You can either create a user-defined role and give that role SELECT permissions against all user databases or you can put the user in the db_datareader database fixed role. Here are the trade-offs. In the first case, the user doesn't have the ability to query against any of the system tables except where public already has permission. But you have to maintain the SELECT permissions any time you add or remove tables. In the second case, db_datareader has implicit rights to read against any table or view... so if you need to restrict the user in any way, you can't use it.
K. Brian Kelley
@kbriankelley
August 20, 2004 at 2:38 pm
Thats great, thank you so much for your suggestions, but one morething, as bkelley told, if I give db_datareader, the user is able to have a readonly view to all the tables.
The reason I saw and thought was tha may be because of public access, even though I have granted access to master and our project database only.
Is there a way that I can restrict that user to particular tables of the database?
Thanks
-Uwiniwin.
August 20, 2004 at 8:35 pm
Create a user-defined database role within the database.
Grant that role SELECT rights against the tables you want to give access to.
Put the user in the user-defined database role.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply