March 27, 2009 at 2:26 am
I am using sql server 2005 version 9.000 , and I have created a database in that.I want to retrict the database access for only 2 users.
In sql server 2005 we have either single or multiple.
I am using vb.net 2005 to connect the database.I wrote a application and connected the database.
Now i have one more application in which the same database is used.
Now changed the sql server 2005 into single user mode. then i am not able connect to the datase. it is giving error database already in use.
If i change that to mutli mode then i can connect but i want to restrict that access to only 2 connection.
I have created an user testuser and given the permission to read the database.
plz help
March 27, 2009 at 4:13 am
you can do this for any user other than 'sysadmin'.
Let you have two users: userGrant and userDenined; and you want to allow and denied access for database testDB accordingly. To do so, create users as follows.
userGrant:
SQLServer->Security->Logins->New Login
-Login name: userGrant
-SQL Server Authentication
-{give password}
-Go to 'User Mapping' page.
-Select database 'testDB' from the list and check the 'Map' box 'ON'.
-Now, in the database role selection at below, check 'ON' for 'db_owner'.
-Click 'OK'.
userDenied:
SQLServer->Security->Logins->New Login
-Login name: userGrant
-SQL Server Authentication
-{give password}
-Click 'OK'.
Now, login with both user and check when you can ACCESS database testDB. (You can view it either way, but cannot access it all the time).
"Don't limit your challenges, challenge your limits"
March 27, 2009 at 6:26 am
You need to create 2 logins and grant access to the db for only those 2 logins as mentioned in previous post.
Also make sure that the database role membership is restricted to only those roles that are required and not more.
"Keep Trying"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply