sql server - xp_regread() returned error 5, 'Access is denied.'

Question

I'm running the SQL Server Copy Database Wizard.

Of note is that the Operator is NT AUTHORITYSYSTEM, which I thought should have the authority to run whatever it wants.

How can we grant sufficient privileges to NT AUTHORITYSYSTEM? I have already tried:

GRANT EXECUTE ON xp_regread TO public
GRANT EXECUTE ON xp_regread TO [NT AUTHORITYSYSTEM]

And running the following shows that it worked.

SELECT
grantee_principal.name AS [Grantee]
, prmssn.permission_name
FROM
sys.all_objects AS xproc
INNER JOIN sys.database_permissions AS prmssn ON prmssn.major_id=xproc.object_id AND prmssn.minor_id=0 AND prmssn.class=1
INNER JOIN sys.database_principals AS grantee_principal ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(xproc.type='X')and(xproc.name=N'xp_regread' and SCHEMA_NAME(xproc.schema_id)=N'sys')

Output:

Grantee                     permission_name
public                      EXECUTE
NT AUTHORITYSYSTEM         EXECUTE

The following error occurs:

  Event Name: OnError
 Message: An exception occurred while executing a Transact-SQL statement or batch.
StackTrace:    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand, SqlCommand& command)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command)
   at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
   at Microsoft.SqlServer.Management.Smo.SqlServer.GetData(EnumResult erParent)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Initialize(Boolean allProperties)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname, Boolean useDefaultValue)
   at Microsoft.SqlServer.Management.Smo.PropertyCollection.RetrieveProperty(Int32 index, Boolean useDefaultOnMissingValue)
   at Microsoft.SqlServer.Management.Smo.PropertyCollection.GetValueWithNullReplacement(String propertyName, Boolean throwOnNullValue, Boolean useDefaultOnMissingValue)
   at Microsoft.SqlServer.Management.Smo.Server.get_InstanceName()
   at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.MappedLogin.RefreshData()
   at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.MappedLogin.CheckDirty()
   at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.GetDatabaseLogins()
   at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.UpdateObjectsToCopy()
InnerException-->xp_regread() returned error 5, 'Access is denied.'
xp_regread() returned error 5, 'Access is denied.'
xp_regread() returned error 5, 'Access is denied.'
StackTrace:    at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand, SqlCommand& command)
 Operator: NT AUTHORITYSYSTEM
 Source Name: SQLSERVER2008R2_Transfer Objects Task
 Source ID: {9D0562F4-FCC9-498D-A2A9-FC9E5F3B681B}
 Execution ID: {23FF505D-00D3-4F84-8B9D-D9EC962C78D2}
 Start Time: 2015-04-17 7:23:24 PM
 End Time: 2015-04-17 7:23:24 PM
 Data Code: 0

This is the tool we need to use, because we don't have access to the remote server, and because the Import-Export Wizard failed.

Answer 1

You might need to add the account to the sysadmin server role, which allows a member to perform every activity.

For 2008r2, execute this command:

EXEC sp_addsrvrolemember 'NT AUTHORITYSYSTEM', 'sysadmin';