Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 13th, 2003, 09:24 AM
Registered User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to oidhche
Default Grant SELECT on multiple tables

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
 
Old June 14th, 2003, 01:53 AM
Registered User
 
Join Date: Jun 2003
Location: , Indiana, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 16th, 2003, 03:52 AM
Registered User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to oidhche
Default

Tim

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

Sharon
 
Old June 16th, 2003, 06:26 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Select Count from multiple tables aspless SQL Language 1 January 30th, 2008 10:32 AM
Select from Two Tables Problem gaurav_jain2403 SQL Server 2000 2 May 18th, 2006 07:18 AM
Importing Multiple files in Multiple tables Versi Suomi Access 6 June 1st, 2005 08:47 AM
Multiple Recordsets from Multiple Tables TSEROOGY Classic ASP Databases 2 December 28th, 2004 12:45 PM
Select lot tables... vieritlc Classic ASP Databases 3 May 18th, 2004 07:07 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.