I am faced with the task of copying changes to program modules (stored procedures, functions and views) from one server to others, along with access rights. I decided that the easiest way to do this is to collect information in sys.objects
about what was changed and then use PowerShell scripts to get the text of programs and execute them on another server. Please help me figure out how to get scripts of functions, views and access rights. For procedures it was possible to sort it out, but for functions, views and access rights it does not work out in any way.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$srv = new-object Microsoft.SqlServer.Management.Smo.Server("srvMDM")
$db = $srv.Databases.Item("MDM")
$proc = $db.StoredProcedures | ?{ $_.Name -eq "parsing_remains_v1"}
$retval = $proc.ScriptHeader($true) + $proc.TextBody
I managed to solve the problem, but it's far from perfect. The following problems I could not solve:
1 - since I didn't understand how I can create ALTER scripts and not CREATE - I abandoned Smo.Scripter (ScriptForAlter = $true gave an error)
2 - since I did not understand how it is possible to run a PowerShell command on more than one line (without creating a separate .ps1 file), then my entire PowerShell script is transferred to one line Below is a PowerShell script and then a procedure in SQL Server that runs the script - my final solution
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$srv = new-object Microsoft.SqlServer.Management.Smo.Server("srvMDM")
$db = $srv.Databases.Item("MDM")
$proc = $db.UserDefinedFunctions | where { $_.Schema -eq "marat" -and $_.Name -eq "testfunction"}
$retval = $proc.ScriptHeader($true) + $proc.TextBody
ECHO $retval
ECHO GO
$objPermission=$proc.EnumObjectPermissions() | Select-Object objectschema, objectname, permissiontype, PermissionState, Grantee
if ($objPermission)
{
foreach ($rp in $objPermission)
{
$spcontent = $rp.PermissionState.tostring() + " " + $rp.permissiontype.tostring() + " ON [" + $rp.objectschema + "].[" + $rp.objectname + "] TO [" + $rp.grantee + "]"
ECHO $spcontent
}
}
SP:
alter procedure get_PogramStript_by_PowerShell
@ServerName nvarchar(255)
,@dbName nvarchar(255)
,@SchemaName nvarchar(255)
,@ObjectName nvarchar(255)
,@ObjectType nvarchar(10)
,@isCreate bit = 1
,@IncludePermission bit = 0
,@txt NVARCHAR(MAX) OUTPUT
as
BEGIN
SET NOCOUNT ON;
--@ObjectType:
--P = SQL Stored Procedure
--PC = Assembly (CLR) stored-procedure
--RF = Replication-filter-procedure
--V = View
--AF = Aggregate function (CLR)
--FN = SQL scalar function
--FS = Assembly (CLR) scalar-function
--FT = Assembly (CLR) table-valued function
--IF = SQL inline table-valued function
--TF = SQL table-valued-function
--F - function, added it myself, so as not to take a steam bath if necessary and just pass that the function is needed
DECLARE @cmd VARCHAR(8000) --here we collect the powershell script
,@ObjectTypePS varchar(100) --we explain PS what kind of program object is needed
,@HeaderOperation varchar(10) --need ALTER or CREATE script
SELECT @ObjectTypePS = CASE WHEN @ObjectType in ('P','PC','RF') THEN 'StoredProcedures'
WHEN @ObjectType in ('F','AF','FN','FS','FT','IF','TF') THEN 'UserDefinedFunctions'
WHEN @ObjectType = 'V' THEN 'Views'
END
,@HeaderOperation = CASE WHEN @isCreate = 1 THEN '$false'
ELSE '$true'
END
--unfortunately I could not figure out how to transfer the script not in one line and not in a separate ps1 file
SET @cmd = 'powershell.exe -c " try {[System.Reflection.Assembly]::LoadWithPartialName(''Microsoft.SqlServer.SMO'') | out-null; '
SET @cmd += '$srv = new-object Microsoft.SqlServer.Management.Smo.Server('''+@ServerName+'''); '
SET @cmd += '$db = $srv.Databases.Item('''+@dbName+'''); '
SET @cmd += '$obj = $db.'+@ObjectTypePS+' | where { $_.Schema -eq '''+@SchemaName+''' -and $_.Name -eq '''+@ObjectName+'''}; '
SET @cmd += '$retval = $obj.ScriptHeader('+@HeaderOperation+') + $obj.TextBody; '
SET @cmd += 'ECHO $retval.ToString(); '
IF @IncludePermission = 1
BEGIN
SET @cmd += 'ECHO ''GO''; '
SET @cmd += '$objPermission=$obj.EnumObjectPermissions() | Select-Object objectschema, objectname, permissiontype, PermissionState, Grantee; '
SET @cmd += 'if ($objPermission)'
SET @cmd += '{'
SET @cmd += ' foreach ($rp in $objPermission)'
SET @cmd += ' {'
SET @cmd += ' $spcontent = $rp.PermissionState.tostring() + '' '' + $rp.permissiontype.tostring() + '' ON ['' + $rp.objectschema + ''].['' + $rp.objectname + ''] TO ['' + $rp.grantee + ''];'';'
SET @cmd += ' ECHO $spcontent;'
SET @cmd += ' }'
SET @cmd += '}'
END
SET @cmd += '} catch {echo ''error''} "'
declare @Tab table (line NVARCHAR(MAX))
insert into @tab
EXEC master.dbo.xp_cmdshell @cmd
select @txt = N'USE ['+@dbName+']
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
'+(Select STRING_AGG(line,'
') from @tab)
return
END