SQL Server Performance Forum – Threads Archive

sql

I am trying to write sql for giving grant permission on view defn for all stored procedure. Can you tell me wht i am doing wrong. SET NOCOUNT ON DECLARE @spname varchar(80)
declare @sql varchar(8000) DECLARE spname_cursor CURSOR FOR
select top 5 name from sys.objects where type=’P’ OPEN spname_cursor FETCH NEXT FROM spname_cursor
INTO @spname WHILE @@FETCH_STATUS = 0
BEGIN select @sql=’GRANT VIEW DEFINITION ON ‘+ @spname + ‘TO [view_Defn]’
print @sql
— GRANT VIEW DEFINITION ON @spname TO [view_Defn] FETCH NEXT FROM spname_cursor
INTO @spname
END
CLOSE spname_cursor
DEALLOCATE spname_cursor Thanks
emamuthu

---

What error are you getting? I am assuming you are running this in SQL 2005 box based on sys.objects. select @sql=’GRANT VIEW DEFINITION ON ‘+ @spname + ‘ TO [view_Defn]’ After I corrected the above space before TO, it is working fine for me. Thanks,
DilliGrg

---

I don’t see any issues other than the one DilliGrG… Note: You are printing but not executing to execute add the following code after your "print @sql" statement use "EXEC(@sql)" …
Mohammed U.

---

No need of Cursor Execute this Select ‘GRANT VIEW DEFINITION ON ‘+ name + ‘TO [view_Defn]’ from sysobjects
where xtype=’p’ Copy the result back to QA and execute
Madhivanan Failing to plan is Planning to fail

---

It works ..Thanks for everyone Thanks
emamuthu

---

Which one are you referring? Madhivanan Failing to plan is Planning to fail

---

Again, Which one are you referring? Madhivanan Failing to plan is Planning to fail

---

Madhivanan, You are correct but it need manual work where as if yo use cursor you can schedule too… Mohammed U.

---

Agreed. But I want to know which one he is referring to [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

---

]]>