Monthly Archives: May 2012

snapshoting large tables

When initialising a transactional replication subscription with a snapshot, and the source table is very large this can cause concurrency issues for the source database. This is because when performing a snapshot, an exclusive lock is taken on the table … Continue reading

Posted in Replication | Comments Off on snapshoting large tables

generate permissions for a role

This script will generate all the permissions that a role or all roles have… SELECT state_desc COLLATE Latin1_General_CI_AS + ‘ ‘ + permission_name COLLATE Latin1_General_CI_AS + ‘ ON ‘ + ‘[‘ + SCHEMA_NAME(schema_id) + ‘].’ + ‘[‘ + OBJECT_NAME(object_id) + … Continue reading

Posted in Security | Comments Off on generate permissions for a role

role based security

I’m a strong believer of role based security and today proved my point. In a server migration that we have underway, I migrated what I thought was all the permissions for this particular login. As it turned out, someone in … Continue reading

Posted in Security | Comments Off on role based security

Startup stored procedures

Stored procedures are enabled for execution with the proc sp_procoption. Create your stored procedure in the master database and then call sp_procoption like so… sp_procoption @ProcName = ‘my_stored_procedure’ , @OptionName = ‘startup’ , @OptionValue = ‘on’ By calling sp_procoption, you … Continue reading

Posted in Tips | Comments Off on Startup stored procedures