Sql server execute permission; failure to apply permissions

29,131

Solution 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.

Solution 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.

Solution 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'

Solution 4

Here is some code I'm using the verify that (current user) has EXECUTE permission on sp_OACreate etc:

use master;
select state_desc,name from
sys.database_permissions a
left join
sys.all_objects b
on a.major_id = b.object_id
where name like 'sp_OA%';

As pointed out by @John Sansom and @WestDiscGolf the user has to exist in the Master database and the execution rights must granted in the Master database, hence use Master is required. The query above will return records if the user has execute permissions and empty set if they do not. (Execution in the user database will also return empty set.)

I couldn't figure out a way check these permissions using fn_my_permissions, which is supposedly the right tool for jobs like this.

Solution 5

Check if your user has permissions for the database you use. You can do this by Security -> Logins -> Select User and open the properties window. Then select "User Mapping" from the right menu. Now check the databases that you want the given user to have access to. After that select from the bottom part of the window "Database role membership" and check "db_owner". Now the user will be the owner of the database and will be able to execute queries, store procedures and so on.

UPDATE:
Add user for the database by selecting your database -> security -> users -> right click "New User"
Or you can use this query

CREATE LOGIN AbolrousHazem 
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';
USE AdventureWorks2008R2;
CREATE USER AbolrousHazem FOR LOGIN AbolrousHazem;
GO 

Here are more details http://msdn.microsoft.com/en-us/library/ms173463.aspx

Share:
29,131

Related videos on Youtube

WestDiscGolf
Author by

WestDiscGolf

Husband, Dad, Developer and Disc Golfer. I'm also on Twitter

Updated on July 09, 2022

Comments

  • WestDiscGolf
    WestDiscGolf over 1 year

    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.

  • WestDiscGolf
    WestDiscGolf over 13 years
    Thanks for your reply. The db_owner role for the user is set on the database. I have tried to add the specific execute permissions to the db_owner role and I get the following error message "Cannot grant, deny or revoke permissions to or from special roles."
  • WestDiscGolf
    WestDiscGolf over 13 years
    Thanks for your reply. I've run the first command on the server and no records are returned by the query.
  • Thea
    Thea over 13 years
    Check if you have a user attached to your database. It seems like you have only the login but not the user.
  • WestDiscGolf
    WestDiscGolf over 13 years
    this did seem to be the case; I've added an answer to what I did to resolve the issue. Thanks for the help :-)
  • WestDiscGolf
    WestDiscGolf over 13 years
    Thanks for the point in the help in the right direction. +1 :-)
  • John Sansom
    John Sansom over 13 years
    How did you migrate your databases? If you did not also Restore the master database then the user "dbuser" would not have been present and hence not mapped.
  • WestDiscGolf
    WestDiscGolf over 13 years
    @John - You are correct. The restoration of the Master database wasn't done due to some historical bad bits and bobs which we wanted to get away from in the migration. Unfortunatly we also lost the users which are now re-created and mapped to the logins :-)
  • Mike Gledhill
    Mike Gledhill about 10 years
    Excellent - setting my SQL Server login as a "db_owner" solved my issues. Thanks !

Related