Check if a table name exists in stored procedures

 use [yorDB]
 GO


SELECT [Scehma]=schema_name(o.schema_id), o.Name, o.type

FROM sys.sql_modules m

INNER JOIN sys.objects o

ON o.object_id = m.object_id

WHERE m.definition like '%tablename%'









GO

Backup SSISDB

I came across a very good instructions on MSDN for this .

https://blogs.msdn.microsoft.com/mattm/2012/03/23/ssis-catalog-backup-and-restore/

SSIS Catalog – Backup and Restore

 

March 23, 2012By Kaarthik Sivashanmugam

 

 

 

 

Integration Services catalog (SSISDB) uses the encryption mechanism available in SQL Server to protect its sensitive data. So backup and restore of SSIS catalog across machines need some extra steps in addition to the usual backup and restore steps. This post describes all the steps involved in backup and restore of SSIS catalog.

Backup
1. Backup the master key used for encryption in SSISDB database and protect the backup file using a password. This is done using BACKUP MASTER KEY statement.

 USE SSISDB
 BACKUP MASTER KEY TO FILE = 'c:\DMK\SSISDB\key'
 ENCRYPTION BY PASSWORD = 'SS1SC@talogMKBKUP'

 This step is not necessary every time you do backup unless you have lost the file or the password or if you have changed the master key of the SSISDB database.
2. Backup SSISDB database using SQL Server Management Studio or BACKUP DATABASE statement in TSQL.
3. If SSISDB database will be restored in a SQL Server instance that never had SSIS catalog, follow steps 3.1. & 3.2. listed below in additional backup steps section.

Restore
4. If SSISDB database will be restored in a SQL Server instance that never had any SSIS catalog, follow preparatory steps 4.1. & 4.2. listed below in additional restore steps section.
5. Restore SSISDB database using SQL Server Management Studio or RESTORE DATABASE statement in TSQL.
6. If SSISDB is restored in a SQL Server instance that never had any SSIS catalog, follow steps 6.1. thru 6.3. listed below in additional restore steps section.
7. Restore backup of the master key from the source server using the backup file created in step 1 in Backup section above.

 USE SSISDB
 RESTORE MASTER KEY FROM FILE = 'D:\MasterKeyBackup\SSIS-Server1234\key'
 DECRYPTION BY PASSWORD = 'SS1SC@talogMKBKUP'
 ENCRYPTION BY PASSWORD = 'NewC@talogPassw0rd'
 FORCE
 

“SS1SC@talogMKBKUP” is the password used to protect the file containing backup of the master key and “NewC@talogPassw0rd” is the new password to encrypt database master key.
The warning reported when carrying out this step “The current master key cannot be decrypted. The error was ignored because the FORCE option was specified.” can be ignored.
Decrypting all keys using the currently active master key and re-encrypting using the restored master key is a resource-intensive operation should be scheduled when server usage is low.
SSIS catalog should be fully functional in the restored server now.

Additional Backup Steps
Following steps are necessary during backup if you will be restoring SSISDB database to a SQL Server instance where the catalog was never created.
3.1. Generate create script for sp_ssis_startup stored procedure which is created when SSIS catalog is created in a SQL Server. The script is generated using SQL Server Management Studio. Right-click on the stored procedure in master database in SSMS Object Explorer, select “Script Stored Procedure as” -> “CREATE TO” -> “File” options to save the script to a file (for example, sp_ssis_startup.sql).
3.2. Generate create script for Agent job [SSIS Server Maintenance Job] which is created in SQL Server Agent automatically when SSIS catalog is created in a SQL Server. The script is generated using SQL Server Management Studio. Right-click on the Agent job in SSMS Object Explorer, select “Script Job as” -> “CREATE TO” -> “File” options to save the job to a file (for example, ssis_server_maintenance_job.sql).

Additional Restore Steps
Following steps are necessary during restore if you are restoring SSISDB database to a SQL Server instance where the catalog was never created.
4.1. Enable CLR functionality in SQL Server that SSISDB database depends on.

 USE MASTER
 EXEC sp_configure 'clr enabled', 1
 RECONFIGURE

4.2. Create asymmetric key and unsafe assembly loading principal that SSISDB database depends on. The login is used only for granting permission and hence does not have to be mapped to a database user.

 USE MASTER
 CREATE ASYMMETRIC KEY MS_SQLEnableSystemAssemblyLoadingKey FROM EXECUTABLE FILE = 'C:\Program Files\Microsoft SQL Server\110\DTS\Binn\ISServerExec.exe'
 CREATE LOGIN MS_SQLEnableSystemAssemblyLoadingUser FROM ASYMMETRIC KEY MS_SQLEnableSystemAssemblyLoadingKey
 GRANT UNSAFE ASSEMBLY TO MS_SQLEnableSystemAssemblyLoadingUser

You may need to change the file system path to ISServerExec.exe depending on your install location.

6.1. Create login [##MS_SSISServerCleanupJobLogin##] using CREATE LOGIN TSQL statement. This login is for internal use in SSISDB maintenance.
6.2. Map SSISDB user ##MS_SSISServerCleanupJobUser## to server login ##MS_SSISServerCleanupJobLogin##

 USE SSISDB
 ALTER USER [##MS_SSISServerCleanupJobUser##] with LOGIN = [##MS_SSISServerCleanupJobLogin##]

6.3. Create startup stored procedure and agent job by executing scripts (sp_ssis_startup.sql  & ssis_server_maintenance_job.sql) created in steps 3.1 & 3.2 under additional backup steps section above in the master database in the SQL Server instance in which SSISDB database is restored. You need to update @servername parameter for sp_add_jobserver step in ssis_server_maintenance_job.sql with the name of the new server replacing old server name.

SSIS error: Drop faield for user

I was trying to drop a user from SSIDB database and I encountered the following error -

The database principal has granted or denied permission to catalog objects in the database and cannot be dropped. The transaction ended in the trigger. The batch has been aborted. (Microsoft SQL Server, Error: 27226)


I ran the following query to find the permission of the user I am trying to drop


use SSISDB
GO

SELECT TOP 1000 [object_type]

,[object_id]

,[principal_id]

,[permission_type]

,[is_deny]

,[grantor_id]

, 'EXEC catalog.revoke_permission @object_type=' + CAST([object_type] AS VARCHAR)

+ ', @object_id=' + CAST([object_id] AS VARCHAR)

+ ', @principal_id=' + CAST(principal_id AS VARCHAR)

+ ', @permission_type=' + CAST(permission_type AS VARCHAR) [execute this]

FROM [SSISDB].[catalog].[explicit_object_permissions]

WHERE principal_id = USER_ID('usertodrop')


Then copied the contents of the [execute this] column in a new query window and executed it against the SSISDB database which revokes the permission .
Then I was able to drop the user.

Error logging in SSIS

A very cool feature where the error from SSIS can be logged with few steps. Right click on the control flow canvas and select Logging.
In the Add a new log section you can select a Provider type. I have selected the SSIS log provider for SQL Server since I want to log the error to the database.  The ErrorLogConn  is the Environment variable that I have created in Project level. ( This points to the msdb database where I want to log the error)

In the following screen you get to select the events that you want to log for. I have selected only the following events keeping in mind that selecting all events might fill up the database soon.

OnError
OnTaskFailed
OnWarning

Once you hit OK an save the project, deploy it and that's all.

 

 

You can run the following to see the last error

 

 

SELECT TOP (100) PERCENT PKG.PackageName, PKG.starttime AS PackageStartTime, LG.source AS TaskName, LG.starttime AS StartTime, LG.endtime AS EndTime,

LG.message

FROM dbo.sysssislog AS LG INNER JOIN

(SELECT LG1.executionid, LG1.source AS PackageName, LG1.starttime

FROM dbo.sysssislog AS LG1 INNER JOIN

(SELECT source, MAX(starttime) AS starttime

FROM dbo.sysssislog

WHERE (event = 'PackageStart')

GROUP BY source

HAVING (MAX(starttime) > DATEADD(dd, -1, GETDATE()))) AS CUR ON CUR.source = LG1.source AND CUR.starttime = LG1.starttime

WHERE (LG1.event = 'PackageStart')) AS PKG ON LG.executionid = PKG.executionid

WHERE (LG.event IN ('OnError'))

ORDER BY PackageStartTime DESC, StartTime

 

(The above query was taken from the blog http://dataqueen.unlimitedviz.com/2012/08/logging-in-ssis/ - an awesome blog!)

List permission on a databse objects

use current_userdb
GO

select o.name , pm.permission_name,pr.name, user_name(pm.grantee_principal_id) from sys.database_permissions pm inner join

sys.database_principals pr

on pm.grantee_principal_id = pr.principal_id

inner join sys.objects o

on o.object_id = pm.major_id

where object_name(pm.major_id) = 'object1'