How to grant unsafe assembly to a username found dynamically?

Question

I have to register a DLL in a SQL Server from a legacy project, the bad thing is that I don't have the DLL nor the source code, just what I have is the binary string obtained from the Create Scripts option on my database. I'm trying to use the following code:

USE [master]
DECLARE @CurrentUserName varchar(MAX)
SET @CurrentUserName = SUSER_SNAME()
SELECT @CurrentUserName
GRANT UNSAFE ASSEMBLY TO @CurrentUserName

The last line is giving me an error:

Incorrect syntax near @CurrentUserName. Expecting ID, NULL, PUBLIC or QUOTED_ID

I'm creating this script for giving my new team members a script to setup their DEV environments the first day without giving them a large manual with instructions because we learned that as our process of setup is huge many mistakes happen.

Thanks for your help!

If you need to use a command like `GRANT` with a variable, consider dynamic SQL. — ZLK, Oct 19 '17 at 22:44
Found a workaround [here](https://stackoverflow.com/a/25848214/8803059), but still curious if there's a way semejant to what I was trying to do. — Alfonso Lara Ramos, Oct 19 '17 at 22:51

score 0 · Accepted Answer · answered Oct 20 '17 at 05:57

--You Have to use Dynamic SQL 

USE [master]
DECLARE @CurrentUserName VARCHAR(MAX),@String_SQL NVARCHAR(MAX)
SET @CurrentUserName = SUSER_SNAME()
SELECT @CurrentUserName
--GRANT UNSAFE ASSEMBLY TO @CurrentUserName

SET @String_SQL=''
SET @String_SQL =N'GRANT UNSAFE ASSEMBLY TO '+@CurrentUserName+''

PRINT @String_SQL
EXEC (@String_SQL)

How to grant unsafe assembly to a username found dynamically?

1 Answers1