Posted on

Solution: Power Automate “No database found” error for business process flows, process advisor, and AI Builder

If you’re trying to build business process flows, use Process Advisor, or use AI Builder in Power Automate, you’re going to need a database established in the intended environment first. If you don’t have a database in the environment yet, you’ll get an error as seen below:

  • Business process flow requires a Microsoft Dataverse database. Try a different environment or create a new one to start using business process flow.
  • You need a database to use process advisor. Create a database, or switch to an environment that has one.
  • AI Builder requires a Dataverse database. Create your database to start using AI models.

In the following sections, I’ll detail how to:

  • Switch to a different environment
  • Add a database to your current (or any) environment
  • Create a new environment with a database

Switch to a different environment

Your organization could already have multiple environments. Always check with your admins before making any uncertain decisions because environments could be used for specific data types, processes, geography compliance restrictions, etc. You may or may not have access to all of your organization’s environments depending on your specific organization’s governance and configuration.

Let’s assume you do have multiple environments and you’ve discussed with your admin or governance team which environments are appropriate for your specific need or project. To switch to a different environment that might have a usable database, click on the name of your current environment in Power Automate in the upper right, then choose the other environment from the side panel.

Click to enlarge

Add a database to your environment

You may choose to just stay in your current environment and add a database to it. If that’s the case, go ahead and click on Create a database and follow the right side panel’s wizard to complete the process.

You can also add databases via the Power Platform admin center if you can access it.

Create a new environment with a database

Let’s assume your organization hasn’t yet created any additional environments you could use other than the default one that came with your tenant (which obviously doesn’t have a database or you wouldn’t be here 😄). If you don’t want to create the database in the default environment, you may wish to create a new environment with a new database.

Check out this other post for help in creating a new environment that includes a Dataverse database.

Posted on

Solution: This environment can’t be created because your org (tenant) needs at least 1 GB of database capacity

colorful human shaped wooden blocks in a bowl

Did you run out of capacity in your Dataverse environments from Power Automate and Power Apps usage? You have several options, and I’ll cover four in this post:

  • On a trial? Change the environment type
  • Purchase a capacity add-on
  • Free up space
  • Delete unused environments

Not what you’re looking for? Check out this detailed information on Dataverse storage capacity.

On a per user plan trial?

Change the environment type from Production to Trial. This will allow you to proceed with provisioning the environment for your trial purposes. You can later convert this to Production, but only if you have more database capacity when you’re ready to do so.

Click to enlarge – change the environment type from Production to Trial when creating a new environment as a user.

Purchase a capacity add-on

Not on a trial? Just out of space? Maybe it’s time to purchase a capacity add-on. View full documentation for more information on this, but here are the basic steps to purchase a capacity add-on:

