如何通过SQL在Power BI报表服务器数据库的Secdata表中批量创建用户报表权限手动条目？

Hey there, let's walk through how to manually create permission entries in the Secdata table for Power BI Report Server—but first, a huge red flag: directly modifying the report server's system tables is officially unsupported by Microsoft. Messing this up can corrupt your database, break existing permissions, or leave your server in an unusable state. Always back up your Report Server database first, and only do this if you've exhausted all UI-based options (like bulk permissions via the web portal or PowerShell) and fully understand the risks.

Alright, let's dive in:

Step 1: Understand Key Secdata Columns #

The Secdata table stores all security assignments, so you'll need to know these core fields:

• PolicyID: Links to the predefined or custom permission policy (e.g., Browser, Content Manager)
• PrincipalID: The unique ID of the user/group you're granting access to
• SecurableID: The unique ID of the report, folder, or dataset you're securing
• Inherited: Bit value (0 = explicit permission, 1 = inherited from parent folder)
• Permissions: Integer mask that matches the permission level defined in the linked Policies table

Step 2: Retrieve Required IDs #

You can't insert a valid entry without these three critical IDs—grab them from related tables first:

Get the User/Group PrincipalID #

Query the Users table to find the ID for your target user or AD group:

SELECT UserID AS PrincipalID, UserName, FullName
FROM Users
WHERE UserName = 'YOUR_DOMAIN\TargetUserOrGroup' -- replace with actual account

Get the Report/Folder SecurableID #

Find the ID of the item you want to grant access to (reports live in the Catalog table):

SELECT ItemID AS SecurableID, Name, Path
FROM Catalog
WHERE Name = 'Your Target Report Name' -- use Path for unique items with duplicate names

Get the PolicyID for Your Desired Permission Level #

Permissions are tied to policies (built-in or custom). To get the ID for a built-in role like Browser or Content Manager:

SELECT PolicyID, Name, Permissions
FROM Policies
WHERE Name IN ('Browser', 'Content Manager', 'Report Builder') -- adjust to your needs

If you need a custom permission set, create it first via the Report Server web portal, then run this query to get its ID.

Step 3: Insert the Permission Entry #

Once you have all three IDs, use an INSERT statement to add the entry. Here's an example for granting Browser access to a specific user for a report:

INSERT INTO Secdata (PolicyID, PrincipalID, SecurableID, Inherited, Permissions)
VALUES (
    1, -- Replace with your PolicyID (from the Policies query above)
    '1A2B3C4D-5E6F-7G8H-9I0J-K1L2M3N4O5P6', -- Replace with user's PrincipalID
    'P6O5N4M3-L2K1-J0I9-H8G7-F6E5D4C3B2A1', -- Replace with report's SecurableID
    0, -- 0 = explicit permission (ignores inheritance), 1 = inherited
    1 -- Match the Permissions value from the Policies table for your chosen policy
)

Pro tip: Double-check the Permissions value matches the one in the Policies table—using the wrong number will grant unintended access.

Step 4: Verify the Entry #

After inserting, confirm the permission exists and is correct with this query:

SELECT 
    u.UserName,
    c.Name AS SecurableItemName,
    p.Name AS PermissionPolicy,
    s.Inherited,
    s.Permissions
FROM Secdata s
JOIN Users u ON s.PrincipalID = u.UserID
JOIN Catalog c ON s.SecurableID = c.ItemID
JOIN Policies p ON s.PolicyID = p.PolicyID
WHERE u.UserName = 'YOUR_DOMAIN\TargetUserOrGroup'
AND c.Name = 'Your Target Report Name'

You should also test by logging in as the user to ensure they can access the report as expected.

Critical Reminders #

• Backup first: Always take a full backup of your Report Server database before touching any system tables.
• Unsupported risk: Microsoft won't troubleshoot issues caused by manual table edits—this is at your own risk.
• Bulk operations: For multiple users/reports, use INSERT ... SELECT to bulk create entries (e.g., join a list of users to the report ID), but test with a single entry first to avoid mistakes.
• Inheritance: If you set Inherited to 1, the permission will follow the parent folder's rules, but explicit permissions (0) take precedence.

内容的提问来源于stack exchange，提问作者Ganesh Padvekar