Knowledge Article
CDB - How to create a SQL Server database and user id using SQL Server Authentication.
How to create a SQL Server database and user id using SQL Server Authentication.
BMC AMI Capacity Management
Capacity Management Database
BMC AMI Cost Management, BMC Capacity Optimization for Mainframes, Visualizer
What are the steps required to create a SQL Server database and user id using SQL Server Authentication? |
Prerequisites: 1. SQL Server 2005 Database components installed and running on the Server Machine. 2. SQL Server's Management Studio installed on either the Server Machine or a Client Workstation. 3. SQL Server Database Instance (Server) to be used is registered in the Management Studio client to be used and a connection can be established to the Server. Step 1: Start Management Studio and log on to the Database instance that will be used for the new database. Step 2: Expand the Security option from the Object Explorer tree, right click on Logins and click on New Login. Fill in the General page like below substituting your login name and password. Step 3: Click on the Server Roles page and select dbcreator. Then click the OK button to create the login id. Step 4: The new login should appear on the Logins page. Step 5: Expand Databases from the Object Explorer tree, right click on Databases and select New Database from the menu. Fill in the General page like below, substituting your database name and Initial Size for the Data and Log components. Step 6: Click on the Options page select Simple from the Recovery model pull down menu. Full recovery is not necessary for a Visualizer database. It will just add unneeded overhead. Click OK to create the new database. Step 7: Expand the tree on the database just created. Expand Security under the database, right click on Users and select New User. Click on the … button next to the Login name field and click Browse in the Select Login box. Scroll down the list and select the Login that was created in Step 3. Click OK twice to get back to the Database User – New screen. Step 8: Select db_owner from both the Owned Schemas and Role Members boxes. Click OK to create the user. Step 9: Right click on Schemas under “the new database”>Security and click on New Schema. Type in a name in the Schema name: field. Click on the Search… button next to the Schema owner field. Click on the Browse… button in the Search Roles and Users box. Then scroll down to the new userid that was created and select it. Click OK three times to save the new schema. Step 10: Double click on the user created in Step 8. Click on the … next to Default Schema. Click the Browse… button on the Select Schema dialog box. In the Browse for Objects box, scroll down the list and select the schema created in the previous step. Click OK twice to get back to the Database User - <userid> screen. Step 11: The Database User should look like this, click OK to save the changes. Step 12: Close Databases and expand Security from the Object Explorer tree. Double click on the new login id, click on User Mapping and scroll down the list to find the new database. The new database should be selected with the Database User and Default Schema that were created above filled in. Once the SQL Server userid and database have been created an ODBC Data Source will need to be created so that Visualizer/Automator can communicate to the database. Steps 13 – 23 outline how to accomplish this. Step 13: From the Windows Start menu select Administrative Tools>Data Sources (ODBC). Step 14: In the ODBC Data Source Administrator click on the System DSN tab and click the Add button. Step 15: Select SQL Native Client from the selection box and click Finish. Step 16: Fill in the following fields and then click Next>. Name: A meaningful and unique name for this Data Source (DSN). Description: Enter a description for this DSN or leave it blank (not required). Server: The name of the SQL Server that contains the database to connect to. Step 17: To use the SQL Server userid created above click on the “With SQL Server authentication…” option and enter the SQL Server userid and password. Then click Next>. Step 18: Click on the Change the default database to check box, select the database from the pull down list and click Next>. Step 19: Take all the defaults in this box. Click Finish. Step 20: Click on Test Data Source… Step 21: If test was successful, you have successfully connected to the database using ODBC. If the test failed, go back and review the previous setting and revise where needed. Click OK. Step 22: Click Ok. Step 23: Click OK. The new database, userid and ODBC Data Source Name (DSN) are now ready to be used in Automator. |
Attachment(s):