Minimum permissions required to use PowerShell Provider

Question

I was playing around with SQL Server PowerShell Snapins and ran into some error messages. I know it uses WMI to pull some data, but what I am not sure of is what the minimum database permissions that are required for the provider to show information.

What are the requirements to allow users to do this?

Answer 1

Hi Jazzy,

In fact, Powershell is just as Windows Explorer, which only provider an interface to allow us to run the tasks. The permission for Powershell depends what permissions the user owned to run the commands. One thing you may note is the ExecutionPolicy options for the Powershell: Restricted, AllSigned, RemoteSigned, and Unrestricted. By default, the ExecutionPolicy is Restricted, which prevents running any scripts, even if you have permissions to run the commands.

For more information: SQL Server and PowerShell Security.

TechNet Subscriber Support
If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

---

Stephanie Lv

TechNet Community Support

Answer 2

You don't understand my question. To use the PowerShell SQL Server provider what permissions do I need in SQL Server to use it.

I know it requires WMI for some parts, but what permissions in the database are the minimum. Do you need sysadmin? Can it be lower than that? What if all I want is access to a single database?

That is what I an asking. There is no clear documentation on what Database permissions are needed to let the provider work when connecting to SQL Server.

Answer 3

Jazz,

Thanks for your update.

According to your description, there are two types of permissions here. One is for access Windows WMI objects, the other is for access SQL Server.

The permission for access WMI objects depends on the service account of Windows Management Instrumentation service. Local system account is required for this account to allow WMI to work smoothly.

For access SQL Server via WMI, the login launched the SQL Server Powershell also needs to have permissions to access the target objects. Assuming that a login which is fixed with only a public server role, he can navigate to the location of ‘Databases’, even a specified database, such as 'AdventureWorks2008'. However, if he wants to access the tables in this database with the next location ‘Tables’, it will fail to, since he has no permission to access the objects in this database. You can receive an error message as “The database AdventureWorks2008 is not accessible.” via SQL Server Management Studio.

---

Stephanie Lv

TechNet Community Support