Snippet Name: envusersssisdb
Tags: Login And User Scripting
Created Date: Mar 20 2022 6:09AM
Last Modified Date: Apr 1 2022 11:23AM
no description at this time.
Dependencies: none-standalone code
This snippet scripts out all the users in the SSISDB database but specifically ignores some of the custom rolls users that are built into the SSISDB database so you get just what would supposedly be user permissions that were added by the DBAs .
Sample Output:
--Script out linked server in a compact format
WITH Serverz
AS
(
SELECT
'EXEC master.dbo.sp_addlinkedserver @server = N''' + [srvz].[name]
+ ''',@srvproduct = N''' + [srvz].[product]
+ ''', @datasrc = N''' + ISNULL([srvz].[data_source],'')
+ CASE WHEN [srvz].[catalog] IS NOT NULL THEN ', @catalog=N''' + [srvz].[catalog] + '''' ELSE '' END
+ ''', @provider = N''' + [srvz].[provider] + ''';
EXEC master.dbo.sp_serveroption @server=N''' + [srvz].[name] + ''', @optname=N''collation compatible'', @optvalue=N'''
+ CASE WHEN [srvz].[is_collation_compatible] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + [srvz].[name] + ''', @optname=N''data access'', @optvalue=N'''
+ CASE WHEN [srvz].[is_data_access_enabled] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + [srvz].[name] + ''', @optname=N''dist'', @optvalue=N'''
+ CASE WHEN [srvz].[is_distributor] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + [srvz].[name] + ''', @optname=N''pub'', @optvalue=N'''
+ CASE WHEN [srvz].[is_publisher] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + [srvz].[name] + ''', @optname=N''rpc'', @optvalue=N'''
+ CASE WHEN [srvz].[is_remote_login_enabled] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + [srvz].[name] + ''', @optname=N''rpc out'', @optvalue=N'''
+ CASE WHEN [srvz].[is_rpc_out_enabled] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + [srvz].[name] + ''', @optname=N''sub'', @optvalue=N'''
+ CASE WHEN [srvz].[is_subscriber] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + [srvz].[name] + ''', @optname=N''connect timeout'', @optvalue=N'''
+ CONVERT(VARCHAR(30),[srvz].[connect_timeout]) + '''
EXEC master.dbo.sp_serveroption @server=N''' + [srvz].[name] + ''', @optname=N''collation name'', @optvalue='
+ CASE WHEN [srvz].[collation_name] IS NULL THEN 'NULL ' ELSE '''' + [srvz].[collation_name] + '''' END + '
EXEC master.dbo.sp_serveroption @server=N''' + [srvz].[name] + ''', @optname=N''lazy schema validation'', @optvalue=N'''
+ CASE WHEN [srvz].[lazy_schema_validation] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + [srvz].[name] + ''', @optname=N''query timeout'', @optvalue=N'''
+ CONVERT(VARCHAR,[srvz].[query_timeout]) + '''
EXEC master.dbo.sp_serveroption @server=N''' + [srvz].[name] + ''', @optname=N''use remote collation'', @optvalue=N'''
+ CASE WHEN [srvz].[uses_remote_collation] = 0 THEN 'false' ELSE 'true' END + '''
EXEC master.dbo.sp_serveroption @server=N''' + [srvz].[name] + ''', @optname=N''remote proc transaction promotion'', @optvalue=N'''
+ CASE WHEN [srvz].[is_remote_proc_transaction_promotion_enabled] = 0 THEN 'false' ELSE 'true' END + '''
' AS [Linky],
[srvz].*
FROM [sys].[servers] [srvz] WHERE [srvz].[server_id] > 0
)
,Loginz
AS
(
--logins
SELECT [srvz].name,
'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N''' + [srvz].[name] + ''',
@useself = N''' + CASE WHEN [logz].[uses_self_credential] = 0 THEN 'False' ELSE 'True' END + ''',
@locallogin = ' + CASE WHEN [logz].[local_principal_id] = 0 THEN 'NULL' ELSE '''' + SUSER_NAME([logz].[local_principal_id])+ '''' END + ',
@rmtuser = ' + CASE
WHEN [logz].[remote_name] IS NULL THEN 'NULL, @rmtpassword = NULL; '
ELSE 'N''' + [logz].[remote_name] + ''',@rmtpassword = N''########'';'
END AS [cmd],
[logz].* FROM [sys].[linked_logins] [logz]
INNER JOIN [sys].[servers] [srvz] ON [srvz].[server_id] = [logz].[server_id]
WHERE [srvz].[server_id] > 0
)
SELECT [server_id],1 AS Sortorder,[name],Linky FROM [Serverz] T1
UNION ALL
SELECT [server_id],2 AS Sortorder,[name],cmd FROM [Loginz] T2
ORDER BY server_id,Sortorder