Attaching DB and Mapping Users

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Attaching DB and Mapping Users

Author

Topic

harwoodj
Starting Member

3 Posts

Posted - 2007-10-16 : 13:52:42

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 objConn As New SqlConnection("Server=Web-testbed\SQLExpress;uid=CreateDBs;pwd=ABC123;database=master")
objConn.Open()

Dim objCmd As New 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()

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 conn As New 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 cmd As New 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()

End Using

End Using

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

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

Login failed for user 'TestUser'.

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 356

Any Help would be appreciated on this.

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 15:01:06

Quite a lot of code there, most of which I haven't read.

When you attach a Database to a [new] Server then the Server Logins are not associated with the Users in that database until you do some "re-synchronise steps"

So any Login that you used before, EVEN IF that Login-ID exists on the new server, will not have the original permissions (until you do the "re-synchronise steps")

Don't know if that is maybe the answer to your problem? but thought I would mention it just in case.

Kristen

harwoodj
Starting Member

3 Posts

Posted - 2007-10-16 : 15:48:34

I added:
EXEC sp_change_users_login @Action = 'Update_One', @UserNamePattern = 'TestUser', @LoginName = 'TestUser', @Password = 'user';

But now I get:
System.Data.SqlClient.SqlException: An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 16:15:21

My "normal" syntax for that scenario is:


EXEC MyDatabaseName.dbo.sp_change_users_login 'Update_One', 'TestUser', 'TestUser'

which looks to be pretty much the same as you already have

Kristen

harwoodj
Starting Member

3 Posts

Posted - 2007-10-16 : 16:21:08

I had just relized what I has did I was still logged into the master db and not the db that TestUser was in. Thanks for the help.

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 16:22:54

That'll do it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources