In SQL Server 2008 R2, how do restrict write access to only one user, while allowing others Read Only access to specific tables.

I have a SQL Server 2008 R2 database that I need to restrict so that only one individual, mjones, can add records to a specific table.  

Only mjones should be able to add records to TableOne.  

Everyone else can add records to other tables in the database, but not that one.  

Everyone can view records in every table, including TableOne.  

Everyone can UPDATE records in every table, including TableOne.  They just can't add new records to TableOne.  Except for mjones.  She, and only she, can add records to TableOne.

How to...

Thanks!  :-D
LVL 1
megninAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
GRANT INSERT ON TableONE TO mjones
GRANT SELECT, UPDATE ON TableONE TO Someother user
GRANT SELECT, UPDATE ON TableONE TO mjones

Would do it - AFTER you have removed any grants that they have


Kelvin
0
megninAuthor Commented:
Thanks, Kelvin.

We currently have Active Directory OUs with a number of staff in them that we use for assigning access permissions.

All the staff that work with that database are in OU_ForDatabaseOne, including mjones.

How would I handle that?  Do I need to remove mjones from OU_ForDatabaseOne to give her the extra permissions?

Thank you for the specific statements to accomplish it.  I'm still learning.  :-)
0
megninAuthor Commented:
Is it possible to grant accesses with that level of granularity using SQL Server Management Studio?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Kelvin SparksCommented:
OK, I'd set the permissions for the OU replacing the other users with that OU name.

I can't remember how SQL manages permissions from multiple memberships. Initially try just granting mjones the extra grant and ensuring the rest don't have it (use may have to add mjones login explicity to do this). . If that don't work, you'll have to look at removing from the group and setting up individually
0
megninAuthor Commented:
I can do that.  I've already added mjones login explicitly, expecting I'd have to remove her from the group.
0
Scott PletcherSenior DBACommented:
No, you don't need to remove her from the group, but you do need to add her separately as a login and a user on the db if you want to grant her permissions directly.

SQL will merge the permissions, except that DENY overrides GRANTed permissions.  [If any permission is a full sysadmin, the other permissions, including DENYs, are ignored, and full sysadmin permission are granted.]

You could also create a separate AD entry that was granted the special permission(s) in SQL Server, then add only mjones to that group.
0
megninAuthor Commented:
Okay, so could I do something like,
DENY INSERT ON TableOne TO OU_Name?

It looks like there are three tables that she will be INSERTING and everyone else only UPDATING... primarily one of them.

So, DENY INSERT ON TableOne, TableTwo, TableThree TO OU_Name
and GRANT INSERT ON TableOne, TableTwo, TableThree TO mjones?
0
Scott PletcherSenior DBACommented:
Keep in mind that DENY overrides GRANT, so if you DENY insert to the group mjones is in, you've denied mjones even if you grant it to her separately.

In this case, grant insert just to her and don't worry about deny.
0
megninAuthor Commented:
Oh, yeah.  Then how would I remove INSERT access from the rest of the OU?  The primary goal is to remove INSERT access from everyone but mjones.  We are having issues where people are adding records unnecessarily and mucking up the database.
0
Kelvin SparksCommented:
REVOKE INSERT ON TableOne FROM OUgroupname
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
megninAuthor Commented:
Ah!  Great.

So, with mjones out of the OU, is this really all I need?
REVOKE INSERT ON TableOne FROM OUgroupname

Then just make sure that mjones has INSERT access, GRANT if necessary?
0
Kelvin SparksCommented:
Yes
Although no need to remove mjones from the OU, just as long as they have a separate login with the INSERT.


Kelvin
0
megninAuthor Commented:
Okay, gotcha.   Deny trumps GRANT but REVOKE does not trump GRANT, correct?  (I swear, I've almost got it)  ;-)
0
Kelvin SparksCommented:
You got it
REVOKE just takes away one that existed previously, but DENY shuts the door and overrides other grants
0
megninAuthor Commented:
Thank you so much for the lesson guys!

Now I still have to adjust the web application so the users who no longer will have INSERT access don't try to add a record, just to get an error when the insert fails.   ;-}
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.