Table Security

[klewis10367]

I have a live and a Reporting Database...the reporting database is made up of views that are exact replicas of the live tables. My question is that I do not want to give any type of security rights to the tables that make up the view, I just want the users to be able select the views. I do not think this scenario is possible, but if it is can someone please advise me on how to accomplish it.

 

[mrdenny]

I'll need more information before I can give you a good answer.

Are the Live and Reporting databases on the same server?
The views in the Reporting database simply point to the tables on the production database?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[klewis10367]

Same server and yes, they just point to the tables on the live database.

 

[mrdenny]

Grant the user rights to the views. Make a user a member of the public role of the live database and enable cross database chaining on both databases.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[klewis10367]

How do I enable cross database chaining?

 

[klewis10367]

I figured it out. Any, if I put users in the Public role on the live database, do I have to give them access to each database that I want to include in the view?

 

[mrdenny]

Yes the users need to be a member of the public role in each database that the view(s) will be accessing.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com