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-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:22 AM
Points: 100, Visits: 506
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
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:54 PM
Points: 3,087, Visits: 2,686
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: Today @ 4:34 AM
Points: 6,754, Visits: 14,402
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 8, 2013 4:48 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 6:42 PM
Points: 459, Visits: 1,065
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