[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7407
  • Last Modified:

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?
0
Kobz46
Asked:
Kobz46
  • 5
  • 4
1 Solution
 
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!
0
 
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 !!)

0
 
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?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>What is the User Mappings tab for?
yes, exactly
0
 
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...

Thanx!
0
 
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"...
0
 
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

0
 
Guy Hengel [angelIII / a3]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?
0
 
assystCommented:
If you are not so familiar with GUI then you can use the following script
   
  Grant select on <TableName> to <User>

0
 
Kobz46Author 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!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now