Post reply

Creating a view becomes a SYSTEM object?!

  • September 18, 2012 at 6:20 am

    #96976

    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

    #1539056

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 29, 2012 at 3:38 am

    #1563644

    Tnx for answering, it works indeed.

    Greetz,
    Hans Brouwer

Viewing 3 posts - 1 through 2 (of 2 total)

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