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?
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?
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 !!)
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 !!)
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?
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
yes, exactly
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!
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"...
every login can only have 1 user mapping per database...
you must be doing something "wrong"...
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you are not so familiar with GUI then you can use the following script
Grant select on <TableName> to <User>
Grant select on <TableName> to <User>
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!
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!
ASKER
Thanx guys!