Assigning an existing user to roles on generates invalid SQL.

I had already created a user via:
sqlserver::login

{'v1dbo': password => hiera('versionone:db:v1dbo'), instance => $instance_name, }

I needed to add this user to the 'sysadmin' role: So I changed it to:

sqlserver::login{'v1dbo':
password => hiera('versionone:db:v1dbo'),
instance => $instance_name,
svrroles =>

{ 'public' => 1, 'sysadmin' => '1' }

}

I then received an SQL error.

After looking at the SQL i immediately saw the problem:

ALTER LOGIN [v1dbo] WITH
CHECK_EXPIRATION = OFF,
CHECK_POLICY = ON,
DEFAULT_LANGUAGE = [us_english],
DEFAULT_DATABASE = [master];
IF IS_SRVROLEMEMBER(''public'',''v1dbo'') != 1
BEGIN
ALTER SERVER ROLE public ADD MEMBER [v1dbo];
END
IF IS_SRVROLEMEMBER(''sysadmin'',''v1dbo'') != 1
BEGIN
ALTER SERVER ROLE sysadmin ADD MEMBER [v1dbo];
END

END

---

This is incorrect:
ALTER SERVER ROLE sysadmin ADD MEMBER [v1dbo];

It should be:

ALTER SERVER ROLE [sysadmin] ADD MEMBER [v1dbo];

I looked through the module templates all the role code uses braces properly. However the sqlserver login templates do not.

Here's my diff from my fix, This produces proper SQL and added the roles.

— a/environments/production/modules/sqlserver/templates/create/login.sql.erb
+++ b/environments/production/modules/sqlserver/templates/create/login.sql.erb
@@ -17,9 +17,9 @@ BEGIN
IF IS_SRVROLEMEMBER('<%= role %>','<%= @login %>') != <%= enable_bit %>
BEGIN
<% if enable_bit == '1' || enable_bit == 1 -%>

• ALTER SERVER ROLE <%= role %> ADD MEMBER [<%= @login %>];
  + ALTER SERVER ROLE [<%= role %>] ADD MEMBER [<%= @login %>];
  <% else -%>
• ALTER SERVER ROLE <%= role %> DROP MEMBER [<%= @login %>];
  + ALTER SERVER ROLE [<%= role %>] DROP MEMBER [<%= @login %>];
  <% end -%>
  END
  <% end -%>
  @@ -40,9 +40,9 @@ BEGIN
  DEFAULT_DATABASE = [<%= @default_database %>];
  <% @svrroles.each do |role, enable_bit| -%>
  <% if enable_bit == '1' || enable_bit == 1 -%>
• ALTER SERVER ROLE <%= role %> ADD MEMBER [<%= @login %>];
  + ALTER SERVER ROLE [<%= role %>] ADD MEMBER [<%= @login %>];
  <% else -%>
• ALTER SERVER ROLE <%= role %> DROP MEMBER [<%= @login %>];
  + ALTER SERVER ROLE [<%= role %>] DROP MEMBER [<%= @login %>];
  <% end -%>
  <% end -%>
  END