Browse DevX
Sign up for e-mail newsletters from DevX


Writing Managed Stored Procedures in SQL Server 2005 : Page 4

Are you tired of struggling with T-SQL to encapsulate the logic your applications need to get data into and out of SQL Server? Now you can bypass SQL altogether, and write logic that runs directly within SQL Server in your favorite .NET language.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Enforcing Permissions for Executing Assemblies
A managed assembly's security is scoped to the user identity of the account that loaded it to SQL Server. Data and code (assemblies) owned by a particular user or role are isolated from data and code owned by another user or user role unless you explicitly grant access.

The owner of an assembly can, in turn, grant the permission to reference that assembly to other database users or roles. An assembly can succeed in invoking the methods of another assembly only if one of the following conditions is true.

  • The referenced assembly is owned by the same user
  • The user owning the referenced assembly has granted the permission to the user owning the referencing assembly
When loading an assembly into SQL Server, you have the ability to specify one of three different levels of security in which your code can run:

  • SAFE—This is the default permission set. With this mode, the assembly can only do computation and data access within the server via the in-process managed provider.
  • EXTERNAL_ACCESS—This permission set is typically useful in scenarios where the code needs to access resources outside the server such as files, network, registry and environment variables. Whenever the server accesses an external resource, it impersonates the security context of the user calling the managed code.
  • UNSAFE—SQL Server uses this permission set in situations where an assembly is not verifiably safe or requires additional access to restricted resources, such as the Win32 API.
To specify a given security level, you modify the syntax of the Create Assembly statement, adding the WITH PERMISSION_SET clause as shown below.

CREATE ASSEMBLY <AssemblyName> FROM <AssemblyPath> WITH PERMISSION_SET = <PermissionSetName)

Transact-SQL vs. Managed Code
The ability to write server side objects using a .NET compliant language gives developers a new way to write stored procedures, but that flexibility also poses some important challenges. Developers now must decide when to use T-SQL and when to use a managed language. There's no straight answer to this question; the appropriate method depends on the particular situation, but here are some guidelines.

T-SQL is best used in situations where the code primarily performs data access with little or no procedural logic. Managed code is best suited for CPU-intensive computations and server side objects where you need to create complex logic. You might want to consider using managed code when you want to leverage the rich features and object model supported by the .NET Framework's Base Class Library (BCL). The location in which the code executes is also an important factor to consider. By using this ability to write stored procedures using a .NET language, you can move some of your middle tier code to stored procedures and other server side objects. Doing that lets you take full advantage of the processing power of the database server. On the other hand, you may wish to avoid placing processor-intensive tasks on your database server. Most client machines today are very powerful, and you may wish to take advantage of this processing power by placing as much code as possible on the client.

Thiru Thangarathinam works at Intel Corporation in Chandler, Arizona. He's a Microsoft MVP who specializes in architecting, designing, and developing distributed enterprise-class applications using .NET-related technologies. He is the author of the books "Professional ASP.NET 2.0 XML" and "Professional ASP.NET 2.0 Databases" from Wrox press and has coauthored a number of books on .NET-related technologies. He is a frequent contributor to leading technology-related online publications.
Thanks for your registration, follow us on our social networks to keep up-to-date