deny, grant problem - 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)

deny, grant problem

Author

Topic

baran121
Starting Member

5 Posts

Posted - 2012-12-04 : 10:23:10

Hi everyone i wanna specially DENY, GRANT tables for some user
there is a code which i tried to run.
there is a mistake
Line 14: Incorrect syntax near '@name'.

please help me. thank you

declare @name varchar(30)

DECLARE c1 CURSOR FOR
Select name From sysobjects WHERE xtype in ('U','P','FN','V') order by name

OPEN c1
FETCH NEXT FROM c1 INTO @name
WHILE @@FETCH_STATUS=0
BEGIN

GRANT SELECT, INSERT, DELETE ON @name TO ARGEMAS

FETCH NEXT FROM c1 INTO @name
END
CLOSE c1
DEALLOCATE c1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-04 : 10:56:24

Declare a variable up top like this:

DECLARE @sql NVARCHAR(4000);

Then change the code between begin and end to this:

BEGIN
    SET @sql = 'GRANT SELECT, INSERT, DELETE ON ' + @name + ' TO ARGEMAS'
    EXEC (@sql);
    
    FETCH NEXT FROM c1 INTO @name
END

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-12-04 : 10:58:19

Please do not cross post: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=181176

Continue in the other thread.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources