Uploaded image for project: 'Modules'
  1. Modules
  2. MODULES-2323

sqlserver: Unable to delete/absent sqlserver::login

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Normal
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: sqlserver
    • Labels:
      None
    • Template:
    • Acceptance Criteria:
      Hide

      Delete an SQL Login without error
      Does not error if the request SQL login does not exist

      Show
      Delete an SQL Login without error Does not error if the request SQL login does not exist
    • Epic Link:
    • Story Points:
      1
    • Sprint:
      Windows 2016-08-24

      Description

      1. Successfully created login1 by applying the below manifest:

      sqlserver::config{ 'MSSQLSERVER':
      admin_user => 'sa',
      admin_pass => 'Pupp3t1@',
      }
      sqlserver::login{'login1':
      instance => 'MSSQLSERVER',
      password => 'Passw0rd!!',
      login_type => 'SQL_LOGIN',
      check_expiration => 'true',
      }
      

      2. Login with sa account and make sure login is not been using/login, attempted to delete the login1 by apply the below manifest:

      sqlserver::config{ 'MSSQLSERVER':
        admin_user => 'sa',
        admin_pass => 'Pupp3t1@',
      }
      sqlserver::login{'login1':
        instance => 'MSSQLSERVER',
        ensure => 'absent'
      }
      

      Results: login1 is not deleted, applying the manifest throws the errors as below:

      PS C:\Users\Administrator> puppet apply C:\cygwin64\tmp\deletelogin1.txt
      Notice: Compiled catalog for uvmh1uqnfs6qtjh.delivery.puppetlabs.net in environment production in 0.56 seconds
      Error: BEGIN TRY
          DECLARE @sql_text as NVARCHAR(max);
          SET @sql_text = N'USE master
      GO
      IF exists(select * from sys.sql_logins where name = ''login1'')
      BEGIN
          -- need to add logic to kill all possible connections if any exists,
          -- possible force flag to prevent from happening during transaction if user would prefer to wait
          execute sp_droplogin @loginame = [login1]
      END
      '
          EXECUTE sp_executesql @sql_text;
      END TRY
      BEGIN CATCH
          DECLARE @msg as VARCHAR(max);
          SELECT @msg = 'THROW CAUGHT: ' + ERROR_MESSAGE();
          THROW 51000, @msg, 10
      END CATCH
       returned 1 instead of one of [0]
      Error: /Stage[main]/Main/Sqlserver::Login[login1]/Sqlserver_tsql[login-MSSQLSERVER-login1]/returns: change from notrun t
      o 0 failed: BEGIN TRY
          DECLARE @sql_text as NVARCHAR(max);
          SET @sql_text = N'USE master
      GO
      IF exists(select * from sys.sql_logins where name = ''login1'')
      BEGIN
          -- need to add logic to kill all possible connections if any exists,
          -- possible force flag to prevent from happening during transaction if user would prefer to wait
          execute sp_droplogin @loginame = [login1]
      END
      '
          EXECUTE sp_executesql @sql_text;
      END TRY
      BEGIN CATCH
          DECLARE @msg as VARCHAR(max);
          SELECT @msg = 'THROW CAUGHT: ' + ERROR_MESSAGE();
          THROW 51000, @msg, 10
      END CATCH
       returned 1 instead of one of [0]
      Notice: Applied catalog in 0.10 seconds
      PS C:\Users\Administrator>
      

      3. Modified the manifest to be:

      sqlserver::config{ 'MSSQLSERVER':
      admin_user => 'sa',
      admin_pass => 'Pupp3t1@',
      }
      sqlserver::login{'login1':
      login => 'login1',
      instance => 'MSSQLSERVER',
      ensure => 'absent',
      }
      

      4. Apply the modified manifest in step3, same errors and the login1 is still not deleted

      PS C:\Users\Administrator> puppet apply C:\cygwin64\tmp\deletelogin1_again.txt
      Notice: Compiled catalog for uvmh1uqnfs6qtjh.delivery.puppetlabs.net in environment production in 0.56 seconds
      Error: BEGIN TRY
          DECLARE @sql_text as NVARCHAR(max);
          SET @sql_text = N'USE master
      GO
      IF exists(select * from sys.sql_logins where name = ''login1'')
      BEGIN
          -- need to add logic to kill all possible connections if any exists,
          -- possible force flag to prevent from happening during transaction if user would prefer to wait
          execute sp_droplogin @loginame = [login1]
      END
      '
          EXECUTE sp_executesql @sql_text;
      END TRY
      BEGIN CATCH
          DECLARE @msg as VARCHAR(max);
          SELECT @msg = 'THROW CAUGHT: ' + ERROR_MESSAGE();
          THROW 51000, @msg, 10
      END CATCH
       returned 1 instead of one of [0]
      Error: /Stage[main]/Main/Sqlserver::Login[login1]/Sqlserver_tsql[login-MSSQLSERVER-login1]/returns: change from notrun t
      o 0 failed: BEGIN TRY
          DECLARE @sql_text as NVARCHAR(max);
          SET @sql_text = N'USE master
      GO
      IF exists(select * from sys.sql_logins where name = ''login1'')
      BEGIN
          -- need to add logic to kill all possible connections if any exists,
          -- possible force flag to prevent from happening during transaction if user would prefer to wait
          execute sp_droplogin @loginame = [login1]
      END
      '
          EXECUTE sp_executesql @sql_text;
      END TRY
      BEGIN CATCH
          DECLARE @msg as VARCHAR(max);
          SELECT @msg = 'THROW CAUGHT: ' + ERROR_MESSAGE();
          THROW 51000, @msg, 10
      END CATCH
       returned 1 instead of one of [0]
      Notice: Applied catalog in 0.10 seconds
      PS C:\Users\Administrator>
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              erick Erick Banks
              Reporter:
              phong Phong Ly
              QA Contact:
              Phong Ly
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Zendesk Support