puppetlabs / puppetlabs/puppetlabs-sqlserver
sqlserver::user::permissions always applied as corrective
Nobody has claimed this yet.
- Dominant language
- Ruby
- Stars
- 5
- Forks
- 22
- Avg merge
- 19h 50m
- Merged PRs (30d)
- 4
Description
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::permissionswith e.g.permissions: ['SELECT', 'INSERT']andstate: 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.
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with templates/query/user/permission_exists.sql.epp and compare it with templates/create/user/permission.sql.epp and templates/query/role/permission_exists.sql.epp. Check the permission variable setup, then run the stated Puppet reproduction twice. Done means the second run reports the sqlserver_tsql resource in sync without reapplying the GRANT permissions.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 1/5
- Estimated time
- Under an hour
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 92/100