Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Access to specific tables

Status
Not open for further replies.

andyc209

IS-IT--Management
Dec 7, 2004
98
GB
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
 
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
"
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


"I'm living so far beyond my income that we may almost be said to be living apart
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top