May 2, 2011 at 8:45 am
I know this is a very basic question, but I haven't found a simple answer yet.
How do I implement table level (or column level) security in a SQL Server 2008 database? Right now, many of our users connect to one of our databases and have full access to all of the tables. I'd like to make it so that they don't see the tables or the columns they don't have access to. Is there a simple and straightforward way of doing this?
Mike Scalise, PMP
https://www.michaelscalise.com
May 2, 2011 at 8:53 am
The "simple" way is to create groups and assign permissions to those groups. Then add the users to that group to get all the permissions.
That's the easy part. The hard part is documenting what all groups should and should not see. This is where you'll spend 95% of your time. There's no shortcut for that one... except granting everything to everyone or maybe just access to sps. Then you can simplify the access control from the application.
May 2, 2011 at 12:12 pm
Ninja's_RGR'us (5/2/2011)
The "simple" way is to create groups and assign permissions to those groups. Then add the users to that group to get all the permissions.That's the easy part. The hard part is documenting what all groups should and should not see. This is where you'll spend 95% of your time. There's no shortcut for that one... except granting everything to everyone or maybe just access to sps. Then you can simplify the access control from the application.
What I'm more or less asking is exactly how I would assign the groups to the specific tables or grant read access to specific columns within tables. I already have AD groups as part of the logins in SQL Server. I just need to go that step further and restrict the table/column viewing permissions. Thanks for the response before.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
May 2, 2011 at 3:19 pm
You need to readup on the subject.
Read up on grant and deny in books online. Everything you need is there.
May 2, 2011 at 6:26 pm
Follow Ninja's advice. Use the Grant\ Deny to the securables.
Thank You,
Best Regards,
SQLBuddy
May 3, 2011 at 1:36 am
Use grants command to grant permissions on a securable to a principal.
Also you could use view to query the column you need and grant the user permission to query the view.
http://msdn.microsoft.com/en-us/library/ms187965.aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply