3

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user592087
  • 33
  • 1
  • 3
  • A lot of those "management" statements do **not** accept variables - use explicit names, or then you must use dynamic SQL (build your explicit SQL statement as a string variable, and then execute it) to achieve your goal – marc_s Jan 27 '11 at 11:51

1 Answers1

9

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)
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262