Describe the Bug
When declaring sqlserver::user::permissions with GRANT permissions, Puppet re-applies the permissions on every run as a corrective change, even though the permissions are already in the desired state.
Expected Behavior
On the second Puppet run (after permissions are already granted), the sqlserver_tsql resource should be in sync and no corrective change should occur.
Steps to Reproduce
- Declare
sqlserver::user::permissions with e.g. permissions: ['SELECT', 'INSERT'] and state: GRANT
- Run Puppet agent — permissions are applied
- Run Puppet agent again — permissions are re-applied as corrective
Root Cause
The onlyif query template templates/query/user/permission_exists.sql.epp iterates over permissions but never assigns the SQL variable @permission:
<% $permissions.each |$requested_permission| { %>
<% $permission = $requested_permission.upcase %>
<%= epp('sqlserver/snippets/user/permission/exists.sql.epp', ...) %>
<% } %>The Puppet variable $permission is set, but the SQL variable @permission stays NULL. Since permission_name = NULL is never TRUE in SQL Server, the subquery always returns NULL, ISNULL coerces it to 'REVOKE', and the check always sees a mismatch — causing Puppet to re-grant every time.
The create template (templates/create/user/permission.sql.epp) and the role equivalent (templates/query/role/permission_exists.sql.epp) both correctly set @permission. This is the same class of bug as #464 / #500 which fixed the login permission template.
Fix
Add the missing SET @permission = '<%= $permission %>'; line in the query template, matching the create template.
Describe the Bug
When declaring
sqlserver::user::permissionswith GRANT permissions, Puppet re-applies the permissions on every run as a corrective change, even though the permissions are already in the desired state.Expected Behavior
On the second Puppet run (after permissions are already granted), the
sqlserver_tsqlresource should be in sync and no corrective change should occur.Steps to Reproduce
sqlserver::user::permissionswith e.g.permissions: ['SELECT', 'INSERT']andstate: GRANTRoot Cause
The
onlyifquery templatetemplates/query/user/permission_exists.sql.eppiterates over permissions but never assigns the SQL variable@permission:The Puppet variable
$permissionis set, but the SQL variable@permissionstays NULL. Sincepermission_name = NULLis never TRUE in SQL Server, the subquery always returns NULL,ISNULLcoerces it to'REVOKE', and the check always sees a mismatch — causing Puppet to re-grant every time.The create template (
templates/create/user/permission.sql.epp) and the role equivalent (templates/query/role/permission_exists.sql.epp) both correctly set@permission. This is the same class of bug as #464 / #500 which fixed the login permission template.Fix
Add the missing
SET @permission = '<%= $permission %>';line in the query template, matching the create template.