Solved

SQL Server 2008 Login creation

Posted on 2014-04-09
4
525 Views
Last Modified: 2014-04-09
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
GO
CREATE DATABASE B
GO
CREATE LOGIN user_A with password='U$er_A@1234'
Go
CREATE LOGIN user_B with password='U$er_B@1234'
Go
USE A
GO
CREATE USER user_A for login user_A;
GO
EXEC sp_addrolemember 'db_owner', 'user_A'
GO
USE B
GO
CREATE USER user_B for login user_B
GO
EXEC sp_addrolemember 'db_owner', 'user_B'

Open in new window


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.

------------------------------
ADDITIONAL INFORMATION:

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

Open in new window


   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"

Open in new window


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.
Thank you
0
Comment
Question by:coerrace
  • 2
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
Dulton earned 500 total points
ID: 39989659
I believe you need something like the following.

/*Create User A and Database A*/

USE [master]
GO
CREATE DATABASE [A]
GO
CREATE LOGIN [user_A] with password='U$er_A@1234', default_database=[A]
GO
GRANT CONNECT SQL TO [user_A]
GO
USE [A]
GO
CREATE USER [user_A] for login [user_A];
GO
ALTER USER [user_A] with DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember 'db_owner', 'user_A'
GO

/*Create User B and Database B*/

USE [master]
GO
CREATE DATABASE [B]
GO
CREATE LOGIN [user_B] with password='U$er_B@1234', default_database=[B]
GO
GRANT CONNECT SQL TO [user_B]
GO
USE [B]
GO
CREATE USER [user_B] for login [user_B];
GO
ALTER USER [user_B] with DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember 'db_owner', 'user_B
GO

Open in new window

0
 

Author Comment

by:coerrace
ID: 39989680
The users update ok but they have access to "master" database is possible eliminate the access to "master" databse?
Thank you
0
 
LVL 6

Expert Comment

by:Dulton
ID: 39989710
I'm unable to find any way to limit it. My belief is that as long as you don't create them a user in master, they'll inherit the guest user... with minimal permission.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/058ef200-9aee-4e0c-b312-46852bb4dd7a/deny-access-to-master?forum=sqlsecurity
0
 

Author Comment

by:coerrace
ID: 39989952
Ok thank you anyway if you find a way to block post.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Acces SQL Insert Problem 6 33
Comparison query - 4 columns 9 28
Migration from SQL server to oracle (XML input) 4 28
SQL Availablity Groups Shared Path 2 16
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question