Solved

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

Posted on 2009-07-14
12
238 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

12 Experts available now in Live!

Get 1:1 Help Now