As a global admin, go to the Microsoft 365 admin center (https://admin.microsoft.com). Then choose Billing > Purchase services from the left nav.

In the search bar, search for capacity and choose the type of capacity you’d like to add for your organization (i.e. Power Apps portals page views, AI Builder, etc.).

Click to enlarge

Choose Details for the capacity type you’re seeking to expand and complete your purchase.

Click to enlarge

You should also be sure to check out these two resources once you’ve completed your purchase:

Free up space

Just trying to clean up a bit? Start your evaluation by going to the Power Platform admin center’s Capacity page to see what’s using up the most space. Are there things you could clean up? Notice any unusual usage?

Click to enlarge

Depending on what you find in the Capacity page, you may find yourself wanting to look more closely at environments. To do this, select the Environments node from the left-hand nav.

From Environments, you can select an environment and choose to view its resources which will give you a good idea of what might be using more than anticipated. Perhaps a flow or app just needs adjusted. You could, for example, open a listed environment and choose Resources > Flows, examine the flows using the environment, see their owners, and even disable the flows until further action can be taken to address the underlying issue.

Delete an environment

See any environments that could be deleted? Just keep in mind a deleted environment takes its resources and backups too – so consider any flows, apps, etc. that might need updated to use a different environment first.

If you do determine there are unused and unneeded environments in your organization, you can delete them from the Power Platform admin center.

  1. Log in to the Power Platform admin center
  2. Click on Environments on the left nav if not already there
  3. Select the environment that you wish to delete, and choose Delete (can’t delete your default environment, though)
Click to enlarge

Environment owners have 7 days from deletion to recover it if they wish.

Posted on

Solution: Creating an environment requires a Power Apps per user plan or may be done by an admin in Power Platform admin center

time lapse photo of stars on night

If you’re attempting to use Solutions, AI Builder, certain Power Apps templates, etc. you may run into a situation where you’re working in an environment without a Dataverse (formerly known as Common Data Service) database. This will prompt you to create a new environment or database as a power user (if allowed).

You can add a database to your current environment, or create a new environment with a database. In this post, I’ll cover the steps to create a new environment.

To proceed with creating an environment, you’ll need a Power Apps per user plan or should be an admin. I’ll cover both methods below in the following sections. You can also see this detailed documentation on who is able to create new environments.

Create a new environment with a Power Apps per user plan

Once you’ve been licensed with a Power Apps per user plan, you’ll be able to create a solution and use existing or create new environments unless your admins have limited who can create new environments.

If you’re allowed, you can simply begin building your flow or app as you normally would, but now that you’re licensed appropriately, you’ll be able to choose to create a new environment in-context as you go. For example, let’s say there are no environments with databases you can use for a new Assets Checkout app you want to build from a template in Power Apps. Simply begin building the app from template, and choose to Create new environment when prompted.

Click to enlarge – prompt received when creating the model-driven app template named Assets checkout from Power Apps

Then in the panel that appears to the right, give the environment a name, choose a data region, and environment type. Then choose Create environment.

Choose whether you want to create a database with this new environment or create it without one. In this scenario, Power Apps is telling us we need a database so we’re going to choose to create our environment WITH a database. Click Create database.

Click to enlarge – prompt to create a database with our new environment.

Verify your currency and language. And if you’re creating a sample app (as we are in this scenario) you’ll also decide whether to bring in the sample data with that template. When satisfied, click Create my database.

Now as you’re building solutions in the Power Platform, you can switch between environments by selecting the name of the active environment and choosing the environment in which you wish to build (or utilize data from).

Click to enlarge

Now let’s create an environment from the Power Platform admin center as an admin.

Create a new environment as an admin in the Power Platform admin center

This is arguably the better method to use at an organization level to make sure your environments have a consistent naming convention, no efforts are duplicated, and boundaries are clear from one environment to another. With each environment potentially belonging to a different geography depending on how it was set up, this could be a significant compliance concern for some sectors. If you need to restrict environment creation to admins only, check out this other post. Otherwise, let’s proceed with creating a new environment for your users from the Power Platform admin center.

First, log in to the Power Platform admin center and choose Environments on the left nav if you’re not already there. Then, click New.

Click to enlarge

Name and describe your new environment, and choose the region in which its data needs to be stored. You’ll also choose type (Trial, Sandbox, Production, etc.). If this environment is going to be used to store and use data, you’ll want to also enable database creation (creates a Dataverse database). Click Next when ready.

Click to enlarge

Then choose the language, currency, and additional options (including restricting usage to a specific security group). Click Save when ready.

Click to enlarge

You (and/or your organization’s users whom are allowed to use the environment) will now be able to connect to the environment and any of the tables within its Dataverse database. This Dataverse connection can be used in Dynamics 365 apps, Power Apps portals and apps, and Power Automate flows.

Click to enlarge
Posted on

Restrict creation of new Power Platform environments to admins only

A person buried under a pile of boxes

Note: This post applies to traditional environments, not Dataverse for Teams environments. Thanks to Loryan Strant for adding that clarification.

As long as someone in your organization is on a Power Apps or Power Automate per-user plan, they may have permission to create their own environment (unless you’ve already limited it previously in your organization). These environments can be used and shared by Dynamics 365, Power Apps portals and apps, and Power Automate flows. Environments can house multiple Dataverse tables (which can also be provisioned by a power user with appropriate licensing). Power Platform apps and Dynamics 365 can then connect to, read from, and write to these tables which would likely be shared across multiple applications/processes.

For example, if Megan Bowen is licensed on a Power Apps per-user plan she would, by default, be able to create new environments as she wished. And with the right training and deployed best practices, this may not be an issue. But if training isn’t provided and users are creating environments left and right, it may be time to limit who can create them (regardless of their licensing) until proper training and governance can be deployed.

So if you wish to manage environments centrally and prevent environment sprawl, you will want to limit the ability of users in your organization so they can’t create their own environments. Doing this may slow their individual productivity, but could also prevent duplicated efforts, inconsistent data organizationally, scattered naming conventions, and more. As long as you replace this restriction with a formal and effective governance policy, request form, etc. it will minimize disruption to your colleagues’ productivity.

Ready to do this? Let’s go over the steps to limit Microsoft Power Platform environment creation and management.

Limit new environment creation via the Power Platform admin center

As a Power Platform admin, sign in to the Power Platform admin center.

Choose the Settings gear in the upper right corner and choose Power Platform settings.

Click to enlarge

Then change Who can create production and sandbox environments to Only specific admins. As seen in the tooltip in the following screenshot, this limits creation to:

  • Global admins
  • Dynamics 365 service admins
  • Power Platform service admins and
  • Delegated admins

Click Save and you’re finished!

Note that this doesn’t remove existing environments or limit the abilities of their creator(s) to continue managing pre-existing environments. This setting will only apply to new environments and prevent additional unwanted sprawl.

Prefer to restrict environment creation and management via PowerShell? Check out this documentation.

Posted on

SharePoint 2019 Solution: “Sorry, apps are turned off. If you know who runs the server, tell them to enable apps.”

Photo by Artem Beliaikin from Pexels

Without an App Catalog and a setting enabled, your users may run into the following error when attempting to access the SharePoint Store from the “Add an app” dialog:

“Sorry, apps are turned off. If you know who runs the server, tell them to enable apps.”

If you’ve run into this issue and are a farm admin, you can enable the app store and ability in SharePoint 2019 by following these steps. If you’ve already created the App Catalog site collection, skip to step 4.

1. Log onto your central admin server and open central admin

Click to enlarge

2. Choose Apps > Manage App Catalog. Make sure the Web Application shown is the correct web application then click OK to create a new App Catalog (or enter a URL for one if you’ve already created one)

Click to enlarge

3. Set the App Catalog site name and description, URL, admin, and then end users who should see apps in the catalog.

Click to enlarge

4. Once you have an App Catalog, go back to Apps > Configure store settings.

Click to enlarge

5. Confirm the Web Application shown is the correct web app, then change App Purchases to Yes. Save your changes.

Now when users who were granted access to view apps in the store choose SharePoint Store from the “Add an app” dialog, they’ll be able to get marketplace apps as well.

Click to enlarge
Posted on

Export a report of all SharePoint lists, libraries, discussion boards, calendars, and more from all site collections with PowerShell

Photo by Tiger Lily from Pexels

I wanted to get an idea of how many people were using discussion boards in my SharePoint Server environment and in what sites. I modified a script I found to create the sort of inventory list I needed and ended with a script that:

  • Provides a list within PowerShell of each URL, site name, and list name found matching a specific list type.
  • Provides a total count and save location confirmation at the end.
  • Exports a CSV file of the details to a specified location.

Find the Template ID for the list you’re searching for

List template ID options are as follows, taken from this Docs article. You’ll need the Template ID of the type of list you wish to search in your SharePoint environment.

List template typeTemplate IDBase typeDescription
Custom List1000A basic list that can be adapted for multiple purposes.
Document Library1011Contains a list of documents and other files.
Survey1024Fields (2) on a survey list represent questions that are asked of survey participants. Items in a list represent a set of responses to a survey.
Links1030Contains a list of hyperlinks and their descriptions.
Announcements1040Contains a set of simple announcements.
Contacts1050Contains a list of contacts used for tracking people in a site (2).
Calendar1060Contains a list of single and recurring events. An events list has special views for displaying events on a calendar.
Tasks1070Contains a list of items that represent finished and pending work items.
Discussion Board1080Contains discussions entries and their replies.
Picture Library1091Contains a library adapted for storing and viewing digital pictures.
DataSources1101Contains data connection description files.
Form Library1151Contains XML documents. An XML form library can also contain templates for displaying and editing XML files through forms, as well as rules for specifying how XML data is converted to and from list items.
No Code Workflows1171Contains additional workflow definitions that describe new processes that can be used in lists. These workflow definitions do not contain advanced code-based extensions.
Custom Workflow Process1180Contains a list used to support custom workflow process actions.
Wiki Page Library1191Contains a set of editable Web pages.
CustomGrid1200Contains a set of list items with a grid-editing view.
No Code Public Workflows1221A gallery for storing workflow definitions that do not contain advanced code-based extensions.
Workflow History1400Contains a set of history items for instances of workflows.
Project Tasks1500Contains a list of tasks with specialized views of task data in the form of Gantt chart.
Public Workflows External List6000An external list for viewing the data of an external content type.
Issues Tracking11005Contains a list of items to track issues.
Table of list template IDs taken from https://docs.microsoft.com/en-us/openspecs/sharepoint_protocols/ms-wssts/8bf797af-288c-4a1d-a14b-cf5394e636cf.

Modify the PowerShell script to suit your needs

Once you have the list template ID you wish to query from the table above, modify the PowerShell script found below as follows:

  1. Replace the $ListTemplateId variable value with your desired template ID.
  2. Replace the -WebApplication parameter’s URL value with your own SharePoint web app address.
  3. Replace the $saveCSVLocation value inside the double-quotes with the path to where you’d like the export to be written.

Get all SharePoint list types in one report

Want it all? You could modify the script by replacing -eq $ListTemplateId on line 15 with -gt 0. Then replace line 21 with:

@{Site=$web.Title;ListName=$list.Title;Type=$list.BaseTemplate;URL=($web.Url + "/" + $i.DefaultView.Url)}) | % { New-Object object | Add-Member -NotePropertyMembers $_ -PassThru }

