Solved

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

Posted on 2009-07-14
12
247 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
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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 Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

773 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