Unable to drop user after restore...

[lidds]

I am writing a utility to my application that will allow the user to create a backup of an sql database and restore that backup on a different server. The restore option will also create the SQL login and user to that database that my software is using. This all works fine, apart from the fact that when the database is backed up is save the sql user in the backup, so when I restore the backup a user exists in the database, but no corresponding login. So what I planned on doing was dropping the user from the restored database and recreating both the login and user again.

However when I try to drop the user from the restored database I get the following error:

Cannot drop the login 'RISDEMO', because it does not exist or you do not have permission.

The code I am using to drop the user the following:

SQLConnString = "Server='myserver';Trusted_Connection=True"
Dim myConn As New SqlClient.SqlConnection(SQLConnString)
            Dim cmd As New SqlClient.SqlCommand("", myConn)
            Dim sb As Text.StringBuilder = Nothing

Try
                sb = New Text.StringBuilder
                sb.Append("USE myDatabase")
                sb.Append(ControlChars.CrLf & "GO")
                sb.Append(ControlChars.CrLf & "DROP USER myUser")
                sb.Append(ControlChars.CrLf & "GO")
                myConn.Open()
                cmd.CommandText = sb.ToString
                cmd.ExecuteNonQuery()
            Catch ex As SqlClient.SqlException
            Finally
                myConn.Close()
            End Try

I understand the error, and I think it is a permissions thing. However I am using windows authentication and obviously have enough permissions to restore the database. The other thing that is strange is if I use SQL Server Management Studio still login using windows authentication I can delete the user????

Any help would be apreshiated as I have spent hours on this.

Thanks

Simon

 

[lidds]

The alternative is to create a backup but with out users, if that is possible. I am using SMO, but I could not find anything on the internet on how to do this?

 

[jmcilhinney]

Are you absolutely sure that it's that part of the code that's causing the issue? The fact that you're using a DROP USER statement but the error message say that it can't drop a login seems odd.