search

puppetlabs / puppetlabs-sqlserver

PE-only Microsoft SQL module
Other
5 stars 21 forks source link

sql server login permissions #464

Open rismoney opened 9 months ago

rismoney commented 9 months ago

Describe the Bug

trying to use sqlserver::login::permissions but getting error msg failed with error message THROW CAUGHT: (corrective)

Expected Behavior

complete puppet run

Steps to Reproduce

sql::logons::sqlperms:
  myco\sec_sqlserver_dev_adm:
    login: myco\sec_sqlserver_dev_adm
    permissions: ['VIEW SERVER STATE','ALTER TRACE']
    state: 'GRANT'
    instance: MSSQLSERVER

  $sqlperms.each |$perms, $attributes| {
    sqlserver::login::permissions  { $perms:
      *     => $attributes
    }
  }

Environment

Debug: Sqlserver_tsql[login-permission-MSSQLSERVER-myco\sec_sqlserver_dev_adm-GRANT](provider=mssql): Running resource BEGIN TRY
    SET NOCOUNT ON
    DECLARE @sql_text as NVARCHAR(max);
    SET @sql_text = N'USE [master];
DECLARE @perm_state varchar(250), @error_msg varchar(250), @permission varchar(250);

SET @permission = ''VIEW SERVER STATE''
SET @perm_state = ISNULL(
    (SELECT perm.state_desc FROM sys.server_permissions perm
          JOIN sys.server_principals princ ON princ.principal_id = perm.grantee_principal_id
          WHERE princ.type IN (''U'',''S'',''G'')
            AND princ.name = ''myco\sec_sqlserver_dev_adm''
        AND perm.permission_name = @permission),
     ''REVOKE'')
;
SET @error_msg = ''EXPECTED login [myco\sec_sqlserver_dev_adm] to have permission ['' + @permission + ''] with GRANT but got '' + @perm_state;
IF @perm_state != ''GRANT''
            THROW 51000, @error_msg, 10;

SET @permission = ''ALTER TRACE''
SET @perm_state = ISNULL(
    (SELECT perm.state_desc FROM sys.server_permissions perm
          JOIN sys.server_principals princ ON princ.principal_id = perm.grantee_principal_id
          WHERE princ.type IN (''U'',''S'',''G'')
            AND princ.name = ''myco\sec_sqlserver_dev_adm''
        AND perm.permission_name = @permission),
     ''REVOKE'')
;
SET @error_msg = ''EXPECTED login [myco\sec_sqlserver_dev_adm] to have permission ['' + @permission + ''] with GRANT but got '' + @perm_state;
IF @perm_state != ''GRANT''
            THROW 51000, @error_msg, 10;

'
    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
 against MSSQLSERVER
Debug: /Stage[main]/Sql::Logons/Sqlserver::Login::Permissions[myco\sec_sqlserver_dev_adm]/Sqlserver_tsql[login-permission-MSSQLSERVER-myco\sec_sqlserver_dev_adm-GRANT]/onlyif: OnlyIf returned exitstatus of 1
Debug: /Stage[main]/Sql::Logons/Sqlserver::Login::Permissions[myco\sec_sqlserver_dev_adm]/Sqlserver_tsql[login-permission-MSSQLSERVER-myco\sec_sqlserver_dev_adm-GRANT]/onlyif: OnlyIf error: THROW CAUGHT: EXPECTED login [myco\sec_sqlserver_dev_adm] to have permission [ALTER TRACE] with GRANT but got REVOKE
Debug: Sqlserver_tsql[login-permission-MSSQLSERVER-myco\sec_sqlserver_dev_adm-GRANT](provider=mssql): Running resource BEGIN TRY
    SET NOCOUNT ON
    DECLARE @sql_text as NVARCHAR(max);
    SET @sql_text = N'USE [master];
DECLARE @perm_state varchar(250), @error_msg varchar(250), @permission varchar(250);

