title: How to grant db_owner permissions to an application role?
body: <p>How can grant all the rights and privileges of the <code>db_owner</code> fixed database role to an application role?</p>
  
  <h1>Short Version</h1>
  
  <p>The command:</p>
  
  <pre class="lang-sql prettyprint-override"><code>GRANT CONTROL ON [DatabaseName] TO [ApplicationRoleName];
  </code></pre>
  
  <p>would be what I want, but it fails with:</p>
  
  <blockquote>
    <p>Msg 15151, Level 16, State 1, Line 23<br>
    Cannot find the object 'DatabaseName', because it does not exist or you do not have permission.</p>
  </blockquote>
  
  <h1>Research Effort</h1>
  
  <p>I'm investigating using SQL Server <strong>Application Roles</strong>.</p>
  
  <ul>
  <li>it's like a user (in that it has a username and password)</li>
  <li>and it's like a role </li>
  </ul>
  
  <p>Once connected to the server, your application runs a stored procedure to <em>"login"</em> itself as the application:</p>
  
  <pre class="lang-sql prettyprint-override"><code>EXECUTE sp_SetAppRole @rolename = 'Contoso.exe', @password =
  'Tm8gaSBkaWRuJ3QganVzdCBiYXNlNjQgZW5jb2RlIGEgcGFzc3dvcmQuIEl0J3Mgb25seSBhbiBleGFtcGxlIQ==';
  </code></pre>
  
  <h2>Permissions of db_owner</h2>
  
  <p>Normally the application logs in as a <strong>user</strong> who is a member of the <a href="https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/server-and-database-roles-in-sql-server" rel="nofollow noreferrer"><strong>db_owner</strong></a> fixed role. The <code>db_owner</code> role has permission:</p>
  
  <ul>
  <li>to everything</li>
  <li>on every table</li>
  <li>every view</li>
  <li>every stored procedure, function</li>
  <li>for all existing objects</li>
  <li>and all objects that will exist in the future</li>
  </ul>
  
  <p>And while:</p>
  
  <ul>
  <li>you can place a <strong>user</strong> into a <strong>database role</strong></li>
  <li>you can place a <strong>user</strong> into an <strong>application role</strong></li>
  </ul>
  
  <p>You cannot place an <strong>application role</strong> into a <strong>database role</strong></p>
  
  <p>So, the question then: how to grant my application role all permissions (i.e. to do everything)?</p>
  
  <h2>Permissions for a role</h2>
  
  <p>So, now is the time to grant permissions to the role. Following <a href="https://www.techonthenet.com/sql_server/grant_revoke.php" rel="nofollow noreferrer">this page's suggestions</a>:</p>
  
  <pre><code>GRANT SELECT, INSERT, UPDATE, DELETE ON Users TO [Contoso.exe];
  </code></pre>
  
  <p>That's interesting and all, but it doesn't grant all privileges - it only grants <code>SELECT</code>, <code>INSERT</code>, <code>UPDATE</code>, and <code>DELETE</code>. I want to grant <strong><em>everything</em></strong> - especially when I don't know what all the privileges are (or could be).</p>
  
  <p>I blindly try:</p>
  
  <pre><code>GRANT ALL ON Users to [Contoso.exe];
  </code></pre>
  
  <p>and the following appeared in the "Messages" tab:</p>
  
  <blockquote>
    <p>The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity. </p>
  </blockquote>
  
  <p>Ok, so granting <code>ALL</code> doesn't grant ALL. That's...terrifying. </p>
  
  <p>So I'm back to:</p>
  
  <pre><code>GRANT SELECT, INSERT, UPDATE, DELETE ON Users TO [Contoso.exe];
  </code></pre>
  
  <p>Except that doesn't grant everything. For example, I happen to know there's an ability to then go on to grant privileges to others (a privilege that <code>db_owner</code> has). So I have to change my statement to:</p>
  
  <pre><code>GRANT SELECT, INSERT, UPDATE, DELETE ON Users TO [Contoso.exe] WITH GRANT OPTION;
  </code></pre>
  
  <p>Ok, so that's closer, but it only applies to one table.</p>
  
  <p>I need something that applies to all tables:</p>
  
  <pre><code>EXECUTE sp_msForEachTable 'GRANT SELECT, INSERT, UPDATE, DELETE ON ? TO [Contoso.exe] WITH GRANT OPTION;';
  </code></pre>
  
  <p>Although, it turns out I missed some privileges:</p>
  
  <ul>
  <li>SELECT ✔️</li>
  <li>INSERT ✔️</li>
  <li>UPDATE ✔️</li>
  <li>DELETE ✔️</li>
  <li>REFERENCES ❌</li>
  <li>ALTER ❌</li>
  </ul>
  
  <p>Sure, I can update my script:</p>
  
  <pre><code>EXECUTE sp_msForEachTable 'GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER ON ? TO [Contoso.exe] WITH GRANT OPTION;';
  </code></pre>
  
  <p>But, rather than this cat-and-mouse guessing game: I want to grant <strong>ALL</strong> permissions.</p>
  
  <h2>ALL is nearly all</h2>
  
  <p>In the warning above, SQL Server notes that <strong>ALL</strong> doesn't grant all. But they do <a href="https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-2017" rel="nofollow noreferrer">document what all <strong>does</strong> grant:</a></p>
  
  <pre><code>| Permission | Table | View | SP | Scalar UDF | Table UDF |
  |------------|-------|------|----|------------|-----------|
  | SELECT     |  ✔️   |  ✔️ |    |            |     ✔️    |
  | INSERT     |  ✔️   |  ✔️ |    |            |     ✔️    |
  | UPDATE     |  ✔️   |  ✔️ |    |            |     ✔️    |
  | DELETE     |  ✔️   |  ✔️ |    |            |     ✔️    |
  | REFERENCES |  ✔️   |  ✔️ |    |    ✔️      |     ✔️    |
  | EXECUTE    |       |      | ✔️ |    ✔️     |            |
  | ALTER      |  ❌   |  ❌ | ❌ |    ❌     |      ❌   | 
  </code></pre>
  
  <h2>CONTROL all permissions</h2>
  
  <p>Turns out that they were all of them deceived. For another permissions was created. One <a href="https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-2017" rel="nofollow noreferrer">permission to rule them all</a>:</p>
  
  <blockquote>
    <ul>
    <li>CONTROL</li>
    </ul>
    
    <p>Confers ownership-like capabilities on the grantee. The grantee effectively has all defined permissions on the securable. A principal that has been granted CONTROL can also grant permissions on the securable. Because the SQL Server security model is hierarchical, CONTROL at a particular scope implicitly includes CONTROL on all the securables under that scope. For example, CONTROL on a database implies all permissions on the database, all permissions on all assemblies in the database, all permissions on all schemas in the database, and all permissions on objects within all schemas within the database.</p>
  </blockquote>
  
  <p>They go on to enumerate the permissions that are implied when you have <strong>CONTROL</strong>:</p>
  
  <ul>
  <li>ALTER</li>
  <li>CONTROL</li>
  <li>DELETE</li>
  <li>EXECUTE</li>
  <li>INSERT</li>
  <li>RECEIVE</li>
  <li>REFERENCES</li>
  <li>CONTROL</li>
  <li>TAKE OWNERSHIP</li>
  <li>UPDATE</li>
  <li>VIEW CHANGE TRACKING</li>
  <li>VIEW DEFINITION</li>
  </ul>
  
  <p>That's much better. Rather than having to know all the permissions, I just grant one. And rather than knowing which permissions are applicable to what kinds of objects, I grant just one. And because of the line:</p>
  
  <blockquote>
    <p>A principal that has been granted CONTROL can also grant permissions on the securable.</p>
  </blockquote>
  
  <p>I don't have to <code>GRANT</code> <code>WITH GRANT</code>:</p>
  
  <pre class="lang-sql prettyprint-override"><code>-- when you have CONTROL you also get WITH GRANT for free
  EXECUTE sp_msForEachTable 'GRANT CONTROL ON ? TO [Contoso.exe];';
  </code></pre>
  
  <h2>To all objects</h2>
  
  <p>My issue is that I need to give CONTROL permission to every object in the database. And any time any new object is added, I have to be sure to go back and add it to the application role.</p>
  
  <p>What I need is the thing hinted to by Microsoft:</p>
  
  <blockquote>
    <p>For example, CONTROL on a database implies all permissions on the database, all permissions on all assemblies in the database, all permissions on all schemas in the database, and all permissions on objects within all schemas within the database.</p>
  </blockquote>
  
  <p>To restate, if you grant <code>CONTROL</code> on a <strong>database</strong>, then you will have all permissions:</p>
  
  <ul>
  <li>on the database
  
  <ul>
  <li>all objects</li>
  <li>all assemblies in the database</li>
  <li>all schemas</li>
  </ul></li>
  </ul>
  
  <p><em>That</em> is what I want. I want to <code>GRANT CONTROL</code> permission on the <code>Grobber</code> database to the <code>[Contoso.exe]</code> application role:</p>
  
  <pre><code>GRANT CONTROL ON Grobber TO [Contoso.exe];
  
  Msg 15151, Level 16, State 1, Line 23
  Cannot find the object 'Grobber ', because it does not exist or you do not have permission.
  </code></pre>
  
  <p>I may have nearly solved my problem, only to be stopped at the 1 yard line. </p>
  
  <p>Or, I may be nowhere near. So I ask on S.O.:</p>
  
  <blockquote>
    <p>How to grant db_owner permissions to another role?</p>
  </blockquote>
  
  <h1>Edit: Warning: <strong>Don't</strong> use application roles - it breaks everything</h1>
  
  <p>When your client logs into an app role, that identity is a persistent property of that connection. And with connection pooling on (as is the preferred and default option in ADO.net, and ADO, and ODBC) that connection stays open for a <em>long</em> time - even after you close the connection.</p>
  
  <p>When your application (i.e. web-server) tries to open a new connection, it grabs one from the connection pool. The first thing that the <strong>SqlConnection</strong> does is try to reset the state of the connection back to default (using <a href="https://stackoverflow.com/a/2924456/577765"><code>sp_reset_connection</code></a>).</p>
  
  <p>One of the things that <code>sp_reset_connection</code> tries to do is undo the fact that you are <strong>app role</strong> user. That is not allowed (because the server doesn't know who you were before). So by using <strong>application roles</strong>, you will suddenly get errors when you attempt to connect to the server.</p>
  
  <p>The only way to <em>"fix"</em> it is to disable connection pooling.</p>
  
  <p>Which is something that you <strong>don't</strong> want to do.</p>
  
  <p>So the solution is to <strong>not</strong> use application roles in a production setting.</p>