Granting Access and Binding Defaults when recreating SQL Tables: a follow up

In his most recent article, Granting Access and Binding Defaults when recreating SQL Tables, my good friend, David Musgrave, points out how to drop and re-create a Microsoft Dynamics GP object with the SQL Server Management Studio (SSMS) database object scripting capabilities.

His article overall addresses issues with objects not being properly upgraded from previous releases of Microsoft Dynamics GP to the version you are trying to upgrade to, and how to deal with these issue, so please read the articles as they contain valuable upgrade tips.

However, in his article, David points out that SSMS does not generate the object permissions and bindings necessary, hence the reason for the script he created and attached to his article. The bottom line is, these are configurable settings under SSMS's SQL Server Object Explorer options. To activate scripting of permissions and bindings in SSMS, follow these steps:

1. Open SSMS and go to the Tools | Options menu. This will open the Options window. Locate the SQL Server Object Explorer options and expand, then highlight Scripting to activate all scripting options.

SQL Server Management Studio Options window

2. Under Object Scripting Options, change Script Permissions to True.

Script permissions option

3. Under Table and view options, change Script bound defaults and rules to True.

Script bound defaults and rules

4. Click Ok to acknowledge all changes and exit the Options window.

Now let's test this... if we choose to script the SOP10110 table referenced in the sample script provided by David in his article, the following are the results:

USE [TWO]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK__SOP10110__ENDDAT__41320FDC]') AND parent_object_id = OBJECT_ID(N'[dbo].[SOP10110]'))
ALTER TABLE [dbo].[SOP10110] DROP CONSTRAINT [CK__SOP10110__ENDDAT__41320FDC]
GO

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK__SOP10110__STRTDA__42263415]') AND parent_object_id = OBJECT_ID(N'[dbo].[SOP10110]'))
ALTER TABLE [dbo].[SOP10110] DROP CONSTRAINT [CK__SOP10110__STRTDA__42263415]
GO

USE [TWO]
GO

/****** Object:  Table [dbo].[SOP10110]    Script Date: 12/05/2011 08:49:26 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SOP10110]') AND type in (N'U'))
DROP TABLE [dbo].[SOP10110]
GO

USE [TWO]
GO

/****** Object:  Table [dbo].[SOP10110]    Script Date: 12/05/2011 08:49:26 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[SOP10110](
 [PRCSHID] [char](15) NOT NULL,
 [DESCEXPR] [char](51) NOT NULL,
 [NTPRONLY] [tinyint] NOT NULL,
 [ACTIVE] [tinyint] NOT NULL,
 [STRTDATE] [datetime] NOT NULL,
 [ENDDATE] [datetime] NOT NULL,
 [NOTEINDX] [numeric](19, 5) NOT NULL,
 [PROMO] [tinyint] NOT NULL,
 [CURNCYID] [char](15) NOT NULL,
 [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PKSOP10110] PRIMARY KEY NONCLUSTERED 
(
 [PRCSHID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[SOP10110].[PRCSHID]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[SOP10110].[DESCEXPR]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[SOP10110].[NTPRONLY]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[SOP10110].[ACTIVE]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[SOP10110].[STRTDATE]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_DATE]', @objname=N'[dbo].[SOP10110].[ENDDATE]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_MONEY]', @objname=N'[dbo].[SOP10110].[NOTEINDX]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_INT]', @objname=N'[dbo].[SOP10110].[PROMO]' , @futureonly='futureonly'
GO

EXEC sys.sp_bindefault @defname=N'[dbo].[GPS_CHAR]', @objname=N'[dbo].[SOP10110].[CURNCYID]' , @futureonly='futureonly'
GO

GRANT DELETE ON [dbo].[SOP10110] TO [DYNGRP] AS [dbo]
GO

GRANT INSERT ON [dbo].[SOP10110] TO [DYNGRP] AS [dbo]
GO

GRANT SELECT ON [dbo].[SOP10110] TO [DYNGRP] AS [dbo]
GO

GRANT UPDATE ON [dbo].[SOP10110] TO [DYNGRP] AS [dbo]
GO

ALTER TABLE [dbo].[SOP10110]  WITH CHECK ADD CHECK  ((datepart(hour,[ENDDATE])=(0) AND datepart(minute,[ENDDATE])=(0) AND datepart(second,[ENDDATE])=(0) AND datepart(millisecond,[ENDDATE])=(0)))
GO

ALTER TABLE [dbo].[SOP10110]  WITH CHECK ADD CHECK  ((datepart(hour,[STRTDATE])=(0) AND datepart(minute,[STRTDATE])=(0) AND datepart(second,[STRTDATE])=(0) AND datepart(millisecond,[STRTDATE])=(0)))
GO

Note that the name of your constraints may be different, however note that all permissions are granted to the DYNGRP role and that all defaults are properly binded for each currency, date, char, and integer data types.

As usual and if nothing else, you now have 2 methods of dropping and creating SQL Server objects, granting permissions and binding defaults.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/