Granting Access and Binding Defaults when recreating SQL Tables

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

Added to the Useful SQL Scripts Series.

This is a follow on post from my previous article: Backing up and Restoring data when recreating SQL Tables which explains how to recreate tables (usually to fix table structure issues) while making sure that the data is preserved.

In the previous post; it has a step 3 to recreate the table and suggested that you can use the SQL Maintenance window from the application or T-SQL commands.

However, if you are in the middle of an upgrade with a failed table conversion, you will need to ensure that the table has the structure of the version you are upgrading from and not the version you are upgrading to. This will then allow the Microsoft Dynamics GP utilities to complete the table conversion for us (including any data manipulation required). So using SQL Maintenance while logged into an already upgraded company back to the failed company database is not the correct method.

Another method of re-creating the table is to generate the script from a correct version in a pre upgrade database.  You can use the Script Table as and Script Stored Procedure as options to generate the scripts to Drop and Create the Table and its associated zDP Stored Procedures.

Using the scripts generated by this method miss a couple of steps. They don’t:

  1. Grant Access to DYNGRP for the Table and Stored Procedures
  2. Bind Defaults for datetime, character, integer and currency datatypes

Granting access is required to allow users (other than ‘sa’) to be able to access the newly created SQL objects. Binding defaults sets up a default value for table columns to use when no data is provided on an insert statement. This will avoid “Cannot insert NULL” errors when inserting into a table without providing data for every column.

Note: Mariano has provided a method to get the Access and Defaults included in the scripts generated by SQL Server. Please see his post Granting Access and Binding Defaults when recreating SQL Tables: a follow up for details.

The following script (also attached at the bottom of this article) can be used to grant access to DYNGRP for the table and stored procedures and Bind Defaults for all tables in the database.

T-SQL Script Code

/* After creating a table and its associated stored procedures using  */
/* Script As >> Drop and Create To >> New Query Window, the following */
/* script will grant the access to DYNGRP and Bind the defaults       */
/* Written by David Musgrave, Last Modified: 02-Dec-2011              */


SET @Table = 'SOP10110'

DECLARE @cStatement VARCHAR(255)

SELECT 'grant execute on ' + convert(VARCHAR(64), NAME) + ' to DYNGRP'
FROM sysobjects
WHERE type = 'P'
	AND NAME LIKE 'zDP%' + @Table + '%'


OPEN G_cursor

FROM G_cursor
INTO @cStatement

	PRINT (@cStatement)

	EXEC (@cStatement)

	FROM G_cursor
	INTO @cStatement


SET @cStatement = 'grant select,update,insert,delete on ' + @Table + ' to DYNGRP'

PRINT (@cStatement)

EXEC (@cStatement)

PRINT 'Bind Defaults for Date, String, integer and currency datatypes'

EXEC smBindTableDefaults 0 -- 0 = All datatypes

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL,

Hope you find this script useful.


06-Dec-2011: Added a link to Mariano’s post which shows the steps to avoid the need for the additional script provided in this post.

SQL Creating Tables Grant Access and Bind

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on

5 thoughts on “Granting Access and Binding Defaults when recreating SQL Tables

Please post feedback or comments

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.