Attach remote DB file: Attaching DB and Mapping Users

I am trying to attach a db to SQL Server 2005 and assign a User to the DB that can read and write. I am using one user (CreateDBs) to attach the DB and a second user (TestUser) to do the reading and writing. I run the following code and the DB is attached but I get the following error when I run the code below.

********************ERROR********************

System.Data.SqlClient.SqlException: Cannot open database "hello2" requested by the login. The login failed.

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)

at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)

at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at NewSite.submit_Click(Object sender, EventArgs e) in X:\Inetpub\RMA4\NewSite.aspx.vb:line 120

********************CODE********************

'Need to attach to SQL Server

Dim objConnAsNew SqlConnection("Server=Web-testbed\SQLExpress;uid=CreateDBs;pwd=ABC123;database=master")

objConn.Open()

Dim objCmdAsNew SqlCommand("EXEC sp_attach_db @.dbname = N'" & sitename &"', @.filename1 = N'X:\sites\" & sitename &"\Database\" & sitename &".mdf', @.filename2 = N'X:\sites\" & sitename &"\Database\" & sitename &"_log.ldf'", objConn)

objCmd.CommandType = CommandType.Text

objCmd.ExecuteNonQuery()

objConn.Close()

objConn =New SqlConnection("Server=Web-testbed\SQLExpress;uid=CreateDBs;pwd=ABC123;database=" & sitename)

objConn.Open()

objCmd =New SqlCommand("EXEC sp_addrolemember db_datareader, TestUser", objConn)

objCmd.CommandType = CommandType.Text

objCmd.ExecuteNonQuery()

objConn.Close()

objConn =New SqlConnection("Server=Web-testbed\SQLExpress;uid=CreateDBs;pwd=ABC123;database=" & sitename)

objConn.Open()

objCmd =New SqlCommand("EXEC sp_addrolemember db_datawriter, TestUser", objConn)

objCmd.CommandType = CommandType.Text

objCmd.ExecuteNonQuery()

objConn.Close()

It still doesn't work if I use sa as the user. I know I am missing a step, what is it?

Maybe I am not going about this the right way. What I am trying to do is; I have detached a DB that was created in SQL Server 2005 and copied the the .mdf and .ldf files. I renamed them and put them into a foder. When the user enters information into a web form the mdf and ldf files are copied into a new directory and renamed. Then attached to SQL Server 2005. After the code below is run the DB shows up and the user that I need to have read and write capabilities is in the security group.

Dim objConnAsNew SqlConnection("Server=Web-testbed\SQLExpress;uid=CreateDBs;pwd=ABC123;database=master")

objConn.Open()

objCmd.CommandType = CommandType.Text

objCmd.ExecuteNonQuery()

objConn.Close()

But later in the program it runs the following code and it errors on the Open command.

sqlString ="INSERT INTO [DeviceData] (inservice, devicename, serialnum, disksize, ninstalledchan, nchannels, ngroups) VALUES (@.inservice, @.devicename, @.serialnum, @.disksize, @.ninstalledchan, @.nchannels, @.ngroups)"

Using connAsNew SqlConnection("Server=Web-testbed\SQLExpress;Initial Catalog=" & sitename &";UID=TestUser;Password=user;")

'Using conn As New SqlConnection("Data Source=Webserver;Initial Catalog=" & sitename & ";UID=TestUser;Password=user;")

Using cmdAsNew SqlCommand(sqlString, conn)

cmd.CommandType = CommandType.Text

cmd.Parameters.Add(New SqlParameter("@.inservice", inservice.Text))

cmd.Parameters.Add(New SqlParameter("@.devicename", devicename.Text))

cmd.Parameters.Add(New SqlParameter("@.serialnum", serialnum.Text))

cmd.Parameters.Add(New SqlParameter("@.disksize", disksize.Text))

cmd.Parameters.Add(New SqlParameter("@.ninstalledchan", ninstalledchan.Text))

cmd.Parameters.Add(New SqlParameter("@.nchannels", nchannels.Text))

cmd.Parameters.Add(New SqlParameter("@.ngroups", ngroups.Text))

conn.Open()

cmd.ExecuteNonQuery()

conn.Close()

EndUsing

*********Error Message***********

System.Data.SqlClient.SqlException: Cannot open database "NewSite9" requested by the login. The login failed.