Sql server execute permission; failure to apply permissions

Question

I've just migrated from SQL2000 to SQL2008 and I have started getting an execute permission issue on a stored proc which uses sp_OACreate.

The rest of the system works fine with the db login which has been setup and added to the database.

I've tried:

USE master
GO
GRANT EXEC ON sp_OACreate TO [dbuser]
GO

But this fails with the following error:

Msg 15151, Level 16, State 1, Line 1 Cannot find the user 'dbuser', because it does not exist or you do not have permission.

I'm logged into the server as sa with full permissions. I can execute a similar sql statement and apply the permissions to a server role, however not a login/user.

How do I apply the changes to the specific user/login?

I can apply the permissions to the public role and it resolves my issue; however this seems to be a security issue to me which I don't really want to apply to the live server.

Answer 1

Leading on from John's answer I checked the user listings on the Master database and my user wasn't there. Whether it had been deleted or lost some how I don't know. Something may have gone crazy with the migration of the dbs to the new server instance.

Anyway; re-creating the user and associating it to the specific login enabled me to run the following statements on the master database to allow for the execution of the stored procs.

USE MASTER
GO

GRANT EXECUTE ON [sys].[sp_OADestroy] TO [dbuser]
GO
GRANT EXECUTE ON [sys].[sp_OACreate] TO [dbuser]
GO
GRANT EXECUTE ON [sys].[sp_OAMethod] TO [dbuser]
GO
GRANT EXECUTE ON [sys].[sp_OASetProperty] TO [dbuser]
GO

Thanks for all the help and pointers. Hope this helps other people in the future.

Answer 2

The error suggests that the User "dbuser" does not exist in the master database.

I assume the user exists within the master database?

You can check by using the following T-SQL

USE MASTER;
GO

SELECT *
FROM sys.sysusers
WHERE name = 'dbuser'

If the user turns out not to exist, simply use the CREATE USER statement and create a user called "dbuser". The user will automatically be mapped to a Login of the same name, provided one exists.

Answer 3

Your problem could be related to orphaned users.

Try

USE MASTER
GO
EXEC sp_change_users_login 'Report'

This will return one row per orphaned user name. Then,

EXEC sp_change_users_login 'Update_One', 'dbuser', 'dbuser'