Why does an out of the box process connect to the database as SA.

When connecting to a database while an out of the box process runs, it connects to the database as SA.

This occurs when Loadspec loads an assembly. This is because the USP_Loadspec stored procedure is set to EXECUTE AS OWNER, and the owner of USP_Loadspec is dbo, which has the login 'sa.' More information about the EXECUTE AS clause is available on MSDN (https://msdn.microsoft.com/en-us/library/ms188354.aspx).

This is by design, because USP_Loadspec must be able to execute with elevated permissions in order to make the database changes required when loading a spec, such as altering the schema. Most processes in CRM do not execute as sa, however. Rather, they EXECUTE AS CALLER (which is the default), so that they will be executed with the limited permissions of the user that calls the function, such as the AppPool user.

It is also important to note that the initial invocation to execute the USP_Loadspec is run by the AppPool user.  

Steps to Duplicate

1. Run an out of the base process.
2. Review the SQL.
3. Note that you connect to the database as SA.

Environment

 Blackbaud CRM
 4.0
 4.0.166

Was this article helpful?