Solved

Moving Sql Server logins

Posted on 2013-01-12
7
193 Views
Last Modified: 2013-01-25
I am in the midst of moving a sql server db from a GoDaddy hosting environment to Amazon Web Services. I have successfully moved the tables, stored procedures, and UDF's. The .Net application using this db is mostly running and displaying records from the new hosting environment.
However some functionality requires a user to login, and the logins were not moved.  The scripts suggested by articles in the Microsoft Knowledge Base all use the "master" db, and I don't have permission to access that, so the scripts fail.
I also get a connection string problem on login attempts, which is puzzling b/c the correct fields are displayed for the functions that don't require login.
I mostly moved the tables using Import/Export from Sql Server Management Studio, and moved the stored procs using scripts from Management Studio.
I'm over my head on the logins, however, and crossing my fingers on the roles getting sorted out too.
0
Comment
Question by:Mark Klein
  • 3
  • 3
7 Comments
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
The only place you can get the logins from is master.

You'll either have to recreate them manually or perhaps you can set up a  ticket with your hosting provider to extract the logins for you (although, I would think that if you're a soon to be ex-customer they may not be very accomodating on that).

Do you have permissions to create logins on the new server ?  If you can, create the logins from scratch with the same names as they were on the old server (a login gets you logged onto the server instance, then you have users in each database that are connected to that login).

If you can get users created, you should be able to do this to fix the orphaned users in your databases:
http://www.fileformat.info/tip/microsoft/sql_orphan_user.htm
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
get permissions or ask your daddy to help,,
btw: who did  set your logins?
you can recreate your logins if you know passwords or can set a new ones




try to use this (is it what did?).
maybe it is set already - just try EXEC master..sp_help_revlogin



How to transfer logins and passwords between instances of SQL Server
http://support.microsoft.com/kb/246133
0
 

Accepted Solution

by:
Mark Klein earned 0 total points
Comment Utility
When I moved the db using the SSMS import/export wizard, the logins were moved as well.My problem is solved
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
hmm.
lucideq: do you mean non of the posted solutions helped you? :)

I am sure they did
if not - what was the solution?

if the as posted - you can not just close this
0
 

Author Comment

by:Mark Klein
Comment Utility
None of the posted solutions helped me. They either involved permissions or going to the master db or other techniques suggested in the MSFT docs.  What did work was a different method of moving the db, as noted. which brought along the logins with it.
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
there is no sql server "different method"  all in the master DB \ the posted solutions are only (unlikely godaddy let you play with the master DBs backup\restore or use 3rd party tools that are using above posted solutions)

if you meant some application logins (not Sql server) stored in your user DB table - it is asome different story

let say- for the sql server logins the above EE posts are correct -

" What did work was a different method of moving the db, as noted" is not for sql server some thing else what we do not know .

if you really think there is some unknown for EE secret method in sql server world to move sql server logins or we missed one of the methods -- > feel free to share details
\
if not -  it is "objection"
\
0
 

Author Closing Comment

by:Mark Klein
Comment Utility
The suggested alternatives were ones I had already tried
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

762 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now