Q

SQLCLR permission sets

SQL Server 2005 expert Adam Machanic discusses SQL CLR permission sets in this expert response.

What are SQLCLR permission sets?
Native support for .NET routines in SQL Server is an important enhancement to the product, but one that brings many concerns. One such worry is security: Can a DBA ensure that an assembly will not try to access resources that it should not? For instance, what if a third-party library is purchased?

The solution to these problems is permission sets, which are named collections of access rights that can be assigned to an assembly. If an assembly tries to use a namespace or class not allowed by the permission set to which it belongs, an exception will be thrown and execution will stop. Through careful use of appropriate permission sets, a DBA can enforce access rights, even for non-trusted assemblies.

SQL Server includes three built-in permission sets: SAFE, EXTERNAL ACCESS, and UNSAFE. SAFE allows access to .NET's mathematical, string manipulation, and other standard libraries, as well as ADO.NET for data access. EXTERNAL ACCESS extends the rights allowed by the SAFE permission set, adding classes from namespaces such as System.IO for file system operations, and System.Net for network operations. The UNSAFE permission set is unrestricted—all namespaces and classes available within SQL Server can be accessed, and even unmanaged code is allowed. Be careful with the UNSAFE permission set!

Although the default permission sets are not editable, it is possible to create your own, as discussed in Niels Berglund's this blog post.


Do you have comments on this Ask the Expert Q&A? Let us know.
This was first published in December 2005

Dig deeper on Microsoft SQL Server 2005

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close