February 17, 2013 at 5:32 pm
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.
February 17, 2013 at 8:07 pm
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.
February 19, 2013 at 2:23 am
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" 😉
May 8, 2013 at 4:48 pm
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).
----------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply