Visual Studio 2008 has this nifty tool for scripting databases. If you have an existing data connection in your Server Explorer, you can right-click on a database, select “Publish to Provider”, and voila, you have your entire database scripted to a file. Now, you may think “Hey, I can run this on another server, and it will allow me to move this database quickly and easily.” However, you would only be half-right.

Don’t forget to edit that script such that the Login Name, User Name, and Database (or server) roles are set within the script.

This basically translates to using the following (changing the appropriate key words)

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N’DATABASE NAME’)
DROP DATABASE [DATABASE NAME]
GO

CREATE DATABASE [DATABASE NAME]
GO

USE [DATABASE NAME]
****** Object: User [USERNAME] Script Date: 06/30/2008 10:48:21 ******
CREATE USER [USERNAME] FOR LOGIN [LOGINNAME] WITH DEFAULT_SCHEMA=[SCHEMA]
GO

* Set up default database for user *
sp_defaultdb ‘USERNAME’ , [DATABASENAME]
GO
sp_changegroup ‘DATABASE ROLE’, ‘USERNAME’
GO