SQL 2005 Schemas 1

[thamms]

Hi,

I backed up and restored a database from one server to another. I also ran the sp_help_revlogin script to map the logins to their respective database users.

Afterwards, I used the ALTER LOGIN and ALTER USER commands to change the name of the of logins as it had a name that referenced the old server.

Everything "seemed" ok, however the newly renamed login/user no longer has certain privileges. For example, it has lost its ability to execute sp_dts_getpackage and sp_send_dbmail EVEN THOUGH it has explicit execute permissions on these sp's in the msdb database. It also belongs to the DBMailUser role (or whatever it's called).

But if I try to send mail with this renamed user, I get an error message that it doesn't have privileges on sp_send_dbamil. Strange.

As a test, I created a new login and user with these same execute permissions in msdb, and no problem.

There seems to be something buggy with this renamed user.

I am wondering if I should just recreate the user from scratch, OR if this has something to do with the default schema that this renamed user belongs to? I see that it carried over the default schema from the old database.

Any ideas?

Thanks

 

[mrdenny]

You need to map the login to the user in the msdb database as well, not just in the user database which you restored.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com