INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access to specific tables

Access to specific tables

(OP)
I have an SQL Server on a remote server to which i log in with a specific username and password. I want to allow other users access to three tables only on one specific database for them to view and download data. How can i give them access without giving them the admin username and password that will give them access to all the databases? I am using SQL 2005 at the moment but i am migrating it all to 2008 in the coming weeks but assume the solution will work for both.

thanks for any help

RE: Access to specific tables

In SQL Enterprise Manager, you can add new users and only give them read only permission.

There are plenty of articles on the web to do this, and you can do anything from permit read only access to these tables (alternatively create a new role which only has read only access to these tables and assign the role to the users you create. this way if they need access to other tables in future, you only need change the role.)

Programatically you can do this using
"

CODE --> SQL

GRANT <permission> [ ,...n ] ON 
    [ OBJECT :: ][ schema_name ]. object_name [ ( column [ ,...n ] ) ]
    TO <database_principal> [ ,...n ] 
    [ WITH GRANT OPTION ]
    [ AS <database_principal> ]

<permission> ::=
    ALL [ PRIVILEGES ] | permission [ ( column [ ,...n ] ) ]

<database_principal> ::= 
        Database_user 
    | Database_role 
    | Application_role 
    | Database_user_mapped_to_Windows_User 
    | Database_user_mapped_to_Windows_Group 
    | Database_user_mapped_to_certificate 
    | Database_user_mapped_to_asymmetric_key 
    | Database_user_with_no_login 
"
More detail at http://msdn.microsoft.com/en-us/library/ms188371(v...

"I'm living so far beyond my income that we may almost be said to be living apart"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close