Solved

add user to mssql  server 2012

Posted on 2012-03-20
4
580 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Find unused columns in a table 12 70
Building JSON Results Table FROM DB 9 33
SQL Syntax 6 41
SQL Server code help needed 14 30
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

756 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