Following the SQL Server security model, users are able to access only database objects—including those created from .NET assemblies—to which they have user rights. The CLR security model extends this by providing control over the types of system resources that can be accessed by .NET code running on the server. CLR security permissions are specified at the time the assembly is created by using the WITH PERMISSION_SET clause of the CREATE ASSEMBLY statement. Table 3-4 summarizes the options for CLR database security permissions that can be applied to SQL Server database objects.
Requires Membership to View
To gain access to this and all member only content, please provide the following information:
By submitting your registration information to SearchSQLServer.com you agree to receive email communications from the TechTarget network of sites, and/or third party content providers that have relationships with TechTarget, based on your topic interests and activity, including updates on new content, event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile, unsubscribing via email or by contacting us here
- Your use of SearchSQLServer.com is governed by our Terms of Use
- We designed our Privacy Policy to provide you with important disclosures about how we collect and use your registration and other information. We encourage you to read the Privacy Policy, and to use it to help make informed decisions.
- If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States.
Table 3-4: CLR Database Object Security Options
Using the SAFE permission restricts all external access. The EXTERNAL_ACCESS permission enables some external access of resources using managed APIs. SQL Server impersonates the caller in order to access external resources. You must have the new EXTERNAL_ACCESS permission in order to create objects with this permission set. The UNSAFE permission is basically an anything-goes type of permission. All system resources can be accessed, and calls to both managed and unmanaged code are allowed. Only system administrators can create objects with UNSAFE permissions.
In addition to using the CREATE ASSEMBLY statement, you can also set the CLR database object permission using the project properties as is shown in Figure 3-16.
Figure 3-16: Setting the CLR permission
To interactively set the CLR permission level, open the project properties by selecting the Project | Properties option from the Visual Studio 2005 menu. Then open the Database tab and click the Permission Level drop-down. The project must be redeployed before the changes will take place.
Managing CLR database objects
As shown in Table 3-5, SQL Server 2005 provides system views that enable you to see the different CLR objects that are being used in the database.
Table 3-5: System Views to Manage CLR Database Objects
The previous tip is from "Developing CLR database objects: 10 tips in 10 minutes," excerpted from Chapter 3 of the book "Microsoft SQL Server 2005: A Developer's Guide" written by Michael Otey and Denielle Otey, courtesy of McGraw-Hill Publishing.
Basic Transact-SQL Programming Constructs
Home: Introduction
This was first published in May 2007
