Solved

Is it possible to keep SQL Logins after detaching/attaching databases?

Posted on 2009-07-14
12
254 Views
Last Modified: 2012-05-07
Hello.

I am in the process of moving some databases from one storage drive to another. So it will be still on the same server, but a different drive letter.

At first I was detaching the database, robocopy the data/log files to the new drive, then reattaching the database.

The problem I noticed is when I reattached the db, it did not have all the SQL login accounts associated with the database anymore. Just some default ones, dbo, etc.

Does anyone know how I can move a database to another drive but keep the sql logins intact?

Thanks!
0
Comment
Question by:ConquestCS
[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
12 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24854913
seems bit odd,

run this
EXEC sp_change_users_login 'Report'
from your db

take a look at the following blog

http://askmesql.blogspot.com/2009/05/how-to-fix-orphaned-sql-server-users.html
0
 

Author Comment

by:ConquestCS
ID: 24854944
it is weird. I just tested it again on another database just to make sure. the logins still disappear.

Also i tried the command you mentioned above and it returned no results.

thanks.
0
 

Author Comment

by:ConquestCS
ID: 24854963
do you have a sql 2005 test environ to try it out? I am on SQL 2005 SP2.

i attached a sql login to a database.

then i clicked "detach database" then "attach database"

when i went into the security folder in SQL management studio, the sql login I attached earlier was no longer there.

maybe its a bug?
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 60

Expert Comment

by:chapmandew
ID: 24855348
Are you moving the master db as well?  If so, there are likely some other things you must do before you can so so
0
 

Author Comment

by:ConquestCS
ID: 24855372
No just some regular databases.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24855408
Ok, so are you missing the logins to the server or users in the db?
0
 

Author Comment

by:ConquestCS
ID: 24855412
The users in the db. The users are still listed under the Server Security folder, but are missing under the Security folder inside the database itself.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24855784
what does this query return when you run it in the db?

select * from sys.database_principals
0
 
LVL 7

Accepted Solution

by:
wilje earned 250 total points
ID: 24855914
If you are attaching the database back to the same instance of SQL Server, all of the logins exist (logins are stored in master).  The database users are stored in the database and can be found using the following query while connected to that database:
SELECT * FROM sys.database_principals;
The users are not removed from the database - so, either they did not exist before you detached and attached the database - or the login you are using does not have appropriate permissions.
0
 

Author Comment

by:ConquestCS
ID: 24856730
I must be on crack.

When I attached/detached 15 dbs, all of them lost a certain login account (hence creating this question).

And when I tried a test one earlier today the same problem was happening (adding a different login account to a test database).

I ran the select * from sys.database_principals on a test database and the results show all the logins associated with the database.

BUT now when I detach/attach, the logins stay there!!! what the heck?? I am not sure what is going on?

Maybe I did something wrong on my side?
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 250 total points
ID: 24858065
I think you're all good...just maybe something funky with Managemnet Studio...not refreshing or something.  If they're in database_principals, then they're there...
0
 

Author Closing Comment

by:ConquestCS
ID: 31603526
thanks for the help guys. i split it evenly since both of you helped me understand about what the database stores for user login information.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

631 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