BEGIN

    IF ''GRANT_WITH_GRANT_OPTION'' = ISNULL(
    (SELECT perm.state_desc FROM sys.server_permissions perm
          JOIN sys.server_principals princ ON princ.principal_id = perm.grantee_principal_id
          WHERE princ.type IN (''U'',''S'',''G'')
            AND princ.name = ''myco\sec_sqlserver_dev_adm''
        AND perm.permission_name = @permission),
     ''REVOKE'')

        REVOKE GRANT OPTION FOR VIEW SERVER STATE TO [myco\sec_sqlserver_dev_adm] CASCADE;

    GRANT VIEW SERVER STATE TO [myco\sec_sqlserver_dev_adm];
END
BEGIN
    SET @perm_state = ISNULL(
    (SELECT perm.state_desc FROM sys.server_permissions perm
          JOIN sys.server_principals princ ON princ.principal_id = perm.grantee_principal_id
          WHERE princ.type IN (''U'',''S'',''G'')
            AND princ.name = ''myco\sec_sqlserver_dev_adm''
        AND perm.permission_name = @permission),
     ''REVOKE'')
;
SET @error_msg = ''EXPECTED login [myco\sec_sqlserver_dev_adm] to have permission ['' + @permission + ''] with GRANT but got '' + @perm_state;
IF @perm_state != ''GRANT''
            THROW 51000, @error_msg, 10;

END

BEGIN

    IF ''GRANT_WITH_GRANT_OPTION'' = ISNULL(
    (SELECT perm.state_desc FROM sys.server_permissions perm
          JOIN sys.server_principals princ ON princ.principal_id = perm.grantee_principal_id
          WHERE princ.type IN (''U'',''S'',''G'')
            AND princ.name = ''myco\sec_sqlserver_dev_adm''
        AND perm.permission_name = @permission),
     ''REVOKE'')

        REVOKE GRANT OPTION FOR ALTER TRACE TO [myco\sec_sqlserver_dev_adm] CASCADE;

    GRANT ALTER TRACE TO [myco\sec_sqlserver_dev_adm];
END
BEGIN
    SET @perm_state = ISNULL(
    (SELECT perm.state_desc FROM sys.server_permissions perm
          JOIN sys.server_principals princ ON princ.principal_id = perm.grantee_principal_id
          WHERE princ.type IN (''U'',''S'',''G'')
            AND princ.name = ''myco\sec_sqlserver_dev_adm''
        AND perm.permission_name = @permission),
     ''REVOKE'')
;
SET @error_msg = ''EXPECTED login [myco\sec_sqlserver_dev_adm] to have permission ['' + @permission + ''] with GRANT but got '' + @perm_state;
IF @perm_state != ''GRANT''
            THROW 51000, @error_msg, 10;

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
 against MSSQLSERVER
Error: Unable to apply changes, failed with error message THROW CAUGHT:
Error: /Stage[main]/Sql::Logons/Sqlserver::Login::Permissions[myco\sec_sqlserver_dev_adm]/Sqlserver_tsql[login-permission-MSSQLSERVER-myco\sec_sqlserver_dev_adm-GRANT]/returns: change from 'notrun' to ['0'] failed: Unable to apply changes, failed with error message THROW CAUGHT:  (corrective)
rismoney commented 9 months ago

stack trace as requested

Debug: Sqlserver_tsql[login-permission-MSSQLSERVER-myco\sec_sqlserver_dev_adm-GRANT](provider=mssql): Running resource BEGIN TRY
    SET NOCOUNT ON
    DECLARE @sql_text as NVARCHAR(max);
    SET @sql_text = N'USE [master];
DECLARE @perm_state varchar(250), @error_msg varchar(250), @permission varchar(250);

SET @permission = ''VIEW SERVER STATE''
SET @perm_state = ISNULL(
    (SELECT perm.state_desc FROM sys.server_permissions perm
          JOIN sys.server_principals princ ON princ.principal_id = perm.grantee_principal_id
          WHERE princ.type IN (''U'',''S'',''G'')
            AND princ.name = ''myco\sec_sqlserver_dev_adm''
        AND perm.permission_name = @permission),
     ''REVOKE'')
;
SET @error_msg = ''EXPECTED login [myco\sec_sqlserver_dev_adm] to have permission ['' + @permission + ''] with GRANT but got '' + @perm_state;
IF @perm_state != ''GRANT''
            THROW 51000, @error_msg, 10;

