Freigeben über

Granting Access and Binding Defaults when recreating SQL Tables

  • Artikel

David Meego - Click for blog homepageAdded 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 */

declare @Table varchar(20)
set @Table = 'SOP10110'

declare @cStatement varchar(255)
declare G_cursor CURSOR for
select 'grant execute on ' + convert(varchar(64),name) + ' to DYNGRP'
from sysobjects where type = 'P' and name like 'zDP%' + @Table + '%'

set nocount on
OPEN G_cursor FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1) begin
print (@cStatement)
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor

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, https://opensource.org/licenses/ms-pl.html.)
*/

Hope you find this script useful. 

David

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 Defaults.zip

Comments