locked
loss of permissions from detach and attach RRS feed

  • Question

  • Question.
    Would moving databases from one lun to another via Detach, Copy and ReAttach affect SQL Server 2005 Security.  IE should there be any consideration to whether an account is lossed by moving a database from one drive letter to another.

    Friday, September 4, 2009 5:15 PM

Answers

  • Hi ACollins.

    I think your response to the initial answers may have caused some confusion when you referred to instance.

    But in any case, detaching and re-attaching a database to the same instance cannot lose or orphan users. Since the server principals are stored in master, which does not change, and the database principals are stored in the database, which you haven't changed (only taken them offline and then online)

    The orphaned users issue only arises when you break the link between the server and database principals, and that's by 'switching' master when you move to a different server.

    This operation is low-risk, but make sure you have a backup anyway.

    HTH

    Ewan


    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    • Marked as answer by ACollins74 Tuesday, September 8, 2009 4:31 PM
    Monday, September 7, 2009 1:52 PM

All replies

  • Permissions granted in the database is stored inside the database - no loss there. But you have a connection between the user in the database and the login which is stored in the master database. There's plenty fo information about security architecture in Books Online, so I suggest you start reading up on Logins (server principals) and users (database principals) if needed.


    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Sunday, September 6, 2009 9:48 AM
  • As already told, logins are stored in master database  so when you move your db from one instance to another instance you have to transfer logins

    How to transfer logins between instances of SQL Server 2005
    http://support.microsoft.com/kb/918992

    Sunday, September 6, 2009 2:09 PM
  • The question states nothing about moving databases from one instance to another so thanks for the effort but thats not helpful at all.

    The question I have is regarding an instance move from Drive a to Drive B in a clustered environment.

    Is there a possiblity, and i mean any chance.   That after a move from one drive to the next, that  a single users mapping would be lost to a database.  Would you be so confident as to put your paycheck on it.

    Remember, moving an instance from one drive to another within a clustered environment is not a trivial task involving many steps.

    Monday, September 7, 2009 11:59 AM
  • Hello ACollins

    There is no possibility that detaching and attaching a database on the same server will remove a user's permissions.

    This can easily be tested on a non-prod SQL Server. The fact that it is clustered is not relevant.

    Recommended way to do this is to use ALTER DATABASE, then set offline, then copy the files, then bring back online.

    HTH

    Ewan



    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Monday, September 7, 2009 12:05 PM
  • <<Would you be so confident as to put your paycheck on it. >>

    Of course not. If my employer would suggest this, I would change employer - or I would probably not work there in the first place. Everybody who has been in this industry know that things happens - including unexpected things. However, I can say how things work, assuming I have enough information.

    You say you are to move "an instance" from one drive to another. This is not enough information. What exactly will you move? The database files for some database? How, using detach/attach? Etc. The answer will depend on all these details. Ewan gave a good answer - assuming you refer to detach/attach.


    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Monday, September 7, 2009 1:18 PM
  • maybe i'm going aroud this all wrong.  I already know 'how things work'.  I am simply trying to see if anyone else out there in the community had experienced a loss of user mappings to a database when they moved it from one drive to another.  (this is a forum right?)

    perhaps the question should be, what might be a cause a users mappings to a database to vanish, (not orphaned), during a move from one drive to another. 

     

    Monday, September 7, 2009 1:46 PM
  • Hi ACollins.

    I think your response to the initial answers may have caused some confusion when you referred to instance.

    But in any case, detaching and re-attaching a database to the same instance cannot lose or orphan users. Since the server principals are stored in master, which does not change, and the database principals are stored in the database, which you haven't changed (only taken them offline and then online)

    The orphaned users issue only arises when you break the link between the server and database principals, and that's by 'switching' master when you move to a different server.

    This operation is low-risk, but make sure you have a backup anyway.

    HTH

    Ewan


    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    • Marked as answer by ACollins74 Tuesday, September 8, 2009 4:31 PM
    Monday, September 7, 2009 1:52 PM
  • I can only repeat what Ewan stated. If you detach (or backup) and then attach (or restore) *in the same instance*, then the connection between users (sys.database_principals) and logins (sys.server_principals) will be intact. Assuming you didn't mess about with your logins during this process, of course. The actual permissions are stored inside the database - so there can be no loss of assigned permissions (permissions assigned to a user in the database).
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Monday, September 7, 2009 3:16 PM