September 18, 2012 at 6:20 am
We run a SQL Server 2000 instance v8.00.2249(ye, I know...) on which I wish to create a view. I have the SA-role, the view is created as a system object: type System instead of User. This means other users with less autorisation can not see this view.
I have used both a t-sql script and the GUI New View/table and just patch something together. Everytime it appears to be a System object. This is vexing, since I do not know if this is standard behaviour for SQL2000. Can't imagine it is. On another SQL2000 instance this behaviour does not occur.
This SQL2000 instance runs on a W2003 SP2 virtual machine.
TIA
--------------------------------------------------------------------------------
Greetz,
Hans Brouwer
September 18, 2012 at 6:28 am
in SQl 2000, it was certainly possible to leave a setting ON so that subsequent objects were created as system objects.
once an object is marked as a system object, you cannot unmark it; you'll need to drop and recreate it.
you need to run this command to turn that setting off:
EXEC master.dbo.sp_MS_upd_sysobj_category 2
The SQL 2000 code as a complete example.
--Turn system object marking on
EXEC master.dbo.sp_MS_upd_sysobj_category 1
--create all the system objects you want to create:
CREATE PROC sp_yourprocedure.....
--Turn system object marking off
EXEC master.dbo.sp_MS_upd_sysobj_category 2
how it's done in 2005 and above:
EXECUTE sp_ms_marksystemobject 'sp_yourprocedure'
Lowell
November 29, 2012 at 3:38 am
Tnx for answering, it works indeed.
Greetz,
Hans Brouwer
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy