add user to mssql server 2012
Posted on 2012-03-20
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''