Database snapshot permissions on a mirror

Question

Database snapshot permissions on a mirror

Adam Bean

One Orange Chip

Points: 26631
More actions
February 22, 2008 at 2:33 pm

#179327

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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

Andy sql SSCrazy Eights Points: 9496 More actions · Answer 6

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.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 7

Adam Bean

One Orange Chip

Points: 26631

February 26, 2008 at 7:04 am

#781969

Perfect, thanks guys.

Deepak-405225 SSCertifiable Points: 6120 More actions · Answer 8

You may also refer this below link,

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

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