Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Security / August 2006

Tip: Looking for answers? Try searching our database.

SQL2005 grant user access to certain tables

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
walanta - 24 Aug 2006 18:27 GMT
Hi,

We have a database on SQL2005 with 50 tables.
We want to grant someone read table rights to only some tables.

Right now we created a SQL account and he can connect and read all
tables in this database.

I gave this user as db_datareader in Database role membership in
security.

How can I easily stop that? Is there a place I can check to deny him
any access and just grant him a few tables?   Thanks!!
Arnie Rowland - 25 Aug 2006 21:37 GMT
REMOVE that user from the db_datareader role -that role allows reading ALL
tables.

Create a new Role, add the user to the new Role.
Then for each table that the user needs to read, give SELECT permission to
the new Role. (By doing it for the Role, if you need to allow another user
(vacations, etc.) to do the same thing, you just add the additional user to
the same Role.)

Verify that the user is not a member of any other Role that has permissions
to SELECT data from the tables.

Make sure that the 'Public' does not have SELECT permissions for any
tables -PUBLIC 'should' not have any permission for any activity for any
object.

Signature

Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

> Hi,
>
[quoted text clipped - 9 lines]
> How can I easily stop that? Is there a place I can check to deny him
> any access and just grant him a few tables?   Thanks!!
walanta - 27 Aug 2006 01:43 GMT
Should I create database role or application role.
I created database role and it seems like it's working. What's the
difference?   Thanks

>REMOVE that user from the db_datareader role -that role allows reading ALL
>tables.
[quoted text clipped - 11 lines]
>tables -PUBLIC 'should' not have any permission for any activity for any
>object.
Arnie Rowland - 27 Aug 2006 04:24 GMT
Database role was the correct option for this situation.

An Application role is used to provide permissions to all users of an
application. The application has to execute a special stored procedure in
order to use the Application Role, and then the users are limited to the
permissions afforded by the application role. You may wish to read up on
Roles in Books OnLine.

Signature

Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

> Should I create database role or application role.
> I created database role and it seems like it's working. What's the
[quoted text clipped - 17 lines]
>>tables -PUBLIC 'should' not have any permission for any activity for any
>>object.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.