Solved

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

Posted on 2009-07-14
12
249 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

821 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