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 Receive emails

I've recently migrated to sql 2005 and I'm trying to find a solution
to some new requests.
I need to setup a system that process excel spreadsheet received by
email. The sheets will contain data coming from veterinaries that -
after an integration with centrally stored data - should trigger a
report.
I can receive emails both on Lotus Notes (that I don't manage) or POP3
account.
Can't' install Exchange because of the centralized Active Directory
policy.
Anyone, who had a similar issue, can recommend me a simple and robust
solution?
The alternative is to set up a web page to upload the files, but the
IIS server is located on the intranet, so people will need to dial a
modem or use the vpn. Not straight as an email...
Many Thanks,
josephOn Oct 11, 3:37 am, curi...@.spore.it wrote:
> I've recently migrated to sql 2005 and I'm trying to find a solution
> to some new requests.
> I need to setup a system that process excel spreadsheet received by
> email. The sheets will contain data coming from veterinaries that -
> after an integration with centrally stored data - should trigger a
> report.
> I can receive emails both on Lotus Notes (that I don't manage) or POP3
> account.
> Can't' install Exchange because of the centralized Active Directory
> policy.
> Anyone, who had a similar issue, can recommend me a simple and robust
> solution?
> The alternative is to set up a web page to upload the files, but the
> IIS server is located on the intranet, so people will need to dial a
> modem or use the vpn. Not straight as an email...
> Many Thanks,
> joseph
You will most likely want to set this up via a custom ASP.NET
application. You can either have an SSIS pkg triggered through an
ASP.NET application to loop through a directory and import several
excel files into a table, etc -or- you can use bulk insert or BCP
through an ASP.NET application'/stored procedure to import the excel
spreadsheets. Then you could call Reporting Services through the web
service method (http://msdn2.microsoft.com/en-us/library/
microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx )
to create a report. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant