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 2008 Forums

SQL Server Administration (2008)

deny, grant problem

Author

Topic

baran121
Starting Member

5 Posts

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

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

baran121
Starting Member

5 Posts

Posted - 2012-12-04 : 10:54:55

i use following code, it works.
but now i have another problem.
after DENY all tables

i do
grant SELECT, INSERT, DELETE ON coloor TO ARGEMAS

for coloor tables to access and insert,delete,update.

and then
select * from coloor
i got following error:
SELECT permission denied on object 'coloor', database 'zynp', owner 'dbo'.

declare @name varchar(30)
declare @sql varchar(100)

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

set @sql='DENY SELECT, INSERT, DELETE ON '+ @name + ' TO ARGEMAS'
exec @sql

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

shilpash
Posting Yak Master

103 Posts

Posted - 2012-12-04 : 10:57:37

I think GRANT SELECT, INSERT, DELETE ON ...has to be followed by tablename ,you could either store that name into some temp table and do the grant...I dont know know if this works but what I am understanding is you could use this--

SELECT name
INTO #Name
FROM sysobjects
WHERE xtype IN ('U','P','FN','V')
ORDER BY name

GRANT SELECT, INSERT, DELETE ON #name TO ARGEMAS

DROP TABLE #Name

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-04 : 11:59:41

Run this and paste the output.

Select 'Grant SELECT, INSERT, DELETE ON ' + name + ' to <user>'
From sysobjects
WHERE xtype in ('U','P','FN','V')

baran121
Starting Member

5 Posts

Posted - 2012-12-04 : 14:15:50

quote:
Originally posted by sodeep

Run this and paste the output.

Select 'Grant SELECT, INSERT, DELETE ON ' + name + ' to <user>'
From sysobjects
WHERE xtype in ('U','P','FN','V')

thank you very much. so i solved with your help.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-04 : 14:42:26

Welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources