Author: sylvia

When I was given my work laptop, it already had MS SQL server and the management studio installed. It was set up to use windows authentication. On the other hand, our populated test/development database used a SQL server authentication (ie with username/password). I needed to create a login for a test user on my local MS SQL server to achieve compatibility.

Create a test user login

First, in SQL server management studio, open a new query window by right clicking on the server name and select New Query. (This will create a query window for the master database). Create a login for user ${db.username} with ${db.password}.

USE master;
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'${db.username}')
CREATE LOGIN [${db.username}] WITH PASSWORD = '${db.password}';

Then to add the new user to the test database ${db.name}.

USE [${db.name}];
CREATE USER [${db.username}] FOR LOGIN [${db.username}] WITH DEFAULT_SCHEMA=[dbo];
ALTER ROLE [db_owner] ADD MEMBER [${db.username}];
ALTER ROLE [db_datareader] ADD MEMBER [${db.username}];
ALTER ROLE [db_datawriter] ADD MEMBER [${db.username}];

Mixed authentication

My SQL server was originally set to only allow windows authentication. I needed the SQL server instance to accept mixed authentication instead. (Mixed authentication allows both windows and sql server style authentications). In SQL server management studio, right click on the server name, then choose Properties. On the Security page, under Server authentication, select SQL Server and Windows Authentication mode.

You need to restart the SQL server instance to activate this feature. You can restart the server by right clicking on the server name again, and choose Restart. However, you might want to activate TCP/IP authentication before restarting.

Enable TCP/IP Protocol

Lastly, open the SQL server configuration manager (via the windows start menu). Under SQL Server Network Configuration -> Protocols for MSSQLSERVER, toggle the status for TCP/IP to Enabled. You can restart the server now by going to SQL Server Services (in the left navigation pane), right click on SQL Server (MSSQLSERVER) and choose Restart.

One of the first tasks I set myself at my new role was to create my own local development database. Currently, the developers were developing against a populated database shared amongst developers and testers.

I couldn’t find the scripts to recreate the database schema from scratch. The Copy Database Wizard in MS SQL Server Management Studio (SMS) failed with authentication problems. Backup Database stored the sqldump on the remote server hard disk which I didn’t have access to. I was ready to give up when a colleague from another team told me about the scripting functionality in SMS.

Database Objects Scripts

In SMS, right click on the database you want to replicate, then select Tasks -> Generate Scripts. A dialogue will pop up, where you can select the database objects you want to copy. You can copy specific tables, views, stored procedures etc, as shown in the following diagram.

Then in the next dialogue, under the advanced options, there are two options I found especially useful.

• Script DROP and CREATE
• Types of data to script: options are schema only, data only and both schema and data

Once the script was generated, it can be applied in a query window for the target database.