user-name can access only one particular db

Question

user-name can access only one particular db

sql Anand

SSCommitted

Points: 1597
More actions
October 12, 2011 at 5:13 am

#260270

hi,
i create user-name and password for sql server 2008 but this user can access all the db
but i want to create user-name and password that user-name can access only one particular db in sql server 2008..
how to do this any one can help this

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

cindyaz Ten Centuries Points: 1229 More actions · Answer 1

Anand.A (10/12/2011)
hi,
i create user-name and password for sql server 2008 but this user can access all the db
but i want to create user-name and password that user-name can access only one particular db in sql server 2008..
how to do this any one can help this

1. open management studio, expand security

select the login, right click > properties > User Mappings

Select only those databases that the user needs to access.

2. Ensure guest account is disabled in all databases.

sql Anand SSCommitted Points: 1597 More actions · Answer 2

ya i tried this..but when i used that username for connect sql server 2008 it will access all the db

cindyaz Ten Centuries Points: 1229 More actions · Answer 3

cindyaz

Ten Centuries

Points: 1229

October 12, 2011 at 5:53 am

#1393357

what about guest account? have you checked that?

sql Anand SSCommitted Points: 1597 More actions · Answer 4

i cant choose that option (guest account)its in disappearance in that window page

sql Anand SSCommitted Points: 1597 More actions · Answer 5

how to disable guest in another db..if i try to delete that it will show error

Lowell SSC Guru Points: 323518 More actions · Answer 6

sounds like the login that is tied to the user has sysadmin priviledges by mistake;

take a look here and make sure that the login only has the public role; if it has sysadmin, then uncheck the checkbox.

Under the Server(not the database) find the Security folder...Logins..find the login you created that matches your user....

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sql Anand SSCommitted Points: 1597 More actions · Answer 7

HI,

i create new username in that new login window..

in that server roles page i choose pubilc means when i disconnect old user logon and again connect with new username means i cant access all the db tables..