This post talks about how to create a new permission level for assigning users the right to manage permissions while maintaining the principal of least privilege. In most scenarios users who can manage permissions are also given owner rights to the site. Of course a user who can manage permissions can give themselves owner rights, but by default I needed to create a SharePoint security group to which I could add users that would then be allowed to manage the permissions at sites they would otherwise have no access to.
Creating a permission level that gives users the ability to manage permissions most likely involves more than providing the ‘Manage Permissions’ base permission. If you want to allows users with this permission level to add users to SharePoint groups (or remove them) then you will also need to add the ‘Add List Items’ and ‘Delete List Items’ along with the ‘View Application Pages’ base permissions.
“Manage Permissions” permission level base permissions.
With all of these base permissions you can expect a user with otherwise no access to a site to be able to manage site, list, and item permissions as well as manage the membership of the SharePoint security groups of which they are a member. This last point assumes that the SharePoint security groups are configured such that “Who can edit the membership of this group?” is set to “Group Members”.
SharePoint group set-up
Another option is to only provide the “Manage Permissions” base permission and ensure that the security group is the group owner of all the groups which they require the ability to manage membership of. The downside of this is that you must then remove the ‘Owners’ group from this role.
I suspect this is a very rare case I’m describing/solving here, and I’m doubtful this post will ever help anyone. Please let me know if it did!
I have had a hard time creating data connections with an Access 2013 App database. After a good few hours spent scouring the internet for a solution, and a good few more hours uncovering a “solution” that is underwhelming at best, I am happy to share with you my findings. I really hope that someone will leave a comment with a better solution at some point in the future.
This blog post will provide step-by-step guide on how to achieve a data connection from an Excel workbook (which can be hosted in SharePoint) to the SQL database behind an Access 2013 App. Once this is achieved, a good BI developer should have no trouble visualising the data captured via the Access App with the help of pivot tables, slicing and graphing.
The first step is to identify the server address and database to connect to along with the credentials required to authenticate.
This can be done by navigating to the Access App, clicking the ‘settings’ icon, then clicking ‘Customize in Access’
Launching the app database in Access
Download the .accdw file and open it to launch access
Click ‘FILE’ in the ribbon
Click ‘Manage’
In the drop-down menu ensure that ‘From Any Location’ and ‘Enable Read-Only Connection’ are highlighted with pink squares. If not, click them
Determining the Access database location and credentials
Click ‘View Read-Only Connection Information’
Take note of Server, Database, UserName, and Password from this dialog as you will need them all later
Access connection information dialog
Next we use this information to create the data connection.
Launch Excel
Create a new external data connection ‘From Data Connection Wizard’
Launching the Excel data connection wizard
Click ‘Other/Advanced’, then ‘Next’
Click ‘SQL Server Native Client 11.0’, then ‘Next’
On the ‘Data Link Properties’ dialog, uncheck the ‘Blank Password’ box and check the ‘Allow saving password’ box, then input the server name, user name, password, and database
Configuring the data connection. Ensure you provide the database
Test the connection, you should see a dialog box with ‘Test Connection Succeeded’
Note that it is when you attempt to make a data connection without providing the database that you get the following error which I bet lead to to this post:
Cannot open server ‘xxxxxxxxxx’ requested by the login. Client with IP address ‘00.000.000.000’ is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.
You can now click ‘Ok’
Uncheck the ‘Use Trusted Connection’ checkbox and replace the existing password with the correct one. Click ‘Ok’
Select a table and click ‘Next’. You can get fancy here later, let’s just get it working first.
Click ‘Finish’
Click ‘Ok’
The data connection will fail with the following error:
Initialization of the data source failed. Check the database server or contact your administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new data source to connect to the database.
The final frustration!
On the next dialog, uncheck the ‘Use Trusted Connection’ checkbox and replace the existing password with the correct one. Click ‘Ok’.
The second time it works. This process of providing the connection credentials twice is required not only upon the creation of the connection but also every time the data needs to be refreshed. It makes for a rather poor UX and it is a pretty awful scenario to have to explain to a client.
I really want to believe that there is a setting (most probably under the ‘All’ tab on the ‘Data Link Properties’ dialog) that will workaround this issue however I am yet to find it. Please leave a comment if you find a solution to this issue.
In SharePoint 2013, especially when on Office 365, you probably want to take advantage of Office Web Apps (OWA) to open Office documents (Word, Excel, PowerPoint, etc) in the browser.
EDIT: In SharePoint Online the best way to achieve this is by adding ‘web=1’ as a query string parameter to the file URL. E.g. https://tenant.sharepoint.com/Documents/file.docx?web=1
URL to ‘Open in brower’
A common scenario could be an implementation of the “Feedback (SharePoint 2010)” workflow which creates a task with accompanying email for many users. Some of these users may not be able to open Office documents on their client. Unfortunately, the default email content provides a link which only allows the user to download the document (which they are then unable to view) and provides no link back to the document library to find the document in a more manual fashion. In this particular scenario SharePoint Designer (SPD) is a very useful tool to update the email content with a link that allows users to take advantage of OWA. I am not going to go into how to update the workflow using SPD, I will only discuss the link URL format itself.
The URL format you are looking for is: <Site URL>/_layouts/15/WopiFrame.aspx?sourcedoc=<Doc URL>&action=default
Some things of note:
If you inspect the href of a document in a library you will notice that the sourcedoc parameter is not a URL but GUID. This GUID is NOT the unique ID of the document. Providing the unique ID of the document instead of the URL will fail (or potentially display another document).
You can provide either an absolute URL or a server relative URL for the source doc, and ensure it is encoded.
In case you are using SPD the link address you want is: [%Workflow Context:Current Site URL%]/_layouts/15/WopiFrame.aspx?sourcedoc=[%Current Item:Encoded Absolute URL%]&action=default
EDIT 31/30/2015: I’ve been advised by a colleague that this only works when the Site URL and Doc URL are both relative to the same site. This appears to be true in SharePoint Online, however I am almost certain this was not an issue when I wrote this article (I no longer have access to that environment).
If you have stuggled to get outlining (Visual Studio’s ability to collapse/expand sections of a document) working when editing master pages there is a very simple solution. You will notice that some options such as line numbering, when toggled on/off for the HTML editor, are visible when editing master pages. However, there is something ‘special’ about master pages that prevents all the standard HTML editor settings applying (or functioning) correctly with them. As such, Visual Studio provides a Master Page Editior which solves these issues, yay!
Unfortunately, by default master pages are opened using the HTML editor rather than the Master Page editor. To get around this please take the following actions:
1. Right click a master page to open via the solution explorer:
2. Click ‘Open with…’
3. Select ‘Master Page Editor’
4. Click ‘Set as Default’
5. Click ‘Ok’
Open master pages in the Master Page Editor
If you have been playing around under ‘Tools > Options > Text Editor’ and explicity assigning the master extension to the HTML editor (or anything similar) in a futile attempt to get this working, remember to undo your changes as they may interfere with lauching the Master Page Editor by default option.
I debated as to whether to share this tip or not for the sole reason that I’m embarrassed. I just did something that I don’t believe I haven’t done years ago. TFS supports a “Was Ever” operator for work item queries. Using this you can very easily create a query to display all work items which you have ever interacted with. I find this very useful. See the screen shot for how I use it in the simplest case.
