Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Assemblies with EXTERNAL_ACCESS are, through some convoluted path, falling under the EXECUTE AS path. The problem appears when the 'dbo' cannot be mapped to a valid login. dbo's login is the login with the SID the <code>owner_sid</code> value in <a href="http://msdn.microsoft.com/en-us/library/ms178534.aspx"><code>sys.databases</code></a>. Unless an AUTHORIZATION clause was used in CREATE DATABASE the owner_sid is the login sid of the principal issuing the CREATE DATABASE statement. Most times this is the Windows SID of the user logged in and issuing the CREATE DATABASE. With this knowledge in hand one can easily envision the problems that may arise:</p> <ul> <li>copy database: CREATE DATABASE was issued on machine A by an user local to A (ie. <code>MachineA\user</code> or <code>DomainA\user</code>) then the database was copied to machine B (via backup/restore or via file copy). The owner_sid is preserved by file copy as well as by backup/restore, this on machine B the owner_sid is invalid. Everything requiring EXECUTE As fails, including loading assemblies from the database.</li> <li>tombstoned account. CREATE DATABASE was issued by an user that has left the company. The AD account is deleted and all of the sudden EXECUTE AS mysteriously fails, including loading assemblies.</li> <li>disconnected laptop. CREATE DATABASE was issues when the laptop was connected in the work network. At home you can log in using Windows cached credentials, but EXECUTE AS wants to connect to the unavailable AD and fails. Loading assemblies also fails. Problems mysteriously resolves itself next day at work, when you're again within reach of AD.</li> <li>spotty AD connectivity. The EXECUTE AS does not uses system cached credentials and connects to the AD every time. If the AD connectivity has issues (timeout, errors) those issues manifest as similar timeouts and errors in EXECUTE AS, including loading assemblies</li> </ul> <p>All these issues can be diagnosed by simply running: <code>EXECUTE AS USER = 'dbo';</code> in the context of the problem db. It it fails with an error then the cause of your assembly load problems is the EXECUTE AS context of <code>dbo</code>.</p> <p>The solution is trivial, simply force the <code>owner_sid</code> to a valid login. <code>sa</code> is the usually the best candidate:</p> <pre><code>ALTER AUTHORIZATION ON DATABASE::[&lt;dbanme&gt;] TO sa; </code></pre> <p>The funny thing is that the database may seem to be perfectly healthy; tables are available and you can run selects, updates, deletes, create and drop tables etc. Only certain components require <code>EXECUTE AS</code>:</p> <ul> <li>code signing requires the code to have an EXECUTE AS clause</li> <li>assembly validation</li> <li>explicit <code>EXECUTE AS</code> in T-SQL code</li> <li>Service Broker message delivery (including Query Notifications)</li> </ul> <p>The latter is the most often seen culprit, as applications relying on <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx"><code>SqlDependency</code></a> all of a sudden seem to stop working, or have random problems. This article explains how <code>SqlDependency</code> ultimately depends on EXECUTE AS: <a href="http://rusanu.com/2006/06/17/the-mysterious-notification/">The Mysterious Notification</a></p>
    singulars
    1. This table or related slice is empty.
    1. POSQL Server stops loading assembly
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USIan Boyd
    1. USRemus Rusanu
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSQL Server stops loading assembly
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI think you are the one who give the best answer. I will try out EXECUTE AS USER = 'dbo' next time I run into the problem. I believe this is the issue.
      singulars
      1. PO
      1. USHarvey Kwok
    2. COEXECUTE AS USER = 'dbo'; Works perfectly well for me. However your suggested cure: ALTER AUTHORIZATION ON DATABASE::[<dbanme>] TO sa; Worked like a charm - so thanks :)
      singulars
      1. PO
      1. USPaul Hutchinson
    3. CO@PaulHutchinson: hm... that is interesting and kind of ruins my understanding of what's going on. Good to know it helped you, but I'll need to do some future digg to understand your case...
      singulars
      1. PO
      1. USRemus Rusanu
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload