Scribblings of a Microsoft Dynamics GP MVP
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/
Read the original post:
Granting Access and Binding Defaults when recreating SQL Tables: a follow up