Hello we created a login for 2 users with access to different database each other without one have access to the other database, in other words "USER A" just have access to database "A" and "USER B" just have access to database "B" and "USER A" and "USER B" must not have access to other database on the system. To create that 2 logins and permissions we made:
CREATE DATABASE A
CREATE DATABASE B
CREATE LOGIN user_A with password='U$er_A@1234'
CREATE LOGIN user_B with password='U$er_B@1234'
CREATE USER user_A for login user_A;
EXEC sp_addrolemember 'db_owner', 'user_A'
CREATE USER user_B for login user_B
EXEC sp_addrolemember 'db_owner', 'user_B'
But when we we try to login with SQL Management Studio with "User A" or "User B" say login failed:
TITLE: Connect to Server
Cannot connect to 14933-71078.
Login failed for user 'xxx'. (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
Now in same SQL Managmenet Studio with main Windows Administration we go Security/Logins to "User A" or "User B" and in Properties/Server Roles is just checked for any of the 2 "public". We made a test on check all and after check all on any user enter perfect without the login error. The problem with check all is we need to know what to check for security reasons because we don´t want that 2 logins have access to "master" or any other database on the system just the assigned for each user and we need the access of "User A" or "User B" because we´ll use in a connection string in an asp application similar like:
connectionstring="Provider=SQLNCLI10.1; Persist Security Info=false; Data Source=server; Initial Catalog=database A; User Id=UserA;Password=xxx"
And check all options in roles for sure we are giving access to all, then what we need to check there? And like we have attempts of someone trying to enter the SQL we want only force like we say just "User A" just database "B" and "User B" just database "B" nothing else for them. And of course considering also that logins when we´ll use in the connection string must be able that uses to use our system good, is a school because our system when any student enter with his/her password our application record scoring, edit profile, etc.
I hope someone can help.