| Author |
 Topic  |
|
|
baran121
Starting Member
5 Posts |
 Posted - 12/04/2012 : 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 - 12/04/2012 : 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
Yak Posting Veteran
72 Posts |
Posted - 12/04/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/04/2012 : 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 - 12/04/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/04/2012 : 14:42:26
|
| Welcome. |
|
|
| |
Topic |
|
|
|
deny, grant problem
