SQL 2008 Database Security Users/Roles in a mirror

Last Post 07 Sep 2011 06:25 AM by gunneyk. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Paul
New Member
New Member

--
07 Sep 2011 05:11 AM
Guys

Hi newbie here ;-) glad to become a member at last

Qn:

I have a database mirrored with witness and  2 servers, which is working fine...

I have searched to find out if the database security-users/roles are replicated/mirrored ?

As during a mirror failover, i noticed that i had a larger amount of dbase users on 1 server than the other server, this also goes for the dbase roles as well.

If i create a new user/role on the prin***l server, are these replicated/mirrored to the mirror server/dbase ?



These are not Server logins, but sql dbase logins/roles
rm
New Member
New Member

--
07 Sep 2011 05:53 AM
Sql doesn't send new login to mirror partner, you have to create login on both. If you grant db permission to existing login, ensure that login exists on mirror partner and has same sid as on prin***l. If just add existing db user to db role or create new db role, those will be mirrored.
Paul
New Member
New Member

--
07 Sep 2011 06:13 AM
So if a sql server login user  is only created on 1 sql server and mapped to a dbase user, which in turn is part of a dbase role(group) , are you stating that the dbase user and role WILL be mirrored but NOT the SQL server login user.

Or due to NO sql server login user and base user mapping, neither will be mirrored ??
gunneyk
New Member
New Member

--
07 Sep 2011 06:25 AM
Anything you do to a database will be mirrored. Mirroring works at the database level. So users and database roles are at the database level and will in fact be propagated to the mirror. However Logins are at the instance level and will not be automatically propagated. Hope that clears it up.


Acceptable Use Policy
---