.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Post New Web Links

Can you change the default schema from dbo to the user's schema

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :

In SQL server 2008 - there are multiple users set up with their own schema under a common database. Currently the users are set up with dbo as their default schema. Since DBA has not given write access to the users to write to dbo schema, users have to write their t-sql se create table schemaname.tablename  instead of create table tablename.

Can you make the user schema as their default schema. If yes then will a sql like create table table1 work (with out prefixing the schemaname). I could have tried but don't have the access to change the default schema for an user (not a dba)


View Complete Post

More Related Resource Links

Managing TypedDataSets when there is a change in backend Table / Stored Procedure / Database schema

Hi All, I am just curious to know if there is any easier way to synchronize the TypedDataSets with the chages made to backend Table / Stored Procedure schema.  I use Typed DataSets in my project extensively and I found them very useful and easier to code, but difficult to maintain  The difficuly I have always faced is whenever there is a change in a backend database table structure or stored procedure that is linked to any of the Typed DataSet in our project requires to recreate whole DataSet again by scrapping the old one. The backend changes will not be reflected unless and untill you recreate the whole stuff again. Is there any easier way to synchronize those typed datasets with the changes made to backend database schema or any other workaround that will not required to recreate the whole DataSet again.Hope above question makes sense. Any ideas.

GRANT permission on schema to user in SQL 2000

Hi,I am using following statement to grant specified permissions on schema to a user in SQL Server 2005. GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE on ::[Schema Name] to [My DB User]I need to perform the same in SQL 2000. However, this does not work in SQL Server 2000. Is there any equivalent syntax which can be used in both (SQL 2000 & 2005).Thanks in advance...

Calling a user defined function without Schema Prefix

Calling a user defined function without Schema


 Hi ,

I created a LOGIN User U1 and CREATED a database user DU1 and linked him with the Login User and I created a schema say S1 and set S1 as the default schema for DU1 .

I have a stored Procedure  and a userdefined function inside S1 . I logged in as the Login user, I can execute that procedure directly  without any schema prefix  but I cannot call a user defined function F1 directly (without schema prefix) . it throws an exception

"function name is not a recognized built-in function name"

How can i call a UDF directly with out a schema prefix


--Login User Creation
--Database User Creation and assign to default schema
--Giving User Privilege
EXEC sp_addrolemember db_owner,'S11'

Unable to set Default Schema for a group

We're using Windows Authentication with our SQL Server.  We've added a domain group to the SQL Server groups and would like to give it a default schema.  The properties window is the same for users and groups but the default schema field is enable only for user entities.  We cannot add a default schema to a group.
Is this by design,  a bug or a problem with our SQL Server installation?

Updated on 2005/08/29

My first try was done at through Database\Security\Users.  I tried going through Server\Security\Logins and I got this error message 
Alter failed for User 'Domain\Group'. (Microsoft.SqlServer.SMO)
Additional Information:
--> An exception occured while executing a Transact-Sql statement or batch.

     --> The DEFAULT_SCHEMA clause cannot be used with a Windows group or
          with principals mapped to certificates or asymmetric keys.
          (Microsoft SQL Server, Error: 15487)

Why can we not set a Default Schema to a Windows group?  This would be

Schema: Change 'dbo.aspnet_CheckSchemaVersion' to 'myusername.aspnet_CheckSchemaVersion' in ASP


My hosting provider does not give me 'dbo' privilleges. When i restore my database to my hosting server, the tables and stored procedures come with my user name extension (eg: myusername.StoreProcedure1). So, I am planning to create all stored procedures under the same schema name in my local machine as well. I changed the schema name of all stored procedures and tables from dbo to myusername in my local machine. But when I run the website, I am getting the following error:-

Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

