• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

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

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
ConquestCS
Asked:
ConquestCS
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
ConquestCSAuthor Commented:
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
 
ConquestCSAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
chapmandewCommented:
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
 
ConquestCSAuthor Commented:
No just some regular databases.
0
 
chapmandewCommented:
Ok, so are you missing the logins to the server or users in the db?
0
 
ConquestCSAuthor Commented:
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
 
chapmandewCommented:
what does this query return when you run it in the db?

select * from sys.database_principals
0
 
wiljeCommented:
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
 
ConquestCSAuthor Commented:
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
 
chapmandewCommented:
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
 
ConquestCSAuthor Commented:
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now