And replace line 25 with:

write-Output ($web.Title + "," + $list.Title + "," + $list.BaseTemplate + "," + $web.Url + "/" + $i.DefaultView.Url) | Out-File $SaveLocationFinal -Append

Now your report will include all list types, and an added column to specify that list type. Note that this will take a while to run in larger environments.

You could also modify the script to only display in PowerShell (delete lines 23-25 and 31-33) or only export to CSV (delete lines 19-22 and 31-33) but I wanted both outputs for my purposes.

Idea: Take it further with Power BI

Take this to the next level by automating the PowerShell script to run on a schedule exporting results to a folder Power BI reads on an automatic refresh. This is an easy way to get a hands-off dashboard of SharePoint usage by list type.

Posted on

Create a custom permission level in SharePoint

Photo by Markus Spiske from Pexels

I’m often asked for a way to modify permissions beyond what’s available out of the box, but without using workflows.

There are settings that allow item-level permissions in lists (List Settings > Advanced Settings > Item-level Permissions) so that users can only see and/or edit their own items, but this may not solve your need. If so, ta-da! If not, keep reading.

Click to enlarge

In this post, I’ll cover one solution in which we create a custom permission level at the site level that we’ll assign to a group on a specific list’s permissions. This new, custom permission level will be the same as the Contribute level (out of the box) but removes the ability for users to delete items or versions.

