Solved

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

Posted on 2011-03-09
4
594 Views
Last Modified: 2012-05-11
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
Comment
Question by:rjilek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 17

Accepted Solution

by:
Daniel Reynolds earned 500 total points
ID: 35089031
First, Delete the user from the database.
Second, Add the user under the server to the database.

0
 

Author Comment

by:rjilek
ID: 35089115
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
 

Author Closing Comment

by:rjilek
ID: 35089125
The solution was critical in getting me to think in the right direction.
0
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 35089243
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question