SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 deny, grant problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

baran121
Starting Member

5 Posts

Posted - 12/04/2012 :  10:24:35  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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


Go to Top of Page

shilpash
Posting Yak Master

101 Posts

Posted - 12/04/2012 :  10:57:37  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/04/2012 :  11:59:41  Show Profile  Reply with Quote
Run this and paste the output.

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

baran121
Starting Member

5 Posts

Posted - 12/04/2012 :  14:15:50  Show Profile  Reply with Quote
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.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/04/2012 :  14:42:26  Show Profile  Reply with Quote
Welcome.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000