Creating a custom permission level involves a couple main steps:

  1. Create the new permission level.
  2. Change permissions on the list so that the group of users who should have the new permission level are assigned the new permission level.

Create the new, custom permission level

1. Go to Site Settings

2. Under Users and Permissions, select Site permissions

3. Click Permission Levels

4. You could click Add a Permission Level but I typically prefer to copy an existing level (like Contribute) and just make a couple small changes. For this tutorial, I’m going to select Contribute.

Click to enlarge

If copying a level, scroll down to the bottom after selecting a level and click Copy Permission Level.

5. Name and describe the new permission level, then check and uncheck as needed to create the permission level desired. In my example, I want to copy Contribute, but remove the delete ability so I’ve unchecked the two options involving deletion of items and versions.

Click to enlarge

6. Scroll down and click Create.

Change permissions on the list

Now we need to assign our new permission level to users on the list for which we’re preventing deletion.

1. Go to List Settings.

2. Under Permissions and Management, select Permissions for this list.

3. Select the box next to the name of the group for which you’re modifying permissions.

4. Click Edit User Permissions from the top ribbon menu.

5. Uncheck the current permission level assigned to the group, and check the new custom permission level.

6. Click OK.

Posted on

Change the email address used for Access Requests on all SharePoint sites and subsites in a web app using PowerShell

Photo by Karolina Grabowska from Pexels

Perhaps you’ve changed SharePoint administrators or a site owner or two recently. Where are the SharePoint site Access Requests they were receiving now going?

This post covers two PowerShell methods of updating the email address used across all sites in bulk:

  • Replace the email address used on ALL sites, no exceptions (reset all requests throughout the web app to be sent to one address)
  • Change all instances of a specific address to a replacement across ALL sites (i.e. replace the former site owner’s address used in 12 sites’ Access Request Settings with the new site owner’s address)

The second method is particularly nice because it eliminates any guesswork involved in wondering where the former admin/owner may have been listed as the recipient.

Replace the email address used on ALL sites

To modify the email address used for all SharePoint sites and subsites in a web app, run the PowerShell script below from a SharePoint server. You’ll need to replace the $webapp and $requestemail values at the top.

Caution: This action cannot be undone. It replaces the Access Request email on all sites and subsites.

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue 
  
$webapp = Get-SPWebApplication https://sharepoint.contoso.com  
$requestemail = "new-request-recipient@demo.com"   
  
foreach($site in $webapp.Sites)  
{  
   foreach($web in $site.AllWebs)  
   {  
     $url = $web.url  
     Write-host "Checking "$url  
     if (!$web.HasUniquePerm)  
     {  
            Write-Host "Site inherits Access Request settings from parent." -ForegroundColor Yellow  
     }  
     else  
     {  
       if($web.RequestAccessEnabled)  
       {  
            Write-Host "Site utilizes Access Requests."   
            $web.RequestAccessEmail = $requestemail  
            $web.Update()  
            Write-Host "Email changed to " $requestemail -ForegroundColor Green
        }  
            else  
      {  
            Write-Host "Site is not utilizing Access Requests." -ForegroundColor Yellow  
      }  
   }  }
}

Replace all instances of a specific user across all sites in the web app

Perhaps a particular individual left their site owner/admin role and you just want to replace any instance of THAT user in Access Request settings throughout the web app. In that case use the following script instead (updating the three parameters at the top, $webapp, $oldrequestemail, and $newrequestemail):

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue 

$webapp = Get-SPWebApplication https://sharepoint.contoso.com
$oldrequestemail = "old-request-email@contoso.com"  
$newrequestemail = "new-request-email@contoso.com"   

foreach($site in $webapp.Sites)  
{  
   foreach($web in $site.AllWebs)  
   {  
        $url = $web.url  
          Write-host "Checking "$url  
          if (!$web.HasUniquePerm)  
          {  
                 Write-Host "Site inherits Access Request settings from parent." -ForegroundColor Yellow  
     }  
          else  
     {  
            if($web.RequestAccessEnabled)  
                   {  
                   if($web.RequestAccessEmail -eq $oldrequestemail)
            {
            Write-Host "Site utilizes Access Requests sent to old email ("$web.RequestAccessEmail")." -ForegroundColor Red
            $web.RequestAccessEmail = $newrequestemail  
            $web.Update()  
            Write-Host "Email changed to" $newrequestemail -ForegroundColor Green
        }  
                    else 
               {            
                    Write-Host "Email ("$web.RequestAccessEmail") does not match old email address. No change made." -ForegroundColor Yellow
               }}
            else  
      {  
            Write-Host "Site is not utilizing Access Requests." -ForegroundColor Yellow  
      }  

   }  
   }  
   }

Note: If you’ve changed the Access Request recipient and the new person is receiving a “Sorry, this site hasn’t been shared with you” error when attempting to approve requests, check out this post for help.

Credit and gratitude to Ketak Bhalsing and his post on bulk-updating for pointing me in the right direction on this one.

Posted on

Solution: SharePoint site owner with full control unable to approve access requests; site is missing a default members group

In this post I’ll cover two symptoms commonly seen when subsites evolve from inheriting permissions (using existing groups) to being given unique permissions (having their own groups at the site’s level).

Symptoms

  • A site owner with full control gets “Sorry, this site hasn’t been shared with you” when trying to approve access requests.
  • When reviewing Access Request Settings, a user or owner sees the message “Members cannot share this site because this site is missing a default members group.”

Cause

Chances are the site was never set up with default, unique permissions groups. Perhaps the creator of the site chose to inherit permissions from the parent (using existing groups from a hierarchical level higher than the new site), then later decided to manually build out groups that resemble the traditional visitors, members, and owners groups at the new site’s level. Or perhaps the default groups were deleted. Either way, the following solution should set it straight:

Solution

We need to either officially designate or build new default groups for the site, using the same dialog you see when creating a new site. Since we can’t “reconfigure” the site with a wizard, we need to manipulate the site’s URL a bit to get to the configuration screen we’re looking for.

Add “_layouts/15/permsetup.aspx” to the end of the site URL. For example, it may resemble sharepoint.contoso.com/sites/ABC/EA/_layouts/15/permsetup.aspx. This takes you to the permissions setup page.

IF YOU HAVE GROUPS YOU WANT TO SET AS THE DEFAULTS

Perhaps after site creation, you created groups intended to be used like the visitors, members, and owners groups. Go to your site’s _layouts/15/permsetup.aspx page and simply:

Leave “Use an existing group” selected, and change the dropdown for each to the groups that were created and intended to be the new defaults. Click OK when finished. This will make them “official.”

IF YOU DON’T HAVE GROUPS YOU WANT TO SET AS DEFAULT

Change “Use an existing group” to “Create a new group” for at least the Members and Owners options. Here you can add the appropriate persons to each group, or add them at a later time via Site Settings > Site Permissions. Be sure to add your owners (approvers/permissions managers) to the new owner group.

Posted on

Disable modern page comments globally for all sites in SharePoint 2019 and SharePoint Online

Photo by Lukas from Pexels

Those of you with Office 365/SharePoint Online have a simple path to disabling modern page commenting via the admin center. But for those of you, like myself, who also work in SharePoint 2019, your method involves PowerShell and is a bit more laborious. In this post, I’ll cover both methods: Disabling comments via PowerShell for SharePoint 2019 (server) and then via the admin center for SharePoint Online (O365).

Firstly, why disallow commenting altogether instead of giving site owners and page editors the choice? Some organizations have compliance regulations that require any sort of conversational transaction to meet certain criteria. Perhaps you only need to disable commenting temporarily while you build a case to prove compliance. Or perhaps you’ve determined it’s not compliant or have some other reason for wishing to globally disable page comments.

No matter your reasoning, here’s the how:

Disable modern page/news comments globally in SharePoint 2019 via PowerShell

The script below will iterate through all site collections in a web application, and all of the subsites within each site collection, and turn off page comments for sites that haven’t already had them disabled. This does not delete page comments. If you re-enable the feature later, the comments that were there previously will be restored.

Log onto a SharePoint server and run the following PowerShell script, replacing the site URL with your own web app’s URL:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
Get-SPWebApplication "https://sharepoint.contoso.com" | Get-SPSite –Limit All | Get-SPWeb -Limit All | ForEach-Object {
    if($_.CommentsOnSitePagesDisabled = "False")
    {
        $_.CommentsOnSitePagesDisabled = $True
        $_.Update()
        Write-Host "Disabled page comments on " $_.URL -ForegroundColor Green
    }
    else
    {
        Write-Host "Page comments already disabled on " $_.URL -ForegroundColor Yellow
    }
}

If you want a read-out to validate current status (False = not disabled, True = disabled), you can run the following script (again, replacing the URL with the web app of your own):

Get-SPWebApplication "https://sharepoint.contoso.com" | Get-SPSite –Limit All | Get-SPWeb -Limit All | ForEach-Object {
Write-Host $_.CommentsOnSitePagesDisabled $_.URL
}

Disable modern page/news comments globally in SharePoint Online/O365

1. Go to the SharePoint admin center at yourdomain-admin.sharepoint.com and select Settings from the left navigation menu.

2. Select Pages.

3. Uncheck Allow commenting on modern pages and click Save.