Prerequisites
You will need users with the following permissions to interact with SharePoint:
- Create a designated domain user in the domain in which SharePoint works (for example, siq_wss).
- Run "SPOnPremPermissions_ConfgAndContentDB.sql" script on the SQL server hosting the SharePoint Configuration database. This will grant necessary permissions for DAS to properly access Sharepoint information. If all content databases reside on the same server, there are no additional steps necessary. If there are content databases located on separate servers, run "SPOnPremPermissions_ContentDBOnly.sql" script on each content database as directed by script.
Note
SPOnPremPermissions_ConfgAndContentDB.sql provides the option to exclude Content databases which you wish to not be accessed by DAS. All content databases which should not be included, regardless of location, should be included in this script
SPOnPremPermissions_ConfgAndContentDB.sql
``` /*
For the servers that host both: a config DB and 1 or more content DBs. Can be ran all at once or portion by portion.
*/
/*
This script creates a new login for a SharePoint application
with all required permissions for the File Access Manager services.
INSTRUCTIONS:
------------
(*) Replace the "%USERNAME%" variable with the appropriate user name (e.g. DOMAIN\USER).
(*) Replace the "%CONFIG_DB%" variable with the appropriate config database name (usually "SharePoint_Config").
(*) Optional - Exclude content databases by adding rows to the "@excludedContentDBS" table variable (see last section).
*/
SET NOCOUNT ON
/**********************************************************/
/* CREATE THE NEW USER LOGIN */
/**********************************************************/
USE [master]
GO
IF NOT EXISTS (SELECT [loginname] FROM [master].[dbo].[syslogins] WHERE [name] = '%USERNAME%')
BEGIN
CREATE LOGIN [%USERNAME%] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
PRINT 'Created a new login - ''%USERNAME%'''
END
/**********************************************************/
/* GRANT ACCESS TO THE "Config" DATABASE */
/**********************************************************/
USE [%CONFIG_DB%]
GO
IF (USER_ID('%USERNAME%') IS NULL)
BEGIN
CREATE USER [%USERNAME%] FOR LOGIN [%USERNAME%] WITH DEFAULT_SCHEMA=[%USERNAME%]
END
GRANT EXECUTE ON [dbo].[proc_GetVersion] TO [%USERNAME%]
GO
GRANT EXECUTE ON [dbo].[proc_getSiteNames] TO [%USERNAME%]
GO
GRANT EXECUTE ON [dbo].[proc_getObject] TO [%USERNAME%]
GO
GRANT EXECUTE ON [dbo].[proc_getObjectsByClass] TO [%USERNAME%]
GO
GRANT EXECUTE ON [dbo].[proc_getObjectsByBaseClass] TO [%USERNAME%]
GO
GRANT EXECUTE ON [dbo].[proc_getSiteMap] TO [%USERNAME%]
GO
GRANT EXECUTE ON [dbo].[proc_getSiteMapById] TO [%USERNAME%]
GO
GRANT SELECT ON [dbo].[SiteMapVisible] TO [%USERNAME%]
GO
PRINT 'Successfully granted permissions to ''%CONFIG_DB%'''
/**********************************************************/
/* GRANT ACCESS TO THE "Content" DATABASES */
/**********************************************************/
DECLARE @excludedContentDBS TABLE ([name] NVARCHAR(MAX))
/*
Add rows to exclude certain Content Databases by name, for example:
INSERT INTO @excludedContentDBS VALUES ('WSS_Content_Excluded')
*/
DECLARE @grantCmd NVARCHAR(MAX) =
'
IF (USER_ID(''%USERNAME%'') IS NULL)
BEGIN
CREATE USER [%USERNAME%] FOR LOGIN [%USERNAME%] WITH DEFAULT_SCHEMA=[%USERNAME%]
END
-- Used both by the Crawler/Permissions Collector to fetch objects such as Sites, Webs, Lists etc.
GRANT EXECUTE ON [dbo].[proc_GetTpWebMetaDataAndListMetaData] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_GetWebExtendedMetaData] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_GetUrlDocId] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_GetWebUrlFromId] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_EnumLists] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_ListChildWebs] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_ListUrls] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_GetListMetaDataAndEventReceivers] TO [%USERNAME%]
-- Used by the Permissions Collector to retrieve objects permissions, groups, users etc.
GRANT EXECUTE ON [dbo].[proc_SecListSiteGroupsContainingUser] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecGetItemsWithUniquePermissions] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecGetSecurityInfo] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecGetWebsAndListsWithUniquePermissions] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecListScopeUsers] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecListAllWebMembers] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecListScopeGroups] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecListAllSiteMembers] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecGetRoleBindingsForAllPrincipals] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecListSiteGroups] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecGetRoleDefs] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecGetSiteAdmins] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecListSiteGroupMembership] TO [%USERNAME%]
-- These tables are accessed by the Permissions Collector directly (not through a stored procedure).
-- They are used to retrieve list items data which do not have a dedicated Stored Procedure.
GRANT SELECT ON [dbo].[AllUserData] TO [%USERNAME%]
GRANT SELECT ON [dbo].[UserData] TO [%USERNAME%]
GRANT SELECT ON [dbo].[AllDocs] TO [%USERNAME%]
GRANT SELECT ON [dbo].[Docs] TO [%USERNAME%]
-- Used by the Activity Monitoring service to change audit flags and to get, purge and add audit entries
GRANT EXECUTE ON [dbo].[proc_GetAuditEntries] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_TrimAuditEntries] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_GetAuditMask] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SetAuditMask] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_AddAuditEntryUrl] TO [%USERNAME%]
'
-- Dynamically grant permissions for every content database
DECLARE @farmIds TABLE (id UNIQUEIDENTIFIER)
DECLARE @webServiceIds TABLE (id UNIQUEIDENTIFIER)
DECLARE @webApplicationIds TABLE (id UNIQUEIDENTIFIER)
DECLARE @contentDBSNames TABLE (Name NVARCHAR(MAX))
DECLARE @curr_id UNIQUEIDENTIFIER
-- Get all farms ids
INSERT INTO @farmIds
EXEC [dbo].[proc_getObjectsByClass] '674DA553-EA77-44A3-B9F8-3F70D786DE6A', null, null
-- Get all web services ids
DECLARE farms_cursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR SELECT id FROM @farmIds
OPEN farms_cursor
FETCH NEXT FROM farms_cursor INTO @curr_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @webServiceIds
EXEC [dbo].[proc_getObjectsByClass] '45AD2BF2-4E3E-46A1-B477-126944C0ACEF', @curr_id, ''
FETCH NEXT FROM farms_cursor INTO @curr_id
END
CLOSE farms_cursor
DEALLOCATE farms_cursor
-- Get all web applications ids
DECLARE web_services_cursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR SELECT id FROM @webServiceIds
OPEN web_services_cursor
FETCH NEXT FROM web_services_cursor INTO @curr_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @webApplicationIds
EXEC [dbo].[proc_getObjectsByBaseClass] '113FB569-7520-4651-8FC4-E9F4F5887618', @curr_id
FETCH NEXT FROM web_services_cursor INTO @curr_id
END
CLOSE web_services_cursor
DEALLOCATE web_services_cursor
-- Get all sites content databases
DECLARE web_apps_cursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR SELECT id FROM @webApplicationIds
OPEN web_apps_cursor
FETCH NEXT FROM web_apps_cursor INTO @curr_id
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @contentDBSNames
SELECT DISTINCT o.[Name]
FROM [dbo].[SiteMapVisible] s (NOLOCK)
INNER JOIN [dbo].[Objects] o (NOLOCK) ON o.[Id] = s.[DatabaseId]
WHERE s.[ApplicationId] = @curr_id
FETCH NEXT FROM web_apps_cursor INTO @curr_id
END
CLOSE web_apps_cursor
DEALLOCATE web_apps_cursor
-- Grant permissions for every content database
DECLARE @curr_db_name NVARCHAR(MAX)
DECLARE content_dbs_cursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR SELECT DISTINCT [Name] FROM @contentDBSNames WHERE [Name] NOT IN (SELECT [Name] FROM @excludedContentDBS)
OPEN content_dbs_cursor
FETCH NEXT FROM content_dbs_cursor INTO @curr_db_name
WHILE @@FETCH_STATUS = 0
BEGIN
-- Execute the grant permissions command
DECLARE @exec NVARCHAR(MAX) = '[' + @curr_db_name + '].[sys].[sp_executesql]'
EXEC @exec @grantCmd
PRINT 'Successfully granted permissions to content db ''' + @curr_db_name + ''''
FETCH NEXT FROM content_dbs_cursor INTO @curr_db_name
END
CLOSE content_dbs_cursor
DEALLOCATE content_dbs_cursor
PRINT 'Script execution completed successfully.'
-----------------------------------------------------------
```
SPOnPremPermissions_ContentDBOnly.sql
``` /*
For servers which are hosting Sharepoint Content database(s) which do not reside on the same server as the Sharepoint Configuration database.
NOTE: Follow steps chronologically and run each separately. Do not run the full script at once.
If more than 1 content DB hosted on the server: bottom portion will need to be edited and re-ran for each content DB on the server.
*/
/*
Step 1:
This portion creates a new login for a SharePoint application. Run once.
INSTRUCTIONS:
------------
(*) Replace the "%USERNAME%" variable with the appropriate user name (e.g. DOMAIN\USER).
*/
SET NOCOUNT ON
/**********************************************************/
/* CREATE THE NEW USER LOGIN */
/**********************************************************/
USE [master]
GO
IF NOT EXISTS (SELECT [loginname] FROM [master].[dbo].[syslogins] WHERE [name] = '%USERNAME%')
BEGIN
CREATE LOGIN [%USERNAME%] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
PRINT 'Created a new login - ''%USERNAME%'''
END
/*
Step 2:
This portion grants access to the content DB(s). May need to run more than once -- see below.
INSTRUCTIONS:
------------
(*) Replace the "%CONTENT_DB%" variable with the appropriate content database name (i.e. "WSS_Content").
(*) If you have more than one Content DB on the server: after successful run, edit %CONTENT_DB% with the next Content DB name and run again. Repeat until access granted to all Content DBs on the server.
*/
/**********************************************************/
/* GRANT ACCESS TO THE "Content" DATABASES */
/**********************************************************/
DECLARE @grantCmd NVARCHAR(MAX) =
'
IF (USER_ID(''%USERNAME%'') IS NULL)
BEGIN
CREATE USER [%USERNAME%] FOR LOGIN [%USERNAME%] WITH DEFAULT_SCHEMA=[%USERNAME%]
END
-- Used both by the Crawler/Permissions Collector to fetch objects such as Sites, Webs, Lists etc.
GRANT EXECUTE ON [dbo].[proc_GetTpWebMetaDataAndListMetaData] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_GetWebExtendedMetaData] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_GetUrlDocId] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_GetWebUrlFromId] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_EnumLists] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_ListChildWebs] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_ListUrls] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_GetListMetaDataAndEventReceivers] TO [%USERNAME%]
-- Used by the Permissions Collector to retrieve objects permissions, groups, users etc.
GRANT EXECUTE ON [dbo].[proc_SecListSiteGroupsContainingUser] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecGetItemsWithUniquePermissions] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecGetSecurityInfo] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecGetWebsAndListsWithUniquePermissions] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecListScopeUsers] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecListAllWebMembers] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecListScopeGroups] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecListAllSiteMembers] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecGetRoleBindingsForAllPrincipals] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecListSiteGroups] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecGetRoleDefs] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecGetSiteAdmins] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SecListSiteGroupMembership] TO [%USERNAME%]
-- These tables are accessed by the Permissions Collector directly (not through a stored procedure).
-- They are used to retrieve list items data which do not have a dedicated Stored Procedure.
GRANT SELECT ON [dbo].[AllUserData] TO [%USERNAME%]
GRANT SELECT ON [dbo].[UserData] TO [%USERNAME%]
GRANT SELECT ON [dbo].[AllDocs] TO [%USERNAME%]
GRANT SELECT ON [dbo].[Docs] TO [%USERNAME%]
-- Used by the Activity Monitoring service to change audit flags and to get, purge and add audit entries
GRANT EXECUTE ON [dbo].[proc_GetAuditEntries] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_TrimAuditEntries] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_GetAuditMask] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_SetAuditMask] TO [%USERNAME%]
GRANT EXECUTE ON [dbo].[proc_AddAuditEntryUrl] TO [%USERNAME%]
'
-- Execute the grant permissions command
DECLARE @exec NVARCHAR(MAX) = '[%CONTENT_DB%].[sys].[sp_executesql]'
EXEC @exec @grantCmd
PRINT 'Script execution completed successfully.'
-----------------------------------------------------------
```
-
Verify that the permissions were granted successfully The script should have the following messages:
- “Successfully granted permissions to [Configuration DB]”
- For each content database, a message “Successfully granted permissions to content db [Content DB Name]”
- “Script execution completed successfully”
-
If planning to utilize Data Classification, files that need to be classified are required to have read access. For ease of use, a user can be granted site collection read access allowing read to files that are associated to that site collection.
Communications Requirements
| Requirement | Source | Destination | Port |
|---|---|---|---|
| SharePoint Database Access | Resource Collector Virtual Appliance | SharePoint Databases | According to the specific DB definitions |
| Data Classification | Data Classification Virtual Appliance | SharePoint Farm | http & https as required |
Documentation Feedback
Feedback is provided as an informational resource only and does not form part of SailPoint’s official product documentation. SailPoint does not warrant or make any guarantees about the feedback (including without limitation as to its accuracy, relevance, or reliability). All feedback is subject to the terms set forth at https://developer.sailpoint.com/discuss/tos.