Re: Restricting Access to Groups of Tables

From: Dejan Sarka (dejan_please_reply_to_newsgroups.sarka_at_avtenta.si)
Date: 03/10/04 

Date: Wed, 10 Mar 2004 09:28:44 +0100

You will have to do your own procedure, using a cursor and dynamic sql.
Check this example:

USE Northwind
GO

DECLARE mytables CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'C%'
      AND TABLE_TYPE = 'BASE TABLE'
      AND TABLE_SCHEMA = 'dbo'

DECLARE @mtable sysname, @mstmt nvarchar(4000)

OPEN mytables
FETCH NEXT FROM mytables INTO @mtable

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @mstmt = 'DENY SELECT ON ' + @mtable + ' TO Public'
 EXEC(@mstmt)
 FETCH NEXT FROM mytables INTO @mtable
END

CLOSE mytables
DEALLOCATE mytables
GO 

-- 
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"MartyS" <anonymous@discussions.microsoft.com> wrote in message
news:a0a301c40620$dab1c4e0$a101280a@phx.gbl...
> I'm running MAS 500 which includes hundreds of tables. Is
> it possible to restrict or allow access by groups of
> tables which share a common prefix?
>
> i.e tarxxxxx or tapxxxxxx