Link to home
Start Free TrialLog in
Avatar of Kobz46
Kobz46

asked on

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?
Avatar of Kobz46
Kobz46

ASKER

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!
Avatar of Guy Hengel [angelIII / a3]
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 !!)

Avatar of Kobz46

ASKER

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?
>What is the User Mappings tab for?
yes, exactly
Avatar of Kobz46

ASKER

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...

Thanx!
>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"...
Avatar of Kobz46

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If you are not so familiar with GUI then you can use the following script
   
  Grant select on <TableName> to <User>

Avatar of Kobz46

ASKER

Hi!

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!