We help IT Professionals succeed at work.

Create new user in SQL 2005

Kobz46
Kobz46 asked
on
7,455 Views
Last Modified: 2008-06-19
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?
Comment
Watch Question

Author

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 Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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 !!)

Author

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 Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>What is the User Mappings tab for?
yes, exactly

Author

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

Thanx!
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
>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"...

Author

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

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

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

Author

Commented:
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!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.