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
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
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.
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
I only see an objects list against the public role, and nothing against the db_datareader (only option to add specific users)
Thanks again
ASKER
A command line to do this would be great, ie that can be issued in Query Analyser.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.