Uploaded image for project: 'Modules'
  1. Modules
  2. MODULES-3083

Assigning an existing user to roles on generates invalid SQL.

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Normal
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: sqlserver
    • Labels:
      None
    • Template:
    • Epic Link:
    • Story Points:
      1
    • Sprint:
      Windows 2016-08-24

      Description

      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

        Attachments

          Activity

            People

            Assignee:
            erick Erick Banks
            Reporter:
            mterzo Mike Terzo
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Zendesk Support