SQL Server Performance Forum – Threads Archive
sys user ???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.
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
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.
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 ?
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
[email protected] When life gives you a lemon, fire the DBA.
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.
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 />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
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)