CODEWITHSUNDEEP
sqlsql-server-2008variablesrolessql-grant
user592087
user592087

Reputation: 33

T-SQL statement won't accept a variable as role name in a grant statement

I want to create a stored proc in SQL Server 2008 to create database roles and grant permissions to them, that takes one parameter, an nvarchar that represents a role name that is to be created, but im not sure how to write this.

I am able to create the role with this

EXEC sp_addrole @RoleName

but when I try to grant permissions to the role with this

Grant select on dbo.someTable to  @RoleName

it won't accept the @RoleName, how do I go about doing this?

thanks

Upvotes: 3

Views: 4254

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107776

Use dynamic SQL to generate the sql statement as text, which can then be run using EXEC

declare @sql nvarchar(max)
set @sql = 'Grant select on dbo.someTable to ' + @RoleName  -- protect if required
EXEC (sql)

Upvotes: 9

Related Questions