Access to specific tables

[andyc209]

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

 

[hmckillop]

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
"

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=sql.90).aspx

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