View Security

[klewis10367]

I have 2 databases, 1 is the production and the other is all views of the production data (On the same sql2000 server) used for reporting. The one question I have is that I do not want to have any of the users in database 2 to have any rights to the tables in database 1. Is this possible and if so how. I am relatively new to SQl so if you could be as detailed as possible I would appreciate it.

 

[ptheriault]

Yes it is very easy to set up.

This is the GUI approach.
Let me know if you want the TSQL.
1. Open Enterprise Manager.
2. Connect to your instance of SQL Server and drill down into the security folder.
3. Double click on each user account. This will open up the properties window for that user.
4. Check the database you want them to have access to in the top part of the window. Grant them the database role of db_datareader in the lower part of the window. This gives them access to only read from that database.
5. Click ok.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[klewis10367]

Do I have to grant the database reader role access to each of the tables. I am not very familiar with roles as I usually do security based on users.

 

[klewis10367]

Also, If I put them in that role in the production server that will give those users in that role read rights to every table on that server which I do not want to do. I just want the user in database 2 to be able to open a view and not have them in any role or rights in database 2.

 

[klewis10367]

What I am trying to avoid is having a savy user create an odbc connection to one of the production database's and bypassing the Reporting Database. If they have the read only role they would be able to do this.

 

[ptheriault]

Ok, don't add them to the db_reader role. Just add them to the database. By default they will be members of the public group.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

I hit post to soon. You'll have to grant select to the user on the view.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[klewis10367]

Users are added to the public role when they are created, I have already tried the scenario that you suggest and it tells me I need to grant access to each specific table that makes up the view.

 

[ptheriault]

You granted select rights on the view and your getting that error?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Also,
If you have only created the user in database 2 what is wrong with making them db_reader? They will only be able to read from all tables in database 2. They will have no access to the prod database.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[klewis10367]

My views in database 2 are only referring to tables in database 1.