- Docs
- /
06 Jun 2022 16461 views 0 minutes to read Contributors
This article does not apply on version 0.8.11.24 when using the DatabaseStorage and FileStorage option.
When using tempDB storage option it will create the objects in the extended properties of the TempDB
To create folders in the object tree SQLTreeo Add-in makes use of stored procedures to store the information in the user-defined database(s) or the Tempdb database for dynamic folders when the database storage option is active.
The user or login accessing the SQL Server instance requires several priveleges to create the folders, the user needs access to the database and permission to make use of the Stored procedure sys.sp_addextendedproperty.
When the SQLTreeo SSMS Add-in is installed (Trial period) the following script can be run against the SQL Server instance to verify if the user/login has the required permissions/priveleges to create any folders.
T-SQLTransact-SQL
1234567891011121314151617181920212223242526272829303132333435363738 /* SQLTreeo SSMS Add-in script to check folder creationAuthor: SQLTreeoExecution path:1. Open SQL Server Management Studio.2. Open a new query window and select an user-defined database3. Copy-paste Batch 1,2,3 to the query window.4. Execute the 3 batches separately in sequence (first batch 1, after success batch 2, then run batch 3 to retrieve the results).5. Check if batch 3 returns 2 rows where the value column is identical to the @value parameter value.6. Refresh your object explorer, database objects and SQL Server Agent jobs objects are grouped in 1 folder each.7. Repeat step 2 to 6 again using the TempDB this time.8. If step 1-7 return without any errors and returning 2 rows this means you have sufficient permissions to create static/dynamic folders in your SQL Server instance.9. If step 1-7 returns any error this means you do not have enough permissions to create folders and need to contact your Database Administrator to elevate your user/login permissions to be able to execute the sys.sp_addextendedproperty procedure.*/--Batch 1 create a folder in the Databases objectBEGINDECLARE @extended_property_name varchar(512) = N'SQLTreeo|~'+@@servername+'|~Global|~Database|~SQLTreeo Test'EXEC sys.sp_addextendedproperty @name=@extended_property_name, @value=N'<?xml version="1.0" encoding="utf-16"?><ArrayOfFolderPropertiesRulesRule><FolderPropertiesRulesRule Type="SQL">%</FolderPropertiesRulesRule></ArrayOfFolderPropertiesRulesRule>'END--Batch 2 create a folder in the SQL Server agent jobs objectBEGINDECLARE @extended_property_name varchar(512) = N'SQLTreeo|~'+@@servername+'|~Global|~Job|~SQLTreeo Test'EXEC sys.sp_addextendedproperty @name=@extended_property_name, @value=N'<?xml version="1.0" encoding="utf-16"?><ArrayOfFolderPropertiesRulesRule><FolderPropertiesRulesRule Type="SQL">%</FolderPropertiesRulesRule></ArrayOfFolderPropertiesRulesRule>'END--Batch 3 check if the extended properties are present in the database.SELECT ep.name, ep.value FROM sys.extended_properties ep WHERE ep.name LIKE 'SQLTreeo|~'+@@servername+'|~Global|~Job|~%'SELECT ep.name, ep.value FROM sys.extended_properties ep WHERE ep.name LIKE 'SQLTreeo|~'+@@servername+'|~Global|~Database|~%'
In this article
Please choose a monitoring environment to connect to:
Enter your email address to recover your password.
Download link successfully sent. Check your email for details.
Reset password link successfully sent to . Check your email for details.
An email with a link for creating your password is sent to
Share link successfully sent to .
Your reply is send.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
Download link successfully sent to . Check your email for details.
We received your request. We will contact you shortly