{"action":"opened","issue":{"url":"https://api.github.com/repos/sqlcollaborative/dbatools/issues/1813","repository_url":"https://api.github.com/repos/sqlcollaborative/dbatools","labels_url":"https://api.github.com/repos/sqlcollaborative/dbatools/issues/1813/labels{/name}","comments_url":"https://api.github.com/repos/sqlcollaborative/dbatools/issues/1813/comments","events_url":"https://api.github.com/repos/sqlcollaborative/dbatools/issues/1813/events","html_url":"https://github.com/sqlcollaborative/dbatools/issues/1813","id":244758603,"number":1813,"title":"Copy-DBALogin - error processing OFFLINE database on Source","user":{"login":"nvarscar","id":30303784,"avatar_url":"https://avatars3.githubusercontent.com/u/30303784?v=4","gravatar_id":"","url":"https://api.github.com/users/nvarscar","html_url":"https://github.com/nvarscar","followers_url":"https://api.github.com/users/nvarscar/followers","following_url":"https://api.github.com/users/nvarscar/following{/other_user}","gists_url":"https://api.github.com/users/nvarscar/gists{/gist_id}","starred_url":"https://api.github.com/users/nvarscar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/nvarscar/subscriptions","organizations_url":"https://api.github.com/users/nvarscar/orgs","repos_url":"https://api.github.com/users/nvarscar/repos","events_url":"https://api.github.com/users/nvarscar/events{/privacy}","received_events_url":"https://api.github.com/users/nvarscar/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2017-07-21T19:13:01Z","updated_at":"2017-07-21T19:13:01Z","closed_at":null,"body":"## Bug Report\r\nCopy-DBALogin drops existing user in the destination database without recovering it and throws an  error when encountered an OFFLINE (or restoring) database on the Source server:\r\n### General Troubleshooting steps\r\n\r\n- [x] Verified running latest release of dbatools?\r\n- [x] Verified errors are not related to permissions?\r\n- [x] Can duplicate in new/fresh PowerShell.exe session?\r\n\r\n### Version Information\r\n - Operating System (Name|Version): W7, W2012R2\r\n - PowerShell Version: 4\r\n - SQL Server (Edition|Version): 2012 Dev, 2016 Dev\r\n\r\n## Steps to Reproduce\r\nModify the script as per your environment and execute:\r\n```\r\n$database = 'TestMigration'\r\n$login = 'TestLogin'\r\n$source = 'wpg1lsds01,7221'\r\n$destination = 'wpg1lsds01,7220'\r\n$backupfolder = 'D:\\Temp\\'\r\n\r\n$currentdatetime = (Get-Date -Format s).Replace(\":\",\"\")\r\n$fullbackupfile = $backupfolder,$database,$currentdatetime,\".bak\" -join \"\"\r\n$logbackupfile = $backupfolder,$database,$currentdatetime,\".trn\" -join \"\"\r\n\r\nInvoke-sqlcmd -ServerInstance $source -Query \"IF db_id ('$database') IS NULL CREATE DATABASE [$database]; ALTER DATABASE [$database] SET RECOVERY FULL\"\r\nInvoke-sqlcmd -ServerInstance $source -Query \"IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = '$login') CREATE LOGIN [$login] WITH PASSWORD = 'df98gyh23rbAWER@'\"\r\nInvoke-sqlcmd -ServerInstance $source -Query \"USE [$database] IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '$login') CREATE USER [$login] FOR LOGIN [$login]\"\r\n\r\nBackup-SqlDatabase -ServerInstance $source -Database $database -BackupFile $fullbackupfile\r\nInvoke-sqlcmd -ServerInstance $source -Query \"ALTER DATABASE [$database] SET OFFLINE WITH ROLLBACK IMMEDIATE\"\r\n\r\n$fullbackupfile|Restore-DBADatabase -ServerInstance $destination -Database $database\r\n\r\nCopy-DBALogin -Source $source -Destination $destination -Login $login\r\n```\r\n\r\nExpected result:\r\n```\r\nException calling \"EnumDatabasePermissions\" with \"1\" argument(s): \"An exception occurred while executing a Transact-SQL statement or batch.\"\r\nAt line:192 char:4\r\n+             $perms = $sourcedb.EnumDatabasePermissions($username)\r\n+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\r\n    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException\r\n    + FullyQualifiedErrorId : ExecutionFailureException\r\n```\r\n\r\nGet-DbatoolsLog:\r\n\r\n\"Copy-DbaLogin\",\"Attempting to connect to SQL Servers..\"\r\n\"Copy-DbaLogin\",\"Attempting Login Migration\"\r\n\"Copy-Login\",\"Attempting to add TestLogin to WPG1LSDS01\"\r\n\"Copy-Login\",\"Setting TestLogin SID to source username SID\"\r\n\"Copy-Login\",\"Setting login language to us_english\"\r\n\"Copy-Login\",\"Set TestLogin defaultdb to master\"\r\n\"Copy-Login\",\"Successfully added TestLogin to WPG1LSDS01\"\r\n\"Update-SqlPermissions\",\"Successfully performed Grant CONNECT SQL to TestLogin\"\r\n\"Update-SqlPermissions\",\"Dropped user TestLogin (login: TestLogin) from TestMigration on destination. User may own a schema.\"\r\n\r\n## Feature Request\r\nScenario, where it can be used: during a database migration that uses log backup chain, where last log backup has been taken WITH NORECOVERY. Databases will be restored on the target server, but will stay in a \"Restoring\" state on the source server. In current implementation, all the users will be stripped from the target databases when using Copy-DBALogin. Better option would be to keep them intact and afterwards fix them by using Repair-SqlOrphanUser (if needed).\r\n\r\n### Problem to solve\r\nSince the database EXISTS on the source server, but is OFFLINE (or restoring), the script is not able to get proper permissions from the source database and will continue to run as if the user does not exist in the source database, eventually dropping it in the target database. That makes it impossible to use this command in order to copy logins from the source servers where some databases are OFFLINE, but the permissions were transferred with a backup and were planned to be re-instated by matching SIDs. \r\n\r\n\r\n### Additional information\r\nIssue can be resolved by adding verification that source database is accessible:\r\n(line 154, internal\\Update-SqlPermissions.ps1):\r\n`if ($sourcedb -ne $null -and $sourcedb.IsAccessible) {`\r\n\r\nI also noticed, that the the function stops throwing an error after a while (2+ hours from what I've noticed), but I'm not sure why.\r\n\r\nWill try to submit a pull request, but I've never used GitHub before, so it might take a while.\r\nCheers!\r\n"}}
{"actor":{"display_login":"nvarscar"}}