Hi,

I have created a server role in a database which needs SELECT permissions on some, but not all, tables in the database. There are almost a hundred tables that need permissions set on, so I have been trying to put together a SQL script that will do this for me. In the standard GRANT Select on... script you can name only one table to grant permissions on. Someone recommended using dynamic SQL but I have only just got to grips with the basics of SQL. This is my current attempt at using the sp_executesql stored procedure:

************************************************** ************
DECLARE @SQLString VarChar(1000)
DECLARE @TableName VarChar(100)

SET @SQLString = 'Grant Select on @TableName To "ServerRole"'

Set @Tablename='Table1'
EXEC sp_executesql @SQLString

Set @Tablename='Table2'
EXEC sp_executesql @SQLString
************************************************** ************

This generates the following error message:

"Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'."

There is probably something very obvious about my code that jumps out at an experienced (or not-so experienced) SQL person. Can anyone suggest how to improve this code, or an altogether different way of achieving the same end?

All help greatly appreciated.

Thanks,
Sharon

Here's how I would do it. The following code produces the output on the northwind DB. I add the like statements so it only does the tables that start with C or S. I would then run the output in another Query Window.

I hope this helps.

Tim S

SELECT 'GRANT SELECT ON ' + TABLE_NAME + ' TO ServerRole'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND
   ( TABLE_NAME LIKE 'C%' OR TABLE_NAME LIKE 'S%')


-----------------------------------------------------
GRANT SELECT ON Categories TO ServerRole
GRANT SELECT ON CustomerCustomerDemo TO ServerRole
GRANT SELECT ON CustomerDemographics TO ServerRole
GRANT SELECT ON Customers TO ServerRole
GRANT SELECT ON Shippers TO ServerRole
GRANT SELECT ON Suppliers TO ServerRole

Tim

This is great - works a treat. Thanks very much!

Sharon

Just a quick comment, your original code has a few problems.



@SQLString will always hold the string 'Grant Select on @TableName To "ServerRole"'. You need to explicitly concantate the variable values:
SET @SQLString = 'Grant Select on ' + @TableName + ' To "ServerRole", this is not perl or php. Also when you change the value held in @TableName, this will not update @SQLString. Finally if you declare @SQLString as a nvarchar the original message will dissappear.

What you acutally want is:
DECLARE @SQLString nVarChar(1000)
DECLARE @TableName nVarChar(100)

Set @Tablename='Table1'
SET @SQLString = 'Grant Select on ' + @TableName + ' To "ServerRole"'
EXEC sp_executesql @SQLString

Set @Tablename='Table2'
SET @SQLString = 'Grant Select on ' + @TableName + ' To "ServerRole"'
EXEC sp_executesql @SQLString

stahta01's solution is better, but I thought that this may help in writing future code.

regards
David Cameron