Authentication for Existing Schema of a DB with New User Login

1) After Restoring a DB, Create a New Login for new database with a name.

2) Open SQL Server in Windows Authentication to create a New Login.


3) Open the Security Node in SQL Server and Right Click Login and select New Login.

4) It will open a New Login Dialog box.


5) Give the New Login Name and select SQL Server Authentication option and give your password.

6) Unselect the “User must change password at next login” check box.

7) After this, Select Server Roles.


8) Select dbcreator in the Server Roles and select User Mapping


9) Select the DB you want to Map with this login name and select the Database Role Membership for the DB.


10) Select db_owner in the DB Role and public is default.

11) Now Select Database Default Schema for this Login, Select the DB and Click the Browse button in Default Schema Column.

12) It will show select schema dialog box.

13) Click the Browse Button-> and select the Schema you want.


14) And Click ok the Schema is selected for the selected DB.


15) And click ok and disconnect the DB Engine.

16) Now Connect DB Engine with newly created login.

17) Select the DB->Security->Users->loginname. Check the login name is available in the users list of selected DB.

18) If available you continue the process otherwise check the new login properties and change that with windows authentication mode.

19) If available, Open the DB->Security->Users and Right click the loginname, select properties, check schema and DB Role Membership is Correct.



20) Now Open Schemas Node and select the Default Schema of the DB. Right Click and go to properties.


21) Select Permissions and Click Add button in User or Roles Part, it will shows a dialog box and click browse in that dialog box and select the new login name in the list and click ok .


22) It will add to the User or Roles part, Check the Permission grantor is the current schema or not. If it is Current Schema proceed.

23) And select the all row Check box of the Grant column whether the schema is the current schema and click ok.

24) Now Refresh the DB and Open the in the Object Explorer and select table in Query window.

25) Check the Schema is applied or not by enter the table name like

“[Database]. [Schema].[TableName]”.


26) Open Navision-Axapta Object Server and Select Settings-> SQL Server-> Select the Server, DataBase, User Id(newly created login id) and Use this password(give new login password) and Ok.

27) Now start the Server by clicking Start button.

28) Axapta Server Running Properly.

Solution to Connect the Remote SQL Server DB

Just give DSN, Username, Password in the LoginProperty.

Facing Problem while connection Remote Sqlserver DB with Dynamic AX Client using ODBC

Hi,

I connect sql server db(not a default one it is a another db) from Dynamics Axapta 2009.
I used odbc to connect and retrive data.

It was work fine in the server.
but when i was woked in ax client it show an error like this:

1) ODBC operation failed.Unable to log on to the database.

2) [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'ASCENDERS\vasanth'.

3)Object 'OdbcConnection' could not be created


i am also given the user name as sqlserver username "sa" but it take my window authentication.
i don't know how to solve the problem.plz help me with your valuable ideas..

Thanks in advance

Vasanth.A