How to remove default schema?

Hi,

Re: sql server 2005

I recently migrated a database from one server to another. In the process of doing this, I renamed one of the sql server 2000 migrated logins to a new name, using "alter login with name" and "alter user with name"

Now, I'm having problems with permissions. This user cannot execute sp_send_dbmail, even though it has explicit execute permissions on this stored proc in the msdb database.

What I noticed about this user is that is carried over a default schema from the old 2000 server. I'm beginning to think that this has something to do with the lost permissions. So I tried to drop the default schema. No dice. Next, I set the default schema to dbo. That still didn't work.

Can someone please tell me how to drop a default schema for a user? I've tried everything I can think of, from removing the schema name from the properties windows, to "alter user". Nothing has worked.

Thanks

You cannot remove, you can just change the default schema.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks. Yes, I gathered as much.

I realize that the problem with sp_send_dbmail didn't have anything to do with the default schema. It had to do with the fact that the login wasn't mapped to the msdb database, even though it existed as a user in the msdb database. (long story)

Dropping the user, then remapping the login to the user solved the problem.

However, some of the migrated users do NOT have a default schema assigned to them. How is this possible?

All of the other migrated users DID have a default schema, i.e.) the user name = default schema name.

How to remove 1:1 Relationships

I have an asset schema problem which I'm not too sure how to resolve...

I have an asset table which stores values common to all asset types. I have created seperate asset type tables(VehicleAssetDetail, PropertyAssetDetail, InvestmentAssetDetail) to store their specific details.

This in turn creates 1:1 relationships between "Asset" table and the respective detail tables.

If I were to store all the details in the "Asset" table it would cause a lot of redundancy bearing in my mind there are about four other asset type details I have excluded from the schema.

Are the 1:1 relationships bad? If they are, how do I resolve them without throwing all the columns from the asset details tables into the Asset table?

Attached is a 15Kb gif image of the schema. I'd really appreciate any help with this... :)
Thanks.A lot of your assertions are fals.

Why would a 1:1 create redundant data?

Only reason I would use a 1:1 is if the row length is too long, then I would catergorize the data into appropriate entities.

But remember, the best join, is none|||Hi Brett...
You may have misunderstood my statement...
If all the columns of each Detail table would be placed in the "Asset" table and the Details tables dropped, most of the columns in the "Asset" table would be null, except for the columns which relate to the specific asset which is inserted.

A 1:1 relationship would not create redundancy.
So back to my original question... :)|||This was raised in another thread which may be worth a read

http://www.dbforums.com/showthread.php?t=1622324

Read the last 3 or 4 posts or so and you'll get the answer ;)

How To Remap Back To DBO?

How to I change the owner (schema) in SQL 2005 back to dbo from a SQL Server
login? I want this:
someuser.sometable
to be:
dbo.sometable
Thanks!
======================================
David McCarter
www.vsdntips.com
VSDN Tips & Tricks .NET Coding Standards available at:
www.cafepress.com/vsdntips.20412485Using alter schema - for example:
ALTER SCHEMA dbo
TRANSFER someuser.sometable
-Sue
On Mon, 11 Sep 2006 14:28:01 -0700, dotNetDave
<dotNetDave@.discussions.microsoft.com> wrote:

>How to I change the owner (schema) in SQL 2005 back to dbo from a SQL Serve
r
>login? I want this:
>someuser.sometable
>to be:
>dbo.sometable
>Thanks!
>======================================
>David McCarter
>www.vsdntips.com
>VSDN Tips & Tricks .NET Coding Standards available at:
>www.cafepress.com/vsdntips.20412485

how to reflect schema changes at subscriber using transactional replication

Hi

I need to setup transactional replication for a production server.

The tables (articles) at publisher being replicated can expect schema changes.

Is there any way to reflect schema changes at subscriber using transactional replication.

Early Thanks,

Salman Shehbaz.

If this is SQL Server 2005, the schema changes at publisher can be automatically replicated to subscriber.

If this is SQL Server 2000, you should make schema changes through proc sp_repladdcolumn or sp_repldropcolumn when adding or dropping columns. Other schema changes can not be replicated in SQL Server 2000.

How to query xml data in column type xml

How can I query xml data in column type xml using an xml schema from the same table row in the xmlschema column and return values(not xml) to insert into a seperate table

XML Data Table

C1 =RecordID (int)

C2 =XMLData (xml)

C3 = XMLSchema(xml)

How can I query the XML data using the xmlshema column

and return values(not xml) to insert into table 2

Table 2

C1 = RecordID

C2 = User

C3 = date

thanks

I think you are looking for the XQuery "nodes()" function if I understand you correctly. It sounds like you want to reach into the column that is storing the schema, extract scalar values from that schema and store it in a relational table. If this is what you are trying to do, then the nodes() function should work for you.

Here is an example using the nodes() function on the xml data type.

http://msdn2.microsoft.com/en-us/ms188282.aspx

Once you get the data into relational form, you can CURSOR over it and call your INSERT statement with parameters, use INSERT INTO ... SELECT, or generate dynamic SQL.

How to query SQL CE database schema

What are some basic commands to query information about a CE database schema. I'd like to see my foreign keys etc. Where can I find this information in BOL?

Look for the INFORMATION_SCHEMA keyword. This allows you to query the database schema through SQL. For instance, you get the list of tables with this:

Code Snippet

SELECT * FROM INFORMATION_SCHEMA.TABLES

|||The documentation is available here: http://technet.microsoft.com/en-us/library/ms174156.aspx