I am NOT CALLING 'dbo.aspnet_CheckSchemaVersion' from my ASP.NET (C#) code. I am using membership class and login controls in my website. How can I make my code to call 'myuserename.aspnet_CheckSchemaVersion' instead of 'dbo.aspnet_CheckSchemaVersion'?

How to change schema name of SQL stored procedures in ASP.NET (C#)?


I  moved my website to the hosting control panel (ASP.NET files and SQL 2005 database).
The hosting provider does not allow the SQL stored procedures with schema name 'dbo' (for example - the provider does not allow 'dbo.aspnet_CheckSchemaVersion'). So, all 'dbo' schema became 'myusername' ie 'myusername.aspnet_CheckSchemaVersion' in the SQL database. Now, when I open my website, I am getting the following error -

Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

What change should I make in my ASP.NET (C#) to make the website to look for 'myusername.aspnet_CheckSchemaVersion', instead of 'dbo.aspnet_CheckSchemaVersion'?

how to stop ScriptTransfer from generate "Create User" "Create Schema" sql statement?



I am using the following code to generate scripts, but no matter how i configure the options, it always generate create user statement




// Connection to Server
        Server srv = new Server(ConfigurationManager.AppSettings["Server"]);

        srv.ConnectionContext.LoginSecure = false;

Change in transaction replication articles without enabling the enable Schema Replication to true


We have a transactional replication database where in we have set the schema change property to false. Now we have added a new column and to the table and wanted this column also to be replicated. But before enabling the allow schema replication parameter, we have altered the article added a column.

Now we reliazed that we have not enabled the sehema replication option. The database is very large and reinitializing the subscription remains our last option.

Could anyone suggest us the right way forward.

Thanks in advance.


Updating Data-Tier Application project after schema change has been made in SSMS

I have successfully created a Data-Tier Application project within visual studio. How do I update these scripts to reflect the changes made by a DBA in SSMS?  Also, on another project we use Entity Framework which drops and recreates the database from within the application. I would also like to import these changes into the Data-Tier Application project.
I can figure out how to import the database into the project the very first time. But after a change has been made using another tool, how do I synchronize the project in visual studio?
When I create a schema comparison that points from database -> project, it correctly analyzes the schema changes. Am I missing how to update the project with these changes?
Thanks, I really would like a solution to this so I can use Data-Tier Applications instead of a third party tool.

How to change the default page base class?


I've tried setting the <pages pageBaseType="DynamicWebPage" /> value in web.config, but when I response.write out the page type, I'm still getting Microsoft.WebPages.WebPage.

I'm simply trying to sub-class WebPage and add some additional functionality such as a dynamic PageData dictionary similar to Phil Haack's dynamic ViewData dictionary.



How to extract a schema from a sql server database



  I need to extract or get the schema from tables in my databse and what is the best way to do it. Is there any third party tool to do it??


Any idea will be appreciated..



Contract-First Web Services: Schema-based Development with Windows Communication Foundation


Schema- first contract-first modeling of Web Services gives you the ability to model your contracts with an XML-centric mindset. This process keeps you focused on universally acceptable types and the hierarchical data structures that can be represented in XML.

Christian Weyer, Buddhike de Silva

MSDN Magazine October 2009

Class To Contract: Enrich Your XML Serialization With Schema Providers In The .NET Framework


The Microsoft .NET Framework 1.x provided minimal options for mapping classes to schemas and serializing objects to XML documents, making this sort of mapping quite a challenge. The .NET Framework 2.0 changes all this with Schema providers and the IXmlSerializable interface.

Keith Pijanowski

MSDN Magazine June 2006

Data Points: Updating Data in Linked Servers, Information Schema Views, and More


Every day a developer somewhere needs to write code to iterate through SQL ServerT system objects, query and update tables in linked servers, handle optimistic concurrency, and retrieve column and stored procedure metadata.

John Papa

MSDN Magazine November 2004

Web Services: Extend the ASP.NET WebMethod Framework by Adding XML Schema Validation


WebMethods make the development of XML Web Services easier by encapsulating a good deal of functionality, but there is still a lot of underlying XML processing that you have to be responsible for. For example, WebMethods do not validate messages against the implied schema. Because they are not validated, the response that's returned can result in unintended consequences. To address this, the authors extend the WebMethod framework by adding XML Schema validation through a custom SoapExtension class.

Aaron Skonnard and Dan Sullivan

MSDN Magazine July 2003

Metadata: Create a Database Schema Repository with Meta Data Services in SQL Server 2000


SQL Server 2000 Meta Data Services is a repository technology that stores and manages metadata for SQL Server. Instead of building database schemas over and over, Meta Data Services allows you to freeze an entire schema for use in other projects. You can also use these schemas for training, testing, or debugging. In this article, the authors will review the various components of Meta Data Services and show how it can be programmed using a Visual Basic client, XML, and XSLT. They will also show you how to manage and manipulate your metadata by generating a simple database schema using a SQL Server repository.

Alok Mehta and Ricardo Rodriguez

MSDN Magazine May 2003

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend