Configuring SQL Server Permissions

[NewBean]

How can I configure SQL Server in such a way that the a given user can only "execute(select)" a view without letting him see,execute,read etc... on any object in any database on the server?

 

[bassguy]

New Bean,
I have found the easiest way to lock down the SQL server is to deny everything from all views and tables

Then make stored procedures that select the table or view and grant EXEC permission to the Stored Proc. This takes a bit more work but eliminates any ad hoc queries...Only time I have found any problems is if I am Creating a dynamic Query and then running sp_executesql against the Dynamic Query then you have to either assign an App role or open up the Table being Dynamically Queried

hope that Helps

Bassguy

 

[NewBean]

IS THERE A WAY TO DENY ALL TABLES AND VIEWS IN ONE STATEMENT...RATHER THAN CLICKING 100 TABLES AND VIEWS WITH 5 KINDS OF ACCESSES?

 

[bassguy]

CLicky, clicky

sorry I do not know.

I am sure there is the data is just in a table in the master database but I have never tried to figure it out

bassguy

 

[tlbroadbent]

In SQL 7 and 2000 you can assign a user to the db_denydatareader and db_denydatawriter roles. This will prevent the user from seeing or changing any data in a database.

Check my answer to your question in thread183-153521. And please don't post the same question multiple times.

I recommend joining Tek-Tips. There are several benefits for doing so. No money, however! ;-) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.