Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 598
  • Last Modified:

Move SQL Database to new server, user transfers but can't assign permissions

I have a database I'm developing an ASP application on a staging server for that I now need to move to a production server.   I detached the database and copied over to the new server (SQL express 2008).  The database uses SQL Authentication to access via the ODBC connection and the original user transfers over from the old server when I look under the users of the database.  However it appears the password doesn't follow. Under the users of the database there isn't a way to change/modify the user and it doesn't show as a user under the SQL server.

If I try and add the user under the server it will not allow me to map the user to the database with an error that the user already exists.  What and how do I need to make this work?  Sorry I don't have that much experience with SQL server (obviously).
0
rjilek
Asked:
rjilek
  • 2
  • 2
1 Solution
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
First, Delete the user from the database.
Second, Add the user under the server to the database.

0
 
rjilekAuthor Commented:
That kind of basically worked in a complicated way.  I could not just delete the user as it was an owner of some schema.  So I had to create a temp user to assign the schema to and then allow me to delete the user under the database.  Then I could create the user again under the server and reassign the schema to it under the database.  Anyway it worked and thanks the the hints and the quick reply!
0
 
rjilekAuthor Commented:
The solution was critical in getting me to think in the right direction.
0
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
There are other ways of doing this, a system stored procedure(can't remember the name) can map a migrated user to a current user, but not sure if that is available for sql express.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now