Friday, February 1, 2013

Preparing a SQL database for CLR assembly loading

A few times, I’ve had to install CLR assemblies into a Microsoft SQL database.

In order to do so, the deploying user must be granted certain permissions.

Here is the script I use.

/*===========================================================
  THIS SCRIPT WILL PREPARE A SERVER FOR LOADING AN ASSEMBLY 
  BY GRANTING RIGHTS FOR THE SPECIFIED USER TO LOAD
  ASSEMBLIES OF ALL PERMISSION SETS
  AND SETTING THE DATABASE TO TRUSTWORTHY TO 
  ALLOW THE ASSEMBLY TO BE LOADED INTO CLR
  NOTE: MAKE SURE YOU REPLACE THE <Username> PLACEHOLDER
    WITH THE USERNAME YOU WISH TO ELEVATE PERMISSIONS FOR
  NOTE 2: A USER CANNOT ELEVATE THEIR OWN PERMISSIONS. THIS SCRIPT
    SHOULD BE RUN BY A DIFFERENT USER THAN THE ONE BEING ELEVATED
  NOTE 3: RUN IN DATABASE YOU WANT TO LOAD THE ASSEMBLY
  ===========================================================*/
       DECLARE @user NVARCHAR(50)
       DECLARE @tsql NVARCHAR(MAX)
       DECLARE @error INT
 
       SELECT @user = '<Username>'
 
       PRINT 'STEP PREPARE SERVER AND DATABASE FOR ASSEMBLY LOADING'
       PRINT 'SERVER  :   [' + @@SERVERNAME + ']'
       PRINT 'DATEBASE:   [' + db_name() + ']'
       PRINT 'USER    :   ' + @user
       PRINT 'START TIME: ' + convert(varchar(max), getdate())
 
       PRINT ''
       PRINT 'GRANT PERMISSION TO LOAD EXTERNAL_ACCESS ASSEMBLIES'
       SET @tsql = 'USE MASTER ' + char(13) + char(10) + 
                            'GRANT EXTERNAL ACCESS ASSEMBLY TO ' + @user + ''
 
       EXEC sp_ExecuteSQL @tsql 
       SET @Error = @@Error
       IF @Error <> 0 
              BEGIN
                     PRINT 'ERROR OCCURRED: ' + CONVERT(varchar(50), @error)
                     PRINT 'GRANTING EXTERNAL_ACCESS LOADING PERMISSIONS'
                     PRINT 'TO ' + @user
              END
       ELSE
              BEGIN
              PRINT 'GRANT PERMISSION TO LOAD UNSAFE ASSEMBLIES'
              SET @tsql = 'USE MASTER ' + char(13) + char(10) + 
                                   'GRANT UNSAFE ASSEMBLY TO ' + @user + ''
              
              EXECUTE sp_ExecuteSQL @tsql 
              SET @Error = @@Error
              IF @Error <> 0 
                     BEGIN
                           PRINT 'ERROR OCCURRED: ' + CONVERT(varchar(50), @error)
                           PRINT 'GRANTING UNSAFE LOADING PERMISSIONS'
                           PRINT 'TO ' + @user
                     END
              ELSE
                     BEGIN
                     PRINT 'MARK DATABASE ' + db_name() + ' SO ASSEMBLIES CAN RUN'
                     SET @tsql = N'USE MASTER ' + char(13) + char(10) + 
                                          'ALTER DATABASE ' + db_name() +
                                         ' SET TRUSTWORTHY ON '
                     EXEC sp_Executesql @tsql 
                     SET @error = @@ERROR
                     IF @error <> 0 
                           BEGIN
                           PRINT 'ERROR OCCURRED: ' + CONVERT(varchar(50), @error)
                           PRINT 'MARKING DATABASE ' + db_name() + ' SAFE TO RUN ASSEMBLIES'
                           END
                     END
              END                        
       PRINT ''
       PRINT 'STEP COMPLETED'
       PRINT 'END TIME: ' + convert(varchar(max), getdate())

I post this in hopes that someone else will find value in it.

Cheers,

Justin

No comments:

Post a Comment