Attaching DB and Mapping Users

[jharwood]

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.

 

[mrdenny]

After the database is attached you can see the user within the database and it's mapped correctly to the server login?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[jharwood]

If I look under Security --> Users under the database the user it there. When I check the properties of the user under General --> Role Members db_datareader and db_datawriter are checked. But If I check the users Properties under Security --> Logins --> User Mapping Nothing is checked.

 

[mrdenny]

That's what I thought, the user isn't mapped to the correct login, because the SIDs are apparently different.

Use this procedures sp_change_users_login to align the user with the correct login.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[jharwood]

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'.

 

[jharwood]

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.

 

[mrdenny]

You don't need the @password parameter. The other options should be all you need.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[MaffewW]

I've got this sproc we use for tracing orphaned users. I cant credit the author (was emailed it), though it has come in handy a fair few times



CREATE PROC dbo.ShowOrphanUsers
AS
BEGIN
CREATE TABLE #Results
(
[Database Name] sysname COLLATE Latin1_General_CI_AS,
[Orphaned User] sysname COLLATE Latin1_General_CI_AS
)

SET NOCOUNT ON

DECLARE @DBName sysname, @Qry nvarchar(4000)

SET @Qry = ''
SET @DBName = ''

WHILE @DBName IS NOT NULL
BEGIN
SET @DBName =
(
SELECT MIN(name)
FROM master..sysdatabases
WHERE name NOT IN
(
'master', 'model', 'tempdb', 'msdb',
'distribution', 'pubs', 'northwind'
)
AND DATABASEPROPERTY(name, 'IsOffline') = 0
AND DATABASEPROPERTY(name, 'IsSuspect') = 0
AND name > @DBName
)

IF @DBName IS NULL BREAK

SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name],
CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User]
FROM ' + QUOTENAME(@DBName) + '..sysusers su
WHERE su.islogin = 1
AND su.name <> ''guest''
AND NOT EXISTS
(
SELECT 1
FROM master..sysxlogins sl
WHERE su.sid = sl.sid
)'

INSERT INTO #Results EXEC (@Qry)
END

SELECT *
FROM #Results
ORDER BY [Database Name], [Orphaned User]
END



GO


Matt

Brighton, UK