Richard Quadling
asked on
Simplifying security on SQL Server 2008 R2.
Hi.
I'm using SQL Server 2008 R2 (it's the Dev Ed on my Dev Svr).
I have a DB which is under development.
I use Windows Authentication.
I want to allow all users access to the stored procedures and views for the DB.
What is the simplest way to do this please?
I have the sysadmin role (only because I am the developer on the dev svr - not really a proper admin) and I've manually allowed one other user access to the SPs (ticking the execute option for the SPs for that user). I don't have any views yet.
I know I can do this for each user, but I'm quite happy to simply allow all authenticated users access. The app is both an Excel spreadsheet and a PHP developed web app.
When I add new SPs and views, can these inherit the permissions? Or do I need to add a security setting.
TIA,
Richard.
I'm using SQL Server 2008 R2 (it's the Dev Ed on my Dev Svr).
I have a DB which is under development.
I use Windows Authentication.
I want to allow all users access to the stored procedures and views for the DB.
What is the simplest way to do this please?
I have the sysadmin role (only because I am the developer on the dev svr - not really a proper admin) and I've manually allowed one other user access to the SPs (ticking the execute option for the SPs for that user). I don't have any views yet.
I know I can do this for each user, but I'm quite happy to simply allow all authenticated users access. The app is both an Excel spreadsheet and a PHP developed web app.
When I add new SPs and views, can these inherit the permissions? Or do I need to add a security setting.
TIA,
Richard.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So, I can create a DB Role, add the appropriate Active Directories user groups to the role and then use ...
select 'GRANT EXECUTE ON [Database_name].dbo.'+ name + ' TO [db_ExecuteView]' from [Database_name]..sysobject
select 'GRANT SELECT ON [Database_name].dbo.'+ name + ' TO [db_ExecuteView]' from [Database_name]..sysobject
The sql that the above script outputs runs without issue.
And seems to be OK!