Solved

add user to mssql  server 2012

Posted on 2012-03-20
4
589 Views
Last Modified: 2012-08-13
Installed sql server 2012 to see how it works. Since I am a newbie , few simple questions :
In order for a user to connect to sql server he must have a server login first . Right ? He can not log in to a specific  database on the server directly directly ? Right ? Here's what I've done:
I expanded 'security' tab and selected  'new login'. Then I wrote login name  (senad), selected sql server authentication, provided the password and removed 'enforce password expiration' and 'user must change password at next login' options.Default database is 'TESTDB'.
I tried to connect but server would not connect to my  database ('TESTDB') . Why not ?
Changed default database to 'MASTER' and now login works (?) How comes ?
I logged in as 'senad' and went to see its properties : server roles is 'public' ,user mapping is reduced to master,msdb and tempdb. I can not access 'TESTDB'.
So I figured out I must add myself as another user of 'TESTDB'. So I logged in with my 'sa' account and expanded the 'databases' tab. Found my 'TESTDB' and under its 'security' tab I selected 'new user'. In the window that opened I selected:User name 'senad', User type 'sql user with login', under 'login name' I found 'senad' and selected it. Thats all. Do I need to select more options ? I would like 'senad' only to read the data (not alter it in any way).
So must I select 'db_datareader' under membership ? Also must I select some 'schema' for the user ?
I am asking this because when I try to connect from my application I get :
'The SELECT permission was denied on the object MYTABLE,database 'TESTDB',schema 'dbo''
0
Comment
Question by:senad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 12

Expert Comment

by:sachitjain
ID: 37741677
Actually logins are mapped with users at db level. When you create a login through sysadmin account, you also need to map that login to same/other user on any desired database. You could do it by right clicking the login you just created (under Security->Logins tab) then clicking on properties. There you would see properties window with multiple options in left pane. Go for user mapping and map your login with corresponding user in desired database. There only you could also assign database level roles to your login mapped user.
0
 
LVL 22

Author Comment

by:senad
ID: 37741733
So you need (first) to create login for a user for the server itself (and then create a user at database level) ? Do you need to specify also on table adequate permission ? I mapped the user to database but cant see the table ...
0
 
LVL 12

Accepted Solution

by:
sachitjain earned 500 total points
ID: 37742209
No need to create user separately. On User Mapping tab for login, check the database name and automatically user (with name same as that of login) would be created. Then underneath you need to give that user db_datareader DB role.
0
 
LVL 22

Author Closing Comment

by:senad
ID: 37764082
ohh.. got it ...
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

732 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