Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DaveTheBall
Starting Member
3 Posts |
 Posted - 2013-07-09 : 06:51:46
|
| Hi AllJust 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
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2013-07-09 : 08:14:04
|
| 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 groupJack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
DaveTheBall
Starting Member
3 Posts |
Posted - 2013-07-09 : 08:28:41
|
| 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 |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-09 : 08:46:40
|
| 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. |
|
|
|
DaveTheBall
Starting Member
3 Posts |
Posted - 2013-07-09 : 11:13:21
|
| Thanks again for your help Jack - I'll report back if I have any issues but that should do it! |
|
|
|
|
|
|
Grant READ & WRITE for all users