SQL Server Performance

sys user ???

Discussion in 'SQL Server 2005 General DBA Questions' started by ykchakri, Jul 29, 2004.

  1. ykchakri New Member

    Is anyone playing around with beta 2 ? What is this 'sys' user. Most of the system SPs and System views are being owned by a user called 'sys' instead of 'dbo'. And I can't see the text of any of these SPs. It says 'there is no text for this object'. But, I'm able to execute 'em.
  2. satya Moderator

    One of the Technet article refers
    The “system objects” shipped by Microsoft physically reside in a location that is not visible to any users. These objects include system stored procedures, system functions, a new class of object called catalog views (which replace the system tables and some system views of SQL Server 2000) and the INFORMATION SCHEMA VIEWS. These “system objects” logically appear in a “sys” schema of every database. Every database (including master) automatically has a “sys” schema.


    So long way to go in replacing most of the pre SQL 2K5 objects....

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. ykchakri New Member

    Thanks Satya,

    Does this means they are hiding the system code from us ????? In 2K we normally access this code to create customized SPs or to learn how a specific function is being done. So, we will not be able to do that any more ?

  4. derrickleggett New Member

    They aren't "hiding" they system code from us. They are just presenting it differently. They have changed the entire security architecture of SQL Server. The system is fully presented to users through a series of tables, views, and functions. There are sys "views" that represent all the old system tables in SQL Server. There are also many more that represent all aspects of SQL Server processing, transaction states, etc. that were never before available.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  5. ykchakri New Member

    Thanks Derrick,

    Can you tell me how can I view a specific system stored procedure code in 2K5 ? For example, I tried sp_addlogin. There was no properties button when I right click on it, 'sp_helptext sp_addlogin' from QA says 'there is no text for this object'. These are the 2 ways I can use in 2K, to view any SP code.
  6. derrickleggett New Member

    I need to revise my statement. <img src='/community/emoticons/emotion-1.gif' alt=':)' /> You can view the sys objects code only through Profiler at this time, as far as I can see. I did play around with this quite a bit to try and view the entire code and couldn't find a way to do it. Hopefully, they will correct this by the final release. It's annoying, especially with so many new system functions and procedures. I'm wondering if they just did this during the beta stage for some reason. <br /><br />If you come across a way to view the entire code, let me know.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  7. ykchakri New Member

    I've posted this in Microsoft newsgroups and someone suggested to use the new function OBJECT_DEFINITION. e.g. SELECT OBJECT_DEFINITION(OBJECT_ID('sys.sp_helptext'))

    This seems to be working fine, but you will be able to see only the first 8000 characters of the code (due to the max. chars per column limitation)

Share This Page