I am running SQL Server Copy Database Wizard.
It should be noted that the NT AUTHORITY\SYSTEM statement, which, as I thought, should have the authority to run whatever it wants.
How can we provide sufficient privileges for NT AUTHORITY\SYSTEM ? I already tried:
GRANT EXECUTE ON xp_regread TO public GRANT EXECUTE ON xp_regread TO [NT AUTHORITY\SYSTEM]
And run the following shows in which he 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 AUTHORITY\SYSTEM EXECUTE
The following error has occurred:
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 AUTHORITY\SYSTEM 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 a tool that we need to use because we do not have access to the remote server, and because of the unsuccessful creation of the import-export wizard.