SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Grant READ & WRITE for all users
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DaveTheBall
Starting Member

Australia
3 Posts

Posted - 07/09/2013 :  06:51:46  Show Profile  Reply with Quote
Hi All

Just want some advice on setting SSMS 2008 R2 permissions ...

I have an ADE FE and SQL BE which is your typical edit / view / report repository for client project summary information.
Initially there will be 2-3 key users - all of whom need to DELETE, UPDATE or READ records. Some of the data manipulation is done through inherent Access funtionality (e.g. updatable snapshot forms) and some via ADO / VBA.

Users will access the FE via a link to the ADE folder (an AD group of users is set up to permit READ ONLY access to the folder).

I have rolled out a prototype which causes 'SELECT permission denied' messages to the test user. My question is, what is the best-practice method for permitting ALL connected users to READ and WRITE to the db? Do I need to explicitly name each user or should I ask the DBA to set up a user group for Windows Authentication? As you can imagine I'm no DBA and I realise that as more users come on board I will need to consider who has permission to do what - but another question is what damage can the current users do (if granted READ / WRITE permissions) and how? I suppose I can answer some of that by assuming that they can go in via SSMS and start deleting
things!

Thanks in advance for your help ...

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2062 Posts

Posted - 07/09/2013 :  08:14:04  Show Profile  Visit jackv's Homepage  Reply with Quote
An efficient way to manage is to add the AD group as a SQL Server logon, map to relevant databases with relevant privileges. The AD group will have all the members in it.

The added benefit of setting up an AD group is it allows the DBA to focus on applying the security policy - and the application owner can manage what individual accounnts are added to the AD group

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

DaveTheBall
Starting Member

Australia
3 Posts

Posted - 07/09/2013 :  08:28:41  Show Profile  Reply with Quote
Thanks Jack - that sounds like the best-practice 'standard' setting I'm looking for, which I can apply to this and any newly-developed dbs.

How would I create the logon in SSMS 2008 R2 via the interface? Is it Right Click on Security-Users-<Select 'New User' ...> then find the AD group from there?

Regards - Rick
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3651 Posts

Posted - 07/09/2013 :  08:46:40  Show Profile  Reply with Quote
Yes, you are correct. You might have to select the appropriate object types and domains to see the AD group you are looking for.

If you want to repeat the process for other servers, you can generate the creation script and use that script, rather than having to go through the user interface. To generate the creation script, use the SSMS GUI and go through all the steps as if you were going to create the AD login, but instead of clicking ok at the end, click the script button at the top left of the right panel. That will generate the script that SQL Server would have used internally had you clicked the OK button.
Go to Top of Page

DaveTheBall
Starting Member

Australia
3 Posts

Posted - 07/09/2013 :  11:13:21  Show Profile  Reply with Quote
Thanks again for your help Jack - I'll report back if I have any issues but that should do it!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000