SET @permission = ''ALTER TRACE''
SET @perm_state = ISNULL(
    (SELECT perm.state_desc FROM sys.server_permissions perm
          JOIN sys.server_principals princ ON princ.principal_id = perm.grantee_principal_id
          WHERE princ.type IN (''U'',''S'',''G'')
            AND princ.name = ''myco\sec_sqlserver_dev_adm''
        AND perm.permission_name = @permission),
     ''REVOKE'')
;
SET @error_msg = ''EXPECTED login [myco\sec_sqlserver_dev_adm] to have permission ['' + @permission + ''] with GRANT but got '' + @perm_state;
IF @perm_state != ''GRANT''
            THROW 51000, @error_msg, 10;

'
    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
 against MSSQLSERVER
Debug: /Stage[main]/Sql::Logons/Sqlserver::Login::Permissions[myco\sec_sqlserver_dev_adm]/Sqlserver_tsql[login-permission-MSSQLSERVER-myco\sec_sqlserver_dev_adm-GRANT]/onlyif: OnlyIf returned exitstatus of 1
Debug: /Stage[main]/Sql::Logons/Sqlserver::Login::Permissions[myco\sec_sqlserver_dev_adm]/Sqlserver_tsql[login-permission-MSSQLSERVER-myco\sec_sqlserver_dev_adm-GRANT]/onlyif: OnlyIf error: THROW CAUGHT: EXPECTED login [myco\sec_sqlserver_dev_adm] to have permission [ALTER TRACE] with GRANT but got REVOKE
Debug: Sqlserver_tsql[login-permission-MSSQLSERVER-myco\sec_sqlserver_dev_adm-GRANT](provider=mssql): Running resource BEGIN TRY
    SET NOCOUNT ON
    DECLARE @sql_text as NVARCHAR(max);
    SET @sql_text = N'USE [master];
DECLARE @perm_state varchar(250), @error_msg varchar(250), @permission varchar(250);

BEGIN

    IF ''GRANT_WITH_GRANT_OPTION'' = ISNULL(
    (SELECT perm.state_desc FROM sys.server_permissions perm
          JOIN sys.server_principals princ ON princ.principal_id = perm.grantee_principal_id
          WHERE princ.type IN (''U'',''S'',''G'')
            AND princ.name = ''myco\sec_sqlserver_dev_adm''
        AND perm.permission_name = @permission),
     ''REVOKE'')

        REVOKE GRANT OPTION FOR VIEW SERVER STATE TO [myco\sec_sqlserver_dev_adm] CASCADE;

    GRANT VIEW SERVER STATE TO [myco\sec_sqlserver_dev_adm];
END
BEGIN
    SET @perm_state = ISNULL(
    (SELECT perm.state_desc FROM sys.server_permissions perm
          JOIN sys.server_principals princ ON princ.principal_id = perm.grantee_principal_id
          WHERE princ.type IN (''U'',''S'',''G'')
            AND princ.name = ''myco\sec_sqlserver_dev_adm''
        AND perm.permission_name = @permission),
     ''REVOKE'')
;
SET @error_msg = ''EXPECTED login [myco\sec_sqlserver_dev_adm] to have permission ['' + @permission + ''] with GRANT but got '' + @perm_state;
IF @perm_state != ''GRANT''
            THROW 51000, @error_msg, 10;

END

BEGIN

    IF ''GRANT_WITH_GRANT_OPTION'' = ISNULL(
    (SELECT perm.state_desc FROM sys.server_permissions perm
          JOIN sys.server_principals princ ON princ.principal_id = perm.grantee_principal_id
          WHERE princ.type IN (''U'',''S'',''G'')
            AND princ.name = ''myco\sec_sqlserver_dev_adm''
        AND perm.permission_name = @permission),
     ''REVOKE'')

        REVOKE GRANT OPTION FOR ALTER TRACE TO [myco\sec_sqlserver_dev_adm] CASCADE;

    GRANT ALTER TRACE TO [myco\sec_sqlserver_dev_adm];
END
BEGIN
    SET @perm_state = ISNULL(
    (SELECT perm.state_desc FROM sys.server_permissions perm
          JOIN sys.server_principals princ ON princ.principal_id = perm.grantee_principal_id
          WHERE princ.type IN (''U'',''S'',''G'')
            AND princ.name = ''myco\sec_sqlserver_dev_adm''
        AND perm.permission_name = @permission),
     ''REVOKE'')
;
SET @error_msg = ''EXPECTED login [myco\sec_sqlserver_dev_adm] to have permission ['' + @permission + ''] with GRANT but got '' + @perm_state;
IF @perm_state != ''GRANT''
            THROW 51000, @error_msg, 10;

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
 against MSSQLSERVER
Error: Unable to apply changes, failed with error message THROW CAUGHT:
C:/ProgramData/PuppetLabs/puppet/cache/lib/puppet/type/sqlserver_tsql.rb:112:in `sync'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:241:in `sync'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:136:in `sync_if_needed'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:90:in `block in perform_changes'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:89:in `each'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:89:in `perform_changes'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction/resource_harness.rb:21:in `evaluate'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:267:in `apply'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:288:in `eval_resource'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:191:in `call'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:191:in `block (2 levels) in evaluate'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:556:in `block in thinmark'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/2.7.0/benchmark.rb:308:in `realtime'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:555:in `thinmark'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:191:in `block in evaluate'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/graph/relationship_graph.rb:122:in `traverse'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction.rb:178:in `evaluate'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/resource/catalog.rb:238:in `block (2 levels) in apply'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:556:in `block in thinmark'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/2.7.0/benchmark.rb:308:in `realtime'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:555:in `thinmark'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/resource/catalog.rb:237:in `block in apply'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util/log.rb:165:in `with_destination'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/transaction/report.rb:150:in `as_logging_destination'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/resource/catalog.rb:236:in `apply'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/configurer.rb:286:in `block (2 levels) in apply_catalog'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:556:in `block in thinmark'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/2.7.0/benchmark.rb:308:in `realtime'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:555:in `thinmark'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/configurer.rb:285:in `block in apply_catalog'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:234:in `block in benchmark'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/2.7.0/benchmark.rb:308:in `realtime'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:233:in `benchmark'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/configurer.rb:284:in `apply_catalog'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/configurer.rb:500:in `run_internal'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/configurer.rb:339:in `run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:69:in `block (6 levels) in run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/context.rb:62:in `override'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet.rb:289:in `override'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:68:in `block (5 levels) in run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/2.7.0/timeout.rb:95:in `block in timeout'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/2.7.0/timeout.rb:105:in `timeout'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:67:in `block (4 levels) in run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent/locker.rb:21:in `lock'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:61:in `block (3 levels) in run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:148:in `with_client'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:57:in `block (2 levels) in run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:113:in `run_in_fork'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:56:in `block in run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/application.rb:172:in `controlled_run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/agent.rb:47:in `run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/application/agent.rb:433:in `onetime'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/application/agent.rb:393:in `block in run_command'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/context.rb:62:in `override'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet.rb:289:in `override'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/application/agent.rb:390:in `run_command'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/application.rb:421:in `block in run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util.rb:745:in `exit_on_fail'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/application.rb:421:in `run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util/command_line.rb:143:in `run'
C:/Program Files/Puppet Labs/Puppet/puppet/lib/ruby/vendor_ruby/puppet/util/command_line.rb:77:in `execute'
C:/Program Files/Puppet Labs/Puppet/puppet/bin/puppet:4:in `<main>'
Error: /Stage[main]/Sql::Logons/Sqlserver::Login::Permissions[myco\sec_sqlserver_dev_adm]/Sqlserver_tsql[login-permission-MSSQLSERVER-myco\sec_sqlserver_dev_adm-GRANT]/returns: change from 'notrun' to ['0'] failed: Unable to apply changes, failed with error message THROW CAUGHT:
Debug: Sqlserver::Login::Permissions[myco\sec_sqlserver_dev_adm]: Resource is being skipped, unscheduling all events