November 8, 2007 at 8:39 am
Jason,
This is going to sound odd, but ... Have you checked your permissions (and inherited permissions, like group memberships) on this server? Verify schema defaults and see if there are any DDL triggers on the creation of stored procedures that might force something to be a system Proc.
Also, check sys.objects and see what Type the proc you're trying to create is listed as.
I'm still playing around with the stuff myself, but I'm beginning to wonder if it's something bizarre like the above listed stuff.
November 8, 2007 at 8:47 am
I've played with the permissions quite a bit...my user is in the sysadmin role, but I've also tried creating a user that has no rights other than what is necessary to create a procedure in master, and the result is the same.
As for the type, it is 'P', with type_desc being 'SQL_STORED_PROCEDURE', and it's in the .dbo schema.
Another interesting thing...I can create a function and it is properly created as a user function, not a system function.
Brandie Tarvin (11/8/2007)
Jason,This is going to sound odd, but ... Have you checked your permissions (and inherited permissions, like group memberships) on this server? Verify schema defaults and see if there are any DDL triggers on the creation of stored procedures that might force something to be a system Proc.
Also, check sys.objects and see what Type the proc you're trying to create is listed as.
I'm still playing around with the stuff myself, but I'm beginning to wonder if it's something bizarre like the above listed stuff.
The Redneck DBA
November 8, 2007 at 3:02 pm
I'm going to try to bounce the SQL Server service tonight and see if it makes a difference. Probably won't, but shouldn't make thinks any worse. (famous last words...).
The Redneck DBA
November 8, 2007 at 9:46 pm
Tried a service restart. No difference.
The Redneck DBA
November 9, 2007 at 3:54 am
I'm still not making much progress myself. What's the Schema_ID and principal_ID of the proc in sys.objects? Also, have you tried scripting out the server & database configs and comparing them to your other servers?
Lastly, have you tried installing a new instance on the server and seeing if the new instance does the same thing?
November 9, 2007 at 7:28 am
The schema_id is the correct id for whatever schema I create it in. (The problem applies to all schema). I just did a test and the principal_ID is NULL. But I think that just means that it is owned by the same person who owns the schema, and since I created it logged on with my sysadmin account, I think that makes sense.
I won't be able to install a second instance...this db server is feeding a 6000+ user a day website, and I don't want to risk any performance issues.
Scripting out the configs is a good idea...how do you do that? 🙂
Brandie Tarvin (11/9/2007)
I'm still not making much progress myself. What's the Schema_ID and principal_ID of the proc in sys.objects? Also, have you tried scripting out the server & database configs and comparing them to your other servers?Lastly, have you tried installing a new instance on the server and seeing if the new instance does the same thing?
The Redneck DBA
November 9, 2007 at 9:01 am
I think I've finally got this one nailed down.
There is an 'allow updates' option you can set with sp_configure. This was an option you could set in sql 2000, but I wasn't really thinking much of it because I thought you couldn't do that in SQL2005.
According to books online:
"This option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported."
---
Mine was set to 1 on the one server that was misbehaving. Sure enough, when I set it to 0, everything behaves as I would expect...the sp I needed to create is now created as a regular user-defined stored procedure.
sp_configure 'allow updates', 0
reconfigure
---
The interesting thing is I have no idear how that option got set in the first place. The interesting thing is if you do:
sp_configure 'allow updates', 1
reconfigure
It will give you an error message saying that updates to the system tables is not supported, and will show 1 for the configured value, but 0 for the running value for 'allow updates'.
So I don't have any idea how it happened, but at least I know what happened and have been able to fix it.
THANKS! for all of your help!
Jason
The Redneck DBA
November 9, 2007 at 11:06 am
Jason,
I'm so glad you figured it out. I was at a loss myself.
Was this particular DB an upgraded one or a fresh install? If upgraded, probably the option was set in 2000 and never got reset during the upgrade.
November 9, 2007 at 12:37 pm
This one was a fresh install. (Actually, I've never tried an upgrade from 2000 to 2005, I've always been lucky enough to have new boxes to work with)
The Redneck DBA
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply