Solved

add user to mssql  server 2012

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL date incremented 11 27
script to send html report 3 32
export sql results to csv 6 35
What's wrong with this T-SQL Foreign Key? 7 42
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
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
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

776 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