We help IT Professionals succeed at work.

how to grant select to public for all tables and views

xenium
xenium asked
on
1,241 Views
Last Modified: 2008-01-09
Hi,

i'd like to grant public select access to all tables and views. Can i do this automatically? THe SQL below generates the statements that in turn will do the job, but how do i then execute these statements automatically, or maybe there is a simpler way? The tables/views get re-created daily, so i need to do this also daily.

select 'grant select on ' + name + ' to public'
FROM (select name from sysobjects where (xtype='U' or xtype='V') and name not like '%load') as Tbls
order by name
Comment
Watch Question

just check off the db_datareader for public role.
 
You do not have to repeatedly grant, deny, and revoke permissions to or from each person ... Every user in a database belongs to the public database role.

Author

Commented:
Where do i "check off the db_datareader for public role"
I only see an objects list against the public role, and nothing against the db_datareader (only option to add specific users)

Thanks again

Author

Commented:
A command line to do this would be great, ie that can be issued in Query Analyser.

Author

Commented:
Follow-up question generalises this solution, and therefore would solve this q also:
https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22907647.html
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.