• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1157
  • Last Modified:

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
0
xenium
Asked:
xenium
  • 4
  • 2
1 Solution
 
SQL_SERVER_DBACommented:
just check off the db_datareader for public role.
 
0
 
SQL_SERVER_DBACommented:
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.
0
 
xeniumAuthor 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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
xeniumAuthor Commented:
A command line to do this would be great, ie that can be issued in Query Analyser.
0
 
xeniumAuthor Commented:
Follow-up question generalises this solution, and therefore would solve this q also:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22907647.html
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now