Query analyzer issue

Question

Post reply

Query analyzer issue

Bill-314752

Grasshopper

Points: 23
More actions
May 18, 2007 at 9:03 pm

#92405

In query analyzer there are some sp that I can not edit or script as alter. Most do not have this problem. I can not find any differences. Any ideas would be appreciated.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

User permissions/object owner.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Bill-314752 Grasshopper Points: 23 More actions · Answer 2

There are no differences in the permissions or object owner for any of the sp. They were all scripted at the same time by the same user and appear as dbo.

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 3

Are they system procs? What error message are you getting?

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

And, what permissions do you have when you login?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 5

just had another thought. could these be extended stored procedures? also, you can also encrypt procs at creation

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 6

Do you get an error message or is the script option grayed out?

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Bill-314752 Grasshopper Points: 23 More actions · Answer 7

All sp have been created by the datbase owner. None are extended sp There are no error messages. Some scipts are dispalyed as create and not alter even though I select alter. This happens on very few. The condition remains even if I alter the sp. The condition exists in query analyzer if I select edit or script to new window as alter. Change the create statment to alter and it works fine.

I have seen this happen on a few different databases and believe that using the 2005 management studio tool to alter or create sql 2000 sp has contributed to the cause. (I may be wrong). This condition has never happened before on any of the many sql databases that I develop on. Now it happens on a few.

I thought that someone might have seen this behavior before. I plan to isolate a sp with the problem and try to script it as alter on different workstations to determine if it workstation specific. I will post back with results.

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 8

Going back to my original question... what privs do YOU login with? If they are all created as "dbo", then unless YOU have "dbo" or "sa" privs, someone must GRANT privs to public (or to you) for you to be able to change them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 9

Switch to Text results with ctrl-T, then execute "SELECT text FROM syscomments WHERE ID = OBJECT_ID('your_procedure') ORDER BY colid". You should get the original CREATE PROCEDURE text as a result.