Skip to content

Instantly share code, notes, and snippets.

Avatar
💭
Still figuring out all of this!

Marcos Freccia marcosfreccia

💭
Still figuring out all of this!
View GitHub Profile
View Remove-SqlBackups.ps1
function Remove-SQLBackups {
<#
.SYNOPSIS
Removes old backups stored
.DESCRIPTION
The function removes old backups taken using Ola Hallengren maintenance solution. It
also takes care of properly removing the backups based on Production, Development
and Test retention policies.
.PARAMETER RootBackupShare
File share where the SQL Server backups are setup. This should be the same as
@marcosfreccia
marcosfreccia / linking-data-sources-ssrs-reports.ps1
Last active Oct 13, 2018
This script helps you to link Data Sources to SSRS after a migration to a different report Report Server.
View linking-data-sources-ssrs-reports.ps1
$TargetReportServerURL = "http://MySSRSServer/ReportServer"
$ReportServer = "MySqlServerInstance"
$ReportServerDB = "ReportServer"
$RootFolder = "/MyReports"
$DataSourceUrlPath = "/MyReports/Data Sources/SalesDataSource"
<#
This query assumes that in the $RootFolder you have reports that links to only a single Data Source.
If you have in the same $RootFolder reports that have different data sources, you would a new parameter
and also change your WHERE clause to add this possibility.
@marcosfreccia
marcosfreccia / list-database-users-roles.sql
Last active Sep 13, 2018
List Database Users and Roles
View list-database-users-roles.sql
SELECT DISTINCT
dbp.name AS DBUser,
dbp.type_desc AS UserType,
dbp.create_date AS CreateDate,
SUBSTRING(
(
SELECT DISTINCT ';' + roles2.name AS [text()]
FROM sys.database_principals AS dbp2
LEFT JOIN sys.database_role_members AS dbrm2
LEFT JOIN sys.database_principals AS roles2
View create-ssrs-exec-role.sql
USE master
GO
DECLARE @RoleName VARCHAR(50) = 'RSExecRole';
DECLARE @Script VARCHAR(MAX) = 'USE master;';
SELECT @Script = @Script + 'CREATE ROLE ' + @RoleName + CHAR(13);
SELECT @Script
= @Script + 'GRANT ' + prm.permission_name + ' ON ' + +QUOTENAME(OBJECT_SCHEMA_NAME(major_id)) + '.'
+ QUOTENAME(OBJECT_NAME(major_id)) + ' TO ' + rol.name + CHAR(13) COLLATE Latin1_General_CI_AS
FROM sys.database_permissions prm
JOIN sys.database_principals rol