asked on

grant execute on xp_cmdshell to a sql user

Context is SQL Server 2014
I have enabled the system extended procedure xp_cmdshell (being connected as "sa")
Now I want to give execute to this procedure to a user who is says "myuser", login defined at SQL level
when I issue the grant in the master database, it says that myuser does not exist !
The message : Msg 15151, Level 16, State 1, Line 1
Cannot find the user 'myuser', because it does not exist or you do not have permission.
Well, I have the permission ! I am connected as "sa"
How can I fix this ?

Pushpakumara Mahagamage

Read following MS Article, https://msdn.microsoft.com/en-us/library/ms175046.aspx

Check My User permission at

Security, login, properties of my user, User role and User mapping

bharathi sundar

Are you executing the GRANT from master database? If not, execute the command by selecting master database.

Vitor Montalvão

Cannot find the user 'myuser', because it does not exist or you do not have permission

The 'myuser' exists in master database? He may have a login but need to have access in master database also.

LeTay

ASKER

Will try all that ASAP !

LeTay

ASKER

I read that I have to use the procedure sp_xp_cmdshell_proxy_account to authorize a WINDOWS user to run the xp_cmdshell.
But user connecting to my SQL server use a SQL user, not a Windows account !
So how to authorize a SQL user then ?
In the meantime, I found a temporary fix : give sysadmin to the user

LeTay

ASKER

Hum ...
Giving (in addition to "public") sysadmin to "myuser", the application that connects to the SQL server as "myuser" now fails.
So I have to rollback and wait an alternative.
Is there some ?

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LeTay

ASKER

I understand but in previous SQL Server version, it worked fine !
So what is the alternative ?

Vitor Montalvão

Let go back to my previous question. The user exists in master database?

LeTay

ASKER

Yes it exists

Vitor Montalvão

I would say that it should work but I think must be something that I can't see from here and only having access to your machine that I can help you.
If I remember in something else I'll return to this question unless you have some news in the meanwhile.

Pushpakumara Mahagamage

You can do same on SQL 2014,
First carefully check your settings on Previous SQL Box,
You need to grant necessary permission to your user, you can't do that with just public on user role, what mapping exist on user mapping for master [user exist in master]

Set Windows user to SQL Server service and SQL Agent if not configured windows service account yet,

LeTay

ASKER

But such "new" user will not have the same "view" on tables, stored procedures etc... created in the database, with "myuser" as connected user ?!

LeTay

ASKER

I was myself buggy !
The procedure xp_cmsshell worked fine for SA
The problem was that some external file did not exists, but I thought that the xp_cmdshell was not executed, but it was
Sorry for that !

Vitor Montalvão

Not your fault. The error message was misleading us.
I'm glad that you solved your problem.
Cheers