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

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.