Solved

Simplifying security on SQL Server 2008 R2.

Posted on 2011-03-25
2
535 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:Richard Quadling
2 Comments
 
LVL 4

Accepted Solution

by:
qasim_md earned 500 total points
ID: 35213927
You have to generate a script with this SQL and execute it in the SQL window::::

select 'GRANT VIEW DEFINITION ON '+ name +  ' TO [User_Name]' from [Database_name]..sysobjects where type = 'P'
select 'GRANT VIEW DEFINITION ON '+ name +  ' TO [User_Name]' from [Database_name]..sysobjects where type = 'VW'

Example:
GRANT VIEW DEFINITION ON sp_update_LpsPrime_tbl_payments_1000000_rest TO [marketing]

Let me know if it helped ?
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 35214009
Aha.

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]..sysobjects where type = 'P' and name like 'RAQ_%'
select 'GRANT SELECT ON [Database_name].dbo.'+ name +  ' TO [db_ExecuteView]' from [Database_name]..sysobjects where type = 'VW' and name like 'RAQ_%'

The sql that the above script outputs runs without issue.

And seems to be OK!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question