How to give a user SQL permission to execute stored procedure using powershell invoke-sqlcmd

Question

How to give a user SQL permission to execute stored procedure using powershell invoke-sqlcmd

Craig Garland 336

Hi All,

Hope someone has a solution for me.

I have a MS SQL 2016 server. I have created a stored procedure and set execute as to Owner.

I would like to have standard user be able to run this stored procedure using the PowerShell command invoke-sqlcmd. Currently the powershell command works if the user has owner rights to the DB.

I cannot get it working for the standard user that only has exec rights to the stored procedure and public access to the DB. I keep getting the error logon failed for user XXX

The reason to restrict access is a task that needs to be run by first level helpdesk and currently, they need to wait for resource from our SQL Admin to complete the task.

Does anyone know how to set the permissions to allow a user to run a stored procedure without giving read / Write / Owner permissions to the DB.

Thanks for your time.

Craig

Accepted answer

1 additional answer

Your answer

Answer 1

Erland Sommarskog 121.3K MVP Volunteer Moderator

I keep getting the error logon failed for user XXX

Then you need to grant the user access to the server itself. Once you have done that, this is sufficient:

only has exec rights to the stored procedure and public access to the DB.

So to summarise, what you need is:

CREATE LOGIN "DOMAIN\Helpdesk" FROM WINDOWS
go
USE YourDB
go
CREATE USER "Domain\Helpdesk" 
GRANT EXECUTE ON YourSP TO "Domain\Helpdesk"

Note that Helpdesk here can be a Windows user or a Windows group. I very much recommend the latter, as helpdesk people are likely to come and go.

As for the use of EXECUTE AS OWNER, a better technique is to use certificate signing, as EXECUTE AS OWNER disrupts several auditing scheme. But if you don't care about that, you can use it, if it is only a matter of operations on database level. But if there are also operations also on server level, certificate signing is the only option.

Craig Garland 336 Reputation points

2025-06-07T23:29:00.1+00:00

Hi,

Thanks for the comment, but the user has been created and has public access to the server. I have also tried to give the user read / write on the DB which did not work.

Craig
Balmukund 176 Reputation points Microsoft Employee

2025-06-08T01:35:35.3066667+00:00

Login failed for user would have an entry in SQL Server ERRORLOG file as well. You can look at the exact message and the state of the error. That might give us some clue.

You can also take a profiler trace of SQL Server and look at more details of login failure.
Craig Garland 336 Reputation points

2025-06-10T22:26:16.5933333+00:00

Hi

Thanks for that information. I will try and find time over the next week to review these logs and see if it helps.

Craig
Craig Garland 336 Reputation points

2025-06-12T01:22:59.6833333+00:00

Hi Erland,

It seems like once I use the create user on the DB then the procedure works.

I have two questions:

One does this give the user and read / write permission to the DB? I cannot see any permission granted but would like to confirm.

Also can I just use the create user for a security group rather than user?

Thanks for your time.

Craig
Erland Sommarskog 121.3K Reputation points MVP Volunteer Moderator

2025-06-12T15:52:58.22+00:00
I start with this question:

Also can I just use the create user for a security group rather than user?

Yes, and this is also the way you should do it the normal case. Today, Lisa, Henry and Gretchen work on the helpdesk. But rather than adding DOMAIN\Lisa, DOMAIN\Henry etc as server logins and database users, there should be an AD group DOMAIN\HelpdeskUsers. When Lisa moves on to another position, and Emily joins, all that needs to be done is to take Lisa out of HelpdeskUsers and add Emily. As a DBA in SQL Server, you should not have to do anything.

One does this give the user and read / write permission to the DB? I cannot see any permission granted but would like to confirm

If you add a user to the database, in the normal case, this alone only gives the user permission to say "USE db", but the user have no access to tables, stored procedure etc. If you find that the user nevertheless can access for instance tables, this is because the user is part of a role/group with those permissions. That would be any of:

An AD group of which the user is a member.

The public role of which all users are a member. Generally, I recommend against granting permissions to public.

Another, more far-fetched possibility is that there is a DDL trigger for CREATE USER and grants a set of default permissions for the user.

Note also that if the user runs a stored procedure that operates on tables, the user only needs permission to run the procedure, as long as the procedure and the tables have the same owner. (And typically all database objects are owned by dbo.)
Craig Garland 336 Reputation points

2025-06-12T22:28:28.18+00:00

Hi Erland,

Thanks for that detail answer. Looks like this meets all the requirements I was looking for. I have completed initial testing without any issues. Just interesting that you need to add the user \ Group to the DB rather than just the SQL server.

Thanks for your time.

Craig
Erland Sommarskog 121.3K Reputation points MVP Volunteer Moderator

2025-06-13T21:46:13.39+00:00

Just interesting that you need to add the user \ Group to the DB rather than just the SQL server.

If you have a SQL Server instance with only a single application, it may not be very intuitive. But an instance may host many databases that are completely unrelated, and in that case you want people only to have access to the database where they have a legit reason to go.

Answer 2

Olaf Helper 47,416

It doesn't matter, which tool you are using: SSMS, Azure Data Studio or PowerShell.

All are issuing plain T-SQL commands against the database engine.

And in this case it's a GRANT PERMISSION command, see

https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-transact-sql?view=sql-server-ver17

Additional: Have a look at https://github.com/dataplat/dbatools

Share via

How to give a user SQL permission to execute stored procedure using powershell invoke-sqlcmd

1 additional answer

Your answer