Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

When DB restored to different server will database mapping and permissions stay? Expand / Collapse
Author
Message
Posted Sunday, February 17, 2013 5:32 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 1:56 PM
Points: 93, Visits: 463
On Server A I have a database the is overwritten daily by a database on another server [Server B]

After the first restore I map a group called "DOM\Reporting" to the database on Server A and assign read only permissions.

The restored database comes from Server B, this server does not contain an NT Group called "DOM\Reporting".

So when I restore the database mapping has gone.

I know when a SQL Account is used the accounts may need to be synced but I suppose I thought in the case using NT Authentication the security would sync between the Server Level acocunts and the database on restoring...it does not look like this is the case.

So if I am correct would I either have to add the NTGroup to Server B with ReadOnly permissions so they gets transferred, and nothing extra required, or would I have to run a script to map the user to the restored database again and add the permissions -- every time the database is restored.

Ideally I do not want to give the group "DOM\Reporting" any permissions to Server B.

Hopefully I have explained it clear but please let me know if I have not.

thanks for any help.
Post #1421015
Posted Sunday, February 17, 2013 8:07 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:21 PM
Points: 2,842, Visits: 2,423
Database mappings are actually relationships between the syslogins table in the master database and sysusers table in the user database. If there is no record for a Windows user or group in the database when you restore it, then the user or group does not get any permissions and appears as though there is no mapping.

Your thinking is correct - you either add the group to the database on serverB or run a script to add the user to the database after it has been restored to serverA.



Post #1421023
Posted Tuesday, February 19, 2013 2:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:08 AM
Points: 5,952, Visits: 12,830
happycat59 (2/17/2013)
Database mappings are actually relationships between the syslogins table in the master database and sysusers table in the user database.

Note that the correct catalogs now are

sys.server_principals for server level logins
sys.database_principals for database users
sys.database_permissions for database user permissions
sys.database_role_members for database user role membership


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1421484
Posted Wednesday, May 08, 2013 4:48 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:20 PM
Points: 220, Visits: 695
Where you trying to move the data using a linked server connection? You can map the SQL logins in this way using ( I don't mean the Windows logins).
Post #1450834
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse