Database snapshot permissions on a mirror

  • I'm getting ready to implement mirroring and had a question about permissions on a snapshot ...

    Upon database restore, the userid's no longer match up to the serverid's, thus you need to fix any orphan users (sp_change_users_login).

    Ok that's great, that fixes our permissions for now. Yet as soon as you enable mirroring, the database can no longer be altered. So the next step to read the data is to take a snapshot. Once you've taken a snapshot, it retains the permissions in the database at the time of snapshot. If you've fixed your users prior to enabling mirroring, than the snapshot should retain the same permissions prior to mirroring.

    Now, the problem I'm getting at is, what happens when you have new DDL objects, new users, etc.? You can't alter the mirrored database, nor the snapshot. So minus having a sysadmin login, how exactly do permissions stay updated on a snapshot?

    Thanks

  • Drop the snapshot and create a new one?

    Perhaps I'm misunderstanding you, but after you've created a snapshot, neither new data nor new objects created after that time will be reflected in the snapshot. Permissions on new objects aren't really relevant, as the new objects also aren't visible in the snapshot.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (2/23/2008)


    Drop the snapshot and create a new one?

    Perhaps I'm misunderstanding you, but after you've created a snapshot, neither new data nor new objects created after that time will be reflected in the snapshot. Permissions on new objects aren't really relevant, as the new objects also aren't visible in the snapshot.

    Yes, I plan on taking a daily snapshot of the mirror. The question being is what happens when say a new database user is created on the principal? Obviously it will get mirrored over and on the mirrored side you'd have to setup the login. Problem being is, how do you fix the permissions? The database is inaccessible while mirrored, so you can never do anything to fix them.

    Also, to mirror a database, the database has to be restored with NORECOVERY ... meaning, when and where do you get to fix your database users to match up to the server logins?

  • You mean how to match up the newly created login with the database user?

    Fairly easy. The link between a login and the database user is the SID. You can script the login from the priinciple, including the SID and password (If it's a SQL login), and apply that to the mirror server. That way the logion will match up with the DB user and, next time you take a snapshot, the login will have access to the snapshots.

    CREATE LOGIN MyNewLogin WITH PASSWORD = 'V3ry53cr3t', SID = <SID here>

    If the login was created on the principal with the same SID, the user will automatically match up. No permissions fixing needed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Oh wow, I didn't realize you could specify the SID on a login create ... that's news to me.

    So for NT groups, I don't need to worry about this eh?

  • Create the login with a matching SID and you should be good to go.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • AFAIK, for a server-level Principal (i.e. a login to the MSSQL Server, not a database): if it is a Windows-level Principal, then the SID of the login will be the Windows SID.

    To rewrite that Microsoft-speak - when you create a new login to MSSQL Server, and the login is a Domain User, then the SID created in MSSQL is the Domain User's Active Directory SID (guaranteed to be unique in the domain). Thus, as long as all your users are from the same Domain, you should not have any problems with SIDs in MSSQL.

  • Perfect, thanks guys.

  • You may also refer this below link,

    http://www.mssqltips.com/tip.asp?tip=1166

    [font="Verdana"]- Deepak[/font]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply