ERP Software Logo

Microsoft Dynamics vendors provide comparisons and opinions to professionals in the ERP/Accounting software selection process

 
 

The Resource Group

How to Create a More Efficient Grant.sql Script for Dynamics GP


Email | Print

Most people who work with Microsoft Dynamics GP have had to run the Grant.sql script before, but they probably couldn’t actually tell you what it’s used for or why they need to run it. The one thing that I noticed about the script is that if you run it on a very large database, it will take longer to run. I’ve even had to wait for hours at one client’s location because they had a few very large databases, and that made me wonder if there’s a better way to write the script so it runs faster.

 

The first thing I had to do is to analyze exactly what the script was doing. After looking at the script, I determined that the primary view that the information was pulled from was the sysobjects catalog view, which was looking for objects with a type of U, V, and P. Basically, it is searching for all user tables (U), views (V), and stored procedures (P) in the database. Once the objects are found, the script will grant the select, update, insert, and delete permissions to the DYNGRP role for the tables and views, and it will grant the execute permission for the stored procedures. Well, that seems simple enough, right? Then why does it take so long to run on large databases? If you think about it, you should only need to grant the necessary permissions to the user tables, views, and stored procedures if they don’t have them already. Why grant them if the permissions are already set, right? If you look at the Grant.sql script, it will grant the permissions to every object every time you run the script, so that could result in unnecessary granting that could be avoided. OK, now I figured out why it’s running so long, but how do we make it better?

 

There is a better way to do it, but there’s a catch, you need to have Microsoft SQL Server 2005 or higher. Starting with Microsoft SQL Server 2005, there is a new SQL catalog view called database_permissions. That catalog view will show you all of the permissions that an object already has, and if we compare that with the list of objects that we are looking for, we could single out just the objects that need to have the permissions granted and that way hopefully we’ll end up with a much shorter list. In practice, I’ve noticed that most of the objects already have the necessary permissions, so the list of objects that need the permissions is usually very short. I’m sure you’re interested to see the final result.

 

The script is available below.

 

declare @cStatement varchar(max)

select o.* into #temp

from (select s.name, s.id, t.* from sysobjects s

full join (select 'SL','select' union all

select 'UP','update' union all

select 'IN','insert' union all

select 'DL','delete') t(type,perm) on 1=1

where (s.type = 'U' or s.type = 'V') and s.uid = 1 union all

select s.name, s.id, t.* from sysobjects s

full join (select 'EX','execute') t(type,perm) on 1=1

where s.type = 'P') o

left join (select major_id, type from sys.database_permissions) p

on o.id = p.major_id and

o.type COLLATE DATABASE_DEFAULT = p.type COLLATE DATABASE_DEFAULT

where p.major_id is NULL

declare G_cursor CURSOR for

select 'grant'+STUFF((SELECT ', ' + t.perm FROM #temp t

Where t.id=o.id FOR XML PATH('')),1,1,'')+

' on ['+name+'] to DYNGRP' from #temp o group by name, id

set nocount on

OPEN G_cursor

FETCH NEXT FROM G_cursor INTO @cStatement

WHILE (@@FETCH_STATUS <> -1)

begin

exec (@cStatement)

FETCH NEXT FROM G_cursor INTO @cStatement

end

DEALLOCATE G_cursor

drop table #temp

 

Learn more by contacting a Dynamics GP partner like The Resource Group.

 

By Michael Krasivsky, The Resource Group

One Response to “How to Create a More Efficient Grant.sql Script for Dynamics GP”

  1. Jeff Nebeker says:

    Some caveats when granting insert, update and delete to views but overall a nice improvement! Now can you get GP to also include functions in the possible objects? Even though standard GP doesn’t leverage them, 3rd party vendors and others might (and do, now that they’ve been available for years). Kudos!