Permissions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Permissions

Author

Topic

victord
Yak Posting Veteran

64 Posts

Posted - 2003-04-24 : 08:28:18

Dear Folks,

It is quite easy and straight to grant or revoke permissions for a user or group on a single table using for example:

GRANT SELECT
ON authors
TO public
GO

GRANT INSERT, UPDATE, DELETE
ON authors
TO Mary, John, Tom
GO

But how do you grant or deny permissions for a user or group for tables as much as 1000 or more.

Thanks in Advance
Victord

macka
Posting Yak Master

162 Posts

Posted - 2003-04-24 : 08:37:31

You have asked a similar question before:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25456[/url]

Just a few tweaks required:


DECLARE @name AS VARCHAR(255) 
DECLARE @sql as NVARCHAR(255)
DECLARE curDB CURSOR FOR 
--modify this query to suit your needs 
SELECT name 
FROM sysobjects 
WHERE xtype = 'U' 


OPEN curDB 

FETCH NEXT FROM curDB into @name 
WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @sql = N'GRANT SELECT ON [' + @name + '] TO public' 
EXEC sp_executesql @sql
FETCH NEXT FROM curDB into @name 

END 

CLOSE curDB 
DEALLOCATE curDB

macka.

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

Edited by - macka on 04/24/2003 08:43:19

victord
Yak Posting Veteran

64 Posts

Posted - 2003-04-24 : 09:00:58

Thanks Macka for pointing that out.
But that was to change the owner of an object. In this case i want to set the permission on each table within the database.

The cursor you wrote earlier would probably do the same thing, but..., cursor is a bit of a blur to me.

Thanks Again
victord

victord
Yak Posting Veteran

64 Posts

Posted - 2003-04-24 : 09:14:02

Thanks Macks,

Please disregard the first reply.
That cursor was spot on.

Thanks Again,
Victord

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources