June 18, 2015 at 3:32 am
Hi,
We are doing a database upgrade to 2014 from 2005. I would like to know is it possible to copy across the system databases from 2005 to 2014 without any adverse effects.
We have to do this as we have certificates on 2005 which use these tables.
I would really appreciate any comments/suggestions/things to know you would like to share on this.
Thank you.
J.
June 18, 2015 at 3:34 am
I strongly recommend against moving the master database from one instance to another. I suspect it won't even be possible to move them up-version.
For your certificates, back them up and then restore them to the new instance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2015 at 3:52 am
Don't do that. Script the server-scoped objects you want to move instead.
This tool will help you: https://github.com/billgraziano/ScriptSqlConfig. It doesn't script all possible object types, but it's a start.
-- Gianluca Sartori
June 18, 2015 at 6:06 am
Thanks for the advice. We thought as much. Genuinely - we are having problems with the certificates on out new 2014 box. Here is some detail of the problem:
Background: We need users to check if an agent job is running and if not start it up (need to use master.dbo.xp_sqlagent_enum_jobs; msdb.dbo.sysjobs; *msdb.dbo.sp_start_job)
*It never gets to run msdb.dbo.sp_start_job as the try catch ends sproc
Steps
•Certificate created from scratch (on calling database)
•Backup certificate
•Create Certificate on master from backup file
•Certificate login created from cert
•Certificate login given sysadmin
•Security group given execute permissions to stored procedure
•Stored procedure signed with certificate
•Execute as user (The user is a member of the windows security assigned execute permissions of sproc)
•When the procedure runs the following is outputted:
Msg 229, Level 14, State 5, Procedure xp_sqlagent_enum_jobs, Line 84
The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure spRunSSISImportProcess, Line 199
The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.
We have tried multiple certs and 2 databases one of which was created from scratch the other was a restored DB from a 2005 SQL box on a 2014 SQL box with the same output.
Any ideas? Help is much appreciated here. This is really slowing us up...
J.
June 19, 2015 at 2:32 am
We got a solution. We set the relevant database to "TRUSTWORTHY" - the certs then started to work properly. I'm just investigating now if there are any negative ramifications of switching trustworthy to "ON" on a database...
June 19, 2015 at 11:07 pm
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply