Create new user in SQL 2005

Hi Experts,

I need to create a login/user for SQL 2005, that needs to ONLY connect to 1 specific database (through a vb app), and be able to access ONLY 1 or 2 tables. That user shouldn't be able to see any other data (tables, stored procs, functions ect) except the ones that i specify somewhere.

How can this be done?
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>User Mapping : <my database> with default schema : db_datareader
that means that the user will be able to read ALL the tables in that database (which is NOT what you wanted to do. remove that checkbox.
then, enter the database, expand the tables list, and right-click the tables, choose "Properties".
that will open a form, where you choose the "security" tab
choose the user, and check the permissions in the below list that you want.

that should do it.

>The login is working but i cant open anything now
using that login? how do you proceed?
Kobz46Author Commented:
I've upped the points as i do believe this is not difficult to do, but i need to do this before 1pm TODAY (SA time).

Thanx guys!
Guy Hengel [angelIII / a3]Billing EngineerCommented:
create the login, and grant that login access to only that 1 database, and inside that database, grant ony the select/update... whatever you need to those 2 tables.
with sql 2005, a user will not see more than he has been granted minimum permissions to.
note: if the database has a lower compatibility level than 90 (which is sql 2005), for example 80 (which is sql 2000), then, this retricted view does not apply, and every user can see every database/table (but not the contents !!)

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Kobz46Author Commented:
Hi angelIII! Thanx 4 replying so soon!!

Tell me, I'm creating the login now, but where do you set which database that login has access to? What is the User Mappings tab for?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>What is the User Mappings tab for?
yes, exactly
Kobz46Author Commented:
Have you done this before?

I've created the login now, with the mapping 2 only that 1 database, but when i try to add the user, and select THAT login i've created as the login, it tells me "The login already has an account under a different user name. MS SQL error: 15063"

If you've done it before, please give me a step by step example? Will save me a lot of time...

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>it tells me "The login already has an account under a different user name.
every login can only have 1 user mapping per database...
you must be doing something "wrong"...
Kobz46Author Commented:
Ok i've managed to create the login. I tried to add it within the database as well which i assume was wrong... :-/

The login is working but i cant open anything now. How do i tell it now which tables it can acces? I've assigned the login to the following:

Server Roles: public
User Mapping : <my database> with default schema : db_datareader

If you are not so familiar with GUI then you can use the following script
  Grant select on <TableName> to <User>

Kobz46Author Commented:

Well as usual in the field of IT, a hundred thousand things came up and the database issue was shifted out.
What i did was:

created a login within the security of the server. Mapped it to the database that i wanted the user in with a db_datareader schema.

Within that table, I right clicked on the 2 tables that i wanted that user 2 access, and gave him read only access, to only those 2 tables.

Logged onto the server with the new user created, and works like a charm.

Thanx AngelIII!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.