March 28, 2008 at 3:51 am
I am trying to create an MI copy of a live database as a daily backup from Server A to Server B. Scripting this via a maintenance plan TSQL job is fine in that the MI database is restoring to Server B.
However, being MI, I wish to ensure a login account (say Andytest) is reestablished each time with the datareader role.
The TSQL job I am running looks like this:-
USE [Copied_MI]
GO
EXEC sp_addrolemember N'db_datareader', N'Andytest'
GO
The script appears to complete successfully, but when checking the login Andytest, there is no datareader authority present.
However if I subsequently run the exact same commands via the Query screen it works fine and the authority is in place as I would hope.
Anyone got any ideas as to what I might be missing on the task within the job. I think the connection to the instance is fine.
Thanks
March 28, 2008 at 3:59 am
UDBNT (3/28/2008)
I am trying to create an MI copy of a live database as a daily backup from Server A to Server B. Scripting this via a maintenance plan TSQL job is fine in that the MI database is restoring to Server B.However, being MI, I wish to ensure a login account (say Andytest) is reestablished each time with the datareader role.
The TSQL job I am running looks like this:-
USE [Copied_MI]
GO
EXEC sp_addrolemember N'db_datareader', N'Andytest'
GO
The script appears to complete successfully, but when checking the login Andytest, there is no datareader authority present.
However if I subsequently run the exact same commands via the Query screen it works fine and the authority is in place as I would hope.
Anyone got any ideas as to what I might be missing on the task within the job. I think the connection to the instance is fine.
Thanks
My first thought is that you have to re-establish the login and the user AndyTest as the sids will be different (i.e. orphaned user). Why it works though when you run the above command through QA later on, I'm not sure. Even so, try this and see if it works:
USE [Copied_MI]
GO
EXEC sp_change_users_login N'auto_fix', N'Andytest'
GO
March 28, 2008 at 5:15 am
Thanks for the SID bit (I had forgottent that).
However, when I run the following:-
USE [Copy_MI]
GO
EXEC sp_change_users_login N'auto_fix', N'Andytest'
GO
USE [Copy_MI]
GO
CREATE USER [Andytest] FOR LOGIN [Andytest]
GO
USE [Copy_MI]
GO
EXEC sp_addrolemember N'db_datareader', N'Andytest'
GO
The Datareader role gets assigned to Master NOT Copy_MI.
Any thoughts as to why?
Cheers
March 28, 2008 at 6:42 am
Latest status.
Running via Query screen the following syntax works fine and a subsequent check on the Security details for User Andytest show it has datareader defined correctly for Copy_MI database.
Run the same code via a Maintenance Plan TSQL task and this time it runs but puts the datareader against Master NOT Copy_MI.
I'm thinking a bug? Any thoughts.
USE [Master]
GO
RESTORE DATABASE (Details here removed for brevity)
GO
USE [Copy_MI]
GO
EXEC sp_change_users_login N'auto_fix', N'Andytest'
GO
USE [Copy_MI]
GO
CREATE USER [Andytest] FOR LOGIN [Andytest]
GO
USE [Copy_MI]
GO
EXEC sp_addrolemember N'db_datareader', N'Andytest'
March 28, 2008 at 8:27 am
Go it (I think!)
Created a Maintenance Plan job to run TSQL via a remote connection to backup database on Server A to put backup file on Server B via UNC path.
This then links into the next task (on Success), which runs a SQL Server Agent Job.
This job, Restores the database as Copy_MI on Server B then runs the fix of the Security account Andytest, creates a new user Andytest against Copy_MI database then finally adds the Datareader role against the login
What I still don't understand is why this second task seems to require it to be a SQL Agent Job as opposed to running as a second TSQL step in a defined Maintenance Plan.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply