Link to home
Start Free TrialLog in
Avatar of xenium
xenium

asked on

how to grant select to public for all tables and views

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
Avatar of SQL_SERVER_DBA
SQL_SERVER_DBA
Flag of United States of America image

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.
Avatar of xenium
xenium

ASKER

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
Avatar of xenium

ASKER

A command line to do this would be great, ie that can be issued in Query Analyser.
Avatar of xenium

ASKER

Follow-up question generalises this solution, and therefore would solve this q also:
https://www.experts-exchange.com/questions/22907647/How-to-execute-a-series-of-SQL-statements-that-are-generated-from-a-query.html
ASKER CERTIFIED SOLUTION
Avatar of dportas
dportas

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial