Solved

add user to mssql  server 2012

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now