Creating a database and defining tables programmatically in C#

Question

I'm trying to define a database and create a table in it using a C# program. I'm using SQL server and an SQL script file that I read and execute one statement at a time, with 'GO' being the statement delimiter. The basic technique is as outlined in responses here and on MSDN - submit DDL commands via SqlCommand and ExecuteNonQuery.

Basically, I'm processing each command in the SQL script file using ExecuteNonQuery. For the USE statement, however, I'm parsing out the database name, then plugging it into a working connection string that I use in other areas of the app. I then use SqlConnection to connect to that database.

The script contains a USE [master] and then the CREATE DATABASE and a bunch of ALTERs, followed by a USE for the database just created. That's where I'm having the problem. The first USE works fine, the database is created, and each of the ALTER statements works fine too. When I hit the second USE statement, for the database that I just successfully created, I get an SqlException telling me the login failed for the user name I provided. If I leave off the user and password from the connection string, I just get the same exception telling me the login failed for user ''. BTW, before I process the second USE statement, I close the original connection (to [master]), since I'm intending to establish a new one to the new database.

The very next statement in the script is CREATE USER to define the user in the newly created database. How do I do that if I can't connect to it? Or maybe the question is how do I connect to that new database so that I can define users, and more importantly, the table(s)? I'm not sure that it's all that important for the user to be defined in the new database, but it's definitely required for the table(s) to be defined and I can't do that unless I can connect to that database.

Any thoughts? I feel like I'm missing something obvious here, but I've been beating my head against this wall for a while now.

Answer 1

OK, first and foremost, you don't need to close the connection. USE [DatabaseName] will switch the context and you will be on the database.

Below is the scripts to create database and login programmatically. However, the very first run, the system must use your Windows credential to create database. Subsequent runs can be executed through the new database user.

USE [master]

SET QUOTED_IDENTIFIER ON

GO

--1. Create Database

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'YourDatabase')
BEGIN
    ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [YourDatabase]
END

/* Create Marketplace_v1_1 database.                                                          */
USE [master]
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE DATABASE "YourDatabase"  
GO
USE [master]
GO
ALTER DATABASE [YourDatabase] MODIFY FILE ( NAME = N'YourDatabase', FILEGROWTH = 10%)
GO


--2. Create Login/USer

USE [YourDatabase]
GO
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'YourDatabaseUserName')
    DROP USER [YourDatabaseUserName]
GO

USE [master]
GO

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'YourDatabaseUserName')
    DROP LOGIN [YourDatabaseUserName]
GO  


CREATE LOGIN [YourDatabaseUserName] 
    WITH PASSWORD=N'YourPassword'
       , DEFAULT_DATABASE=[YourDatabase]
       , CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [YourDatabase]
GO

--3. Grant Permission to your user, you can remove unecessary permissions depending on your requirements

--3.1 Permissions To read/write data to tables
CREATE USER [YourDatabaseUserName] FOR LOGIN [YourDatabaseUserName]
EXEC sp_addrolemember N'db_datareader', N'YourDatabaseUserName'
EXEC sp_addrolemember N'db_datawriter', N'YourDatabaseUserName'
GO

--3.2. Permissions to backup/restore database

EXEC master..sp_addsrvrolemember @loginame = N'YourDatabaseUserName', @rolename = N'bulkadmin'
GO
EXEC master..sp_addsrvrolemember @loginame = N'YourDatabaseUserName', @rolename = N'dbcreator'
GO
USE [YourDatabase]
GO
EXEC sp_addrolemember N'db_owner', N'YourDatabaseUserName'
GO
EXEC sys.sp_addsrvrolemember @loginame = N'YourDatabaseUserName', @rolename = N'processadmin'
GO
USE [YourDatabase]
GO
EXEC sp_addrolemember N'db_backupoperator', N'YourDatabaseUserName'
GO


--3.3. Grant permissions to operate on database objects
USE [YourDatabase]
GO
GRANT CREATE TABLE TO [YourDatabaseUserName]
GO
USE [YourDatabase]
GO
GRANT AUTHENTICATE TO [YourDatabaseUserName]
GO
USE [YourDatabase]
GO
GRANT BACKUP DATABASE TO [YourDatabaseUserName]
GO
USE [YourDatabase]
GO
GRANT BACKUP LOG TO [YourDatabaseUserName]
GO
USE [YourDatabase]
GO
GRANT CREATE FUNCTION TO [YourDatabaseUserName]
GO
USE [YourDatabase]
GO
GRANT CREATE PROCEDURE TO [YourDatabaseUserName]
GO
USE [YourDatabase]
GO
GRANT CREATE TYPE TO [YourDatabaseUserName]
GO
USE [YourDatabase]
GO
GRANT CREATE VIEW TO [YourDatabaseUserName]
GO
USE [YourDatabase]
GO
GRANT DELETE TO [YourDatabaseUserName]
GO
USE [YourDatabase]
GO
GRANT EXECUTE TO [YourDatabaseUserName]
GO
USE [YourDatabase]
GO
GRANT INSERT TO [YourDatabaseUserName]
GO
USE [YourDatabase]
GO
GRANT SELECT TO [YourDatabaseUserName]
GO
USE [YourDatabase]
GO
GRANT UPDATE TO [YourDatabaseUserName]
GO
USE [YourDatabase]
GO
GRANT ALTER ON SCHEMA::dbo TO [YourDatabaseUserName]
GO
USE [YourDatabase]
GO
GRANT REFERENCES TO [YourDatabaseUserName]
GO
USE [YourDatabase]
GO
GRANT ALTER ANY ROLE TO [YourDatabaseUserName]
GO
USE [YourDatabase]
GO
GRANT CONTROL TO [YourDatabaseUserName]
GO