sql server login permissions #464
Description
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
- Version 8.x
- Platform Win2022 SQL 2022
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)