Just having a discussion with a colleague , she made some changes in configuration and now when she creates a simple (not a system) stored procedure in master database it saves under Programmability –Stored Procedures–System Stored Procedures. Is it comfortable? No,right? After some investigation I found that we need to return ‘allow updates’ to 0 , see below script
EXEC sp_configure ‘allow updates’,0
reconfigure
Now everything got back to work in the ‘right’ place.
Remember if you connect to SQL Server using Windows group, you cannot define a default schema. It is greyed out. This means that if you want everyone to be in a specific schema, you can’t do it by default.
See reference
http://connect.microsoft.com/SQLServer/feedback/details/328585/default-schema-for-windows-group.
I hope finally MS will be addressing this issue in the next release of SQL Server named Denali (CTP 4)
/*
I’m happy to report that the next CTP for SQL Server Denali will allow default schemas to be assigned to Windows groups.
I’d like to thank everyone for their votes and attention to this issue. It made a different in being able to appropriately prioritize and make this improvement happen.
Il-Sung.
*/
MS has just released SQL Server 2008 R2 Service Pack 1. Both the Service Pack and Feature Pack updates are available for download on the Microsoft Download Center. SQL Server 2008 R2 SP1 contains cumulative updates for SQL Server 2008 R2 and fixes to issues that have been reported through our customer feedback platforms.
To download the Service Pack packages please visit:
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26727 —SQL Server 2008 R2 SP1
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26729 —SQL Server 2008 R2 SP1 Express
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=26728 —SQL Server 2008 R2 SP1 Feature Packs
To learn more about SQL Server 2008 R2 SP1 please visit Knowledge Base Article For Microsoft SQL Server 2008 R2 SP1.
http://support.microsoft.com/kb/2528583