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.