SQL Server Most Utilized Tables

Ever wondered which tables in your databases are being used most heavily?  Below is a script for SQL Server 2005/2008 that will tell you exactly that - which tables are utilized most and what percentage of that use is reads and what percentage is writes.  Heavily utilized tables are prime candidates for SQL Server's Table Partitioning or moving to another filegroup (not necessarily recommended with EnterpriseOne) and besides, it's just gosh-darn fun knowing what's going on inside your databases.

Big Caveat - This script makes use of the sys.dm_db_index_usage_stats SQL Dynamic Management View.  The values returned by this DMV do not persist beyond server restarts.  This means the information it gathers is only valid since the last SQL Server restart or (less likely) database attach.  So, if you just re-started your database server you are not going to get good numbers. Also, this information is kept in cache and is subject to memory pressure flushing, not a likely scenario but possible.

The script will gather index information from which one can infer table access information.  It uses the sp_MSForEachDB stored procedure to run through all databases on the instance, places the information in a temporary table, sums the values for index reads and writes, does a bunch of fancy math, rolls it up to the table level and returns the results to show reads, writes, percent of each and the type of index read.


--SQL Script begin
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp
GO

CREATE TABLE #Temp
(TableName NVARCHAR(255), UserSeeks DEC, UserScans DEC, UserUpdates DEC)
INSERT INTO #Temp
EXEC sp_MSForEachDB 'USE [?]; IF DB_ID(''?'') > 4
BEGIN
SELECT DB_NAME() + ''.'' + object_name(b.object_id), a.user_seeks, a.user_scans, a.user_updates 
FROM sys.dm_db_index_usage_stats a
RIGHT OUTER JOIN [?].sys.indexes b on a.object_id = b.object_id and a.database_id = DB_ID()
WHERE b.object_id > 100 AND a.user_seeks + a.user_scans + a.user_updates > 0
END'

SELECT TableName as 'Table Name', sum(UserSeeks + UserScans + UserUpdates) as 'Total Accesses',
sum(UserUpdates) as 'Total Writes',
CONVERT(DEC(25,2),(sum(UserUpdates)/sum(UserSeeks + UserScans + UserUpdates)*100)) as '% Accesses are Writes',
sum(UserSeeks + UserScans) as 'Total Reads', 
CONVERT(DEC(25,2),(sum(UserSeeks + UserScans)/sum(UserSeeks + UserScans + UserUpdates)*100)) as '% Accesses are Reads',
SUM(UserSeeks) as 'Read Seeks', 
CONVERT(DEC(25,2),(SUM(UserSeeks)/nullif(sum(UserSeeks + UserScans),0)*100)) as '% Reads are Index Seeks',
SUM(UserScans) as 'Read Scans',
CONVERT(DEC(25,2),(SUM(UserScans)/nullif(sum(UserSeeks + UserScans),0)*100)) as '% Reads are Index Scans'
FROM #Temp
GROUP by TableName
ORDER BY 'Total Accesses' DESC
DROP table #Temp
--SQL Script end


The returned results will look like this:

Table Name	Total Accesses	Total Writes	% Accesses are Writes	Total Reads	% Accesses are Reads	Read Seeks	% Reads are Index Seeks	Read Scans	% Reads are Index Scans
JDE_PRODUCTION.F06116	1196887461	15886068	1.33	1181001393	98.67	1181001096	100.00	297	0.00
JDE_PRODUCTION.F0618	934633544	995748	0.11	933637796	99.89	933637770	100.00	26	0.00
JDE_PRODUCTION.F4801	902692704	465333312	51.55	437359392	48.45	418433760	95.67	18925632	4.33
JDE_PRODUCTION.F0911	739173150	49489125	6.70	689684025	93.30	689283925	99.94	400100	0.06

From this we can determine what tables are being utilized heavily and in what manner.   The last four columns deal with methods of index read access and are a bit beyond the scope of this article but if you really want to discuss that just ask in the comments and I'll be glad to expand.

EnterpriseOne 9.0 SQL Server Collation Issues

Starting in EnterpriseOne 9.0 the platform pack delivers pre-built databases that are simply attached and are set to a collation of Latin1_General_CI_AS_WS when created at Oracle.  This collation is different than the default SQL Server collation and different than the old (pre-9.0) collations.  Because of this, you will usually find a mix of collations on an E1 install, particularly upgrades.  The collation for E1 databases will likely be different than the collation for tempdb and master.  This can cause problems in an EnterpriseOne system and could result in "Cannot resolve collation conflict" or "Cannot resolve the collation conflict" errors appearing in logs during upgrades or JOIN operations.

You can determine the collation of your system's databases with this code:

--SQL Script begin
SELECT name, collation_name FROM sys.databases
--SQL Script end

If you are on version 9.0 or above you will likely have different collations for your E1 databases and the SQL system databases.  It is possible that you may see different collations between E1 databases if they are upgraded databases.


Background

The reason for E1 databases being delivered with odd collations was a width sensitivity issue for Double-Byte languages.  The solution was to create the EnterpriseOne databases with a width-sensitive collation - Latin1_General_CI_AS_WS.  This solved the width sensitivity problem but  has caused a large amount of grief for customers since it does not match SQL Server's default collation of SQL_Latin1_General_CP1_CI_AS on US English Windows servers.  (Heck, Latin1_General_CI_AS_WS doesn't match any language's default collation, guaranteeing collation conflicts.)  Oracle's recommendation is to install SQL Server with a non-default collation that will match the incoming E1 databases which keep their collation when attached.  Installing SQL Server with a collation that matches the incoming E1 databases will cause databases created on that SQL Server (master, tempdb, etc.) to take the SQL Server collation that will then match the E1-delivered collation and eliminate collation conflicts with JOINS.

To determine your SQL Server collation:

--SQL Script begin
SELECT SERVERPROPERTY('Collation') AS 'Collation'
--SQL Script end


Possible Solutions

If one fails to install SQL Server with the non-default collation (which is highly likely) Oracle recommends changing the collation of the existing databases.Changing a database's collation is no simple matter however and should be well thought out and planned.  Oracle recommends using R98403E but there are several methods and scripts online that can help.

Here is a very informative page that includes links to scripts that can help you change database collations if you choose to do so: http://www.sqlserverclub.com/articles/understanding-sql-server-collation-sequences.aspx

Here is an MSDN blog telling you not to: http://blogs.msdn.com/b/qingsongyao/archive/2011/04/04/do-not-alter-database-collation-in-your-server.aspx


Workarounds

If you are seeing errors in jde logs indicating a collation conflict you may well have to change collation.  If you are having problems with your own query containing a JOIN statement that joins databases with a different collations, you may wish to write the query and specify the collation. Ex: JOIN JDE900.OL900.F9860 on (sys.objects.name = JDE900.OL900.F9860.SIOBNM COLLATE SQL_Latin1_General_CP1_CI_AS).

Here's an example where I utilize this method: http://jeffstevenson.karamazovgroup.com/2010/07/how-large-are-my-tables.html.


I find that the collation issue in E1 to be a general pain but apparently it is necessary to deliver databases that handle the width sensitivity issue.  I personally think the platform pack code could be modified to determine if the double-byte problem is present before splatting databases onto a server that create collation conflicts.  I welcome any feedback on this issue.

More Information

Here's Oracle's explanation why:  https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=1267442.1

Another informative Oracle article that also discusses what happens to upgrade databases' collation:
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=1271189.1

To get a list of all SQL Server collations (very useful):

--SQL Script begin
SELECT * FROM fn_helpcollations()
--SQL Script end

LiteSpeed Object Level Recovery Performance Enhancement

I recently had the opportunity (if you'd call it that) to utilize LiteSpeed's Object Level Recovery functionality on a large (~500GB) database and was somewhat surprised at the poor performance.

An end user had run a report in the JD Edwards EnterpriseOne application with incorrect settings, corrupting a table to the point where we needed the data from prior to the report run. Object Level Recovery (OLR) seemed like just the thing.

After determining the date desired for the restore I opened the OLR wizard in the LiteSpeed console, selected the appropriate full and differential backups and clicked through to view the backup contents to select the desired table. Then I waited...and waited, for hours and hours. It took so long that I stopped the process after five hours, thinking it must be hung. I re-started the process and let it run overnight, eventually the task completed and I was able to restore the table but I was convinced that this was not a workable solution for the future.


Background

LiteSpeed does Object Level Recovery by building an OLR 'map' over the backup contents and allows one to display a list of objects and then select individual tables for recovery. The building of this map is what was taking so long as we are basically building an index over the backup files. Consultations with the vendor, Quest Software, indicated that they are aware of the performance issues with OLR and they suggested either applying a OLR-related HotFix or upgrading to version 6.5, which includes the fix. Another suggestion was to perform the OLR map build during the backup, although support indicated that there might be a performance hit during backups.


Setup

I upgraded LiteSpeed to version 6.5 and performed some rudimentary tests which showed that improvements had been made but nothing significant. OLR map building/contents view was still taking quite a long time, especially when combining a full and a differential backup. Following support's suggestion, I enabled the map building during the backup by going to the maintenance plan properties for both the full and differential backup plans and setting "Optimize the Object Level Recovery speed of the backup" under the "LiteSpeed" section.

Figure 1

Testing

I saved the maintenance plan, tested backup times and was pleasantly surprised that there was no noticeable increase in time required to perform the backup. Now to test the OLR restore time.

I had expected an improvement but was stunned to find that LiteSpeed returned the object list in about 30 seconds! That is truly some kind of performance change, now Object Level Recovery is actually usable.


Summary

I would highly recommend that anyone using LiteSpeed for backups upgrade to version 6.5 and change their maintenance plans to optimize OLR. It doesn't seem to add any appreciable time during the backup and the gains during recovery are simply stunning.

Quest Software should make this the default setting.

SQL Server - Members of db_datareader not in db_denydatawriter

In an earlier series of articles on EnterpriseOne SQL Server default security we discussed the issue of Oracle granting permissions to PUBLIC that created a security exposure when placing users in the SQL Server db_datareader database role.  I advised you to place any user of an E1 database that is in the db_datareader role in the db_denydatawriter role also.  However, I didn't leave you with any quick way to determine what users are in the db_datareader role.  The script below quickly identifies users that are members of db_datareader but not also members of db_denydatawriter.  The script will only return results from databases where this situation actually occurs.

--SQL Script begin
EXEC sp_MSforeachdb 'USE [?]; IF
(SELECT COUNT (*) from (SELECT sys.database_principals.name, sys.database_role_members.member_principal_id
FROM sys.database_role_members
JOIN sys.database_principals
ON sys.database_role_members.member_principal_id=sys.database_principals.principal_id
where role_principal_id = 16390
EXCEPT
SELECT sys.database_principals.name, sys.database_role_members.member_principal_id
FROM sys.database_role_members
JOIN sys.database_principals
ON sys.database_role_members.member_principal_id=sys.database_principals.principal_id
where role_principal_id = 16393) as a) > 0
SELECT DB_NAME() as ''Database'', sys.database_principals.name, sys.database_role_members.member_principal_id
FROM sys.database_role_members
JOIN sys.database_principals
ON sys.database_role_members.member_principal_id=sys.database_principals.principal_id
where role_principal_id = 16390
EXCEPT
SELECT DB_NAME() as ''Database'', sys.database_principals.name, sys.database_role_members.member_principal_id
FROM sys.database_role_members
JOIN sys.database_principals
ON sys.database_role_members.member_principal_id=sys.database_principals.principal_id
where role_principal_id = 16393'
--SQL Script end

Place the users identified by the script in the db_denydatawriter role to address the security exposure.

Default SQL Permissions in EnterpriseOne Part 3

As mentioned in parts 1 and 2 of Default SQL Permissions in EnterpriseOne the permissions granted in EnterpriseOne databases during the install leave quite a security exposure.  Those articles covered in depth the reasons for this and ways to address the problem.  This article will detail two more items of concern: database level permissions set by the install and Guest user access from a dangerous set of permissions associated with the Public role.


Create Table

During the install the file ce_InstallSQLDatabase.BAT is executed and grants CREATE TABLE to PUBLIC:

osql -U%SYSADMIN_USER% -P%SYSADMIN_PSSWD% -S %JDE_SRV% -w 500 -n -Q "Grant CREATE TABLE to public" -d PS_%UENV% -b

The EnterpriseOne install does this to enable the generation of tables through OMW.

While I don't think this is as bad as users having write access to your data there are still ramifications.  Any user who has been granted access to the EnterpriseOne database can create a table and possibly completely fill the filesystem, effectively performing a denial-of-service attack by consuming all disk space on the volume.

To address this, one should REVOKE the CREATE TABLE permission at the database level from the PUBLIC role.  To enable table generation from OMW GRANT the CREATE TABLE permission to the appropriate users for the schema name (TESTDTA and TESTCTL in JDE_DEVELOPMENT, PRODDTA and PRODCTL in JDE_PRODUCTION, etc.).  Alternately, you could create a database role in each database, place the appropriate users (CRPDTA, CRPCTL, TESTDTA, etc.) in the role and grant CREATE TABLE to the role.  CREATE TABLE permissions already exist for the JDE database user in each E1 database so installs and upgrades should not be an issue.


Guest User

The Guest user in EnterpriseOne databases is more insidious, especially when combined with the fact that Oracle grants permissions to the PUBLIC role in all EnterpriseOne databases. 

The guest user account allows a login without a user account to access a database.  It is essentially a user account within the database that logins can use when they are not granted explicit access to a database.  The Guest account is dangerous for that ability and most dBA's remove the account even without the additional permissions it gains when present in an EnterpriseOne database.

If you recall, our earlier articles on E1 SQL Permissions documented how every user in a database inherits full permissions to all tables by virtue of being a member of the PUBLIC role that the install grants those rights to.  Well guess what role the Guest user is a member of?  Yep, PUBLIC.  Therefore any login on a SQL Server that is not granted access to an EnterpriseOne database (and thus uses Guest to connect to the database) still has full, complete rights to every table in that database.  Here's how (test this if you like):

A login is created on a SQL Server and is not granted access to the EnterpriseOne databases.  Since, by definition a login without a user account in a database will use the Guest account, when our login accesses an E1 database they will be doing so using the Guest account.  Since full permissions to all tables is granted to PUBLIC, and since Guest is a member of PUBLIC our login will have full and complete permissions on every single table.  Try it out - create a login on your SQL Server and do not grant it access to any databases.  Then login as that user and run a SELECT statement against one of your EnterpriseOne tables.....or an UPDATE, INSERT or DELETE statement if you like.

It is highly recommended that you take the following steps even if you followed the earlier suggestion to remove permissions from PUBLIC:  If you are on SQL 2000 remove the Guest user from each database.  If you are using SQL2005 or later REVOKE the Guest user's CONNECT permission.  Starting in SQL 2005 the guest user cannot be dropped, but guest user can be disabled by revoking its CONNECT permission.

You can check Guest's CONNECT permission in each database with this code.

--SQL Script begin
declare @cmd1 varchar(500)
set @cmd1 = 'PRINT ''?''; USE [?];
IF (SELECT state_desc FROM sys.database_permissions where type = ''CO'' and grantee_principal_id = user_id( ''guest'' ))
= ''GRANT'' SELECT DB_NAME() as ''Database'', permission_name, state_desc FROM sys.database_permissions
where grantee_principal_id = user_id( ''guest'' )'
exec sp_MSforeachdb @command1=@cmd1
--SQL Script end

If you see CONNECT and GRANT in the results, you are exposed and should take the steps detailed above to address the issue.


Conclusions

The default EnterpriseOne install creates some pretty dangerous security exposures: 

• Well known default login passwords
• Complete permissions granted on tables by default to all users in an E1 database
• CREATE TABLE permissions granted at the database level
• Guest user with complete permissions on E1 databases

Oracle (and JD Edwards before them) attempt to evade responsibility by claiming, somewhat disingenuously, that the database configuration is a customer responsibility and not theirs.  However, given the complexity of the product and the fact that the sale force pushes the whole "Running E1 on SQL Server is a no maintenance, no DBA needed affair", Oracle is duty-bound to ensure that their customers are at least aware of the dangers.  I'd prefer they fix the problem but absent that, they should highlight in the documentation the exact steps recommended to address the issues.

Thanks for sticking with me though this lengthy series.

Default SQL Permissions in EnterpriseOne Part 2

In Default SQL Permissions in EnterpriseOne Part 1 we discussed the problem with granting ad-hoc SQL logins access to EnterpriseOne databases and how doing so gave them rights to modify data. In part 2 we are going to demonstrate this effect in hopes of creating a better understanding of exactly what is going on. Afterward I will offer some suggestions to work around and/or remedy the situation.


Demonstration

First, create a SQL Server login named Darryl, grant it access to a database (I will use SpotlightPlaybackDatabase but feel free to use one of your own) but do not place it in any server or database roles. Once this is done we will query that login's permissions.































The SQL Server system function sys.fn_my_permissions "Returns a list of the permissions effectively granted to the principal on a securable." Since simply calling sys.fn_my_permissions returns the calling user's (the one executing the command) permissions we will combine the function with the EXECUTE AS clause to determine permissions for our newly created user.

The script will look something like this:

--SQL Script begin
use databasename
EXECUTE AS USER = 'Darryl';
SELECT entity_name, permission_name FROM fn_my_permissions ('schemaname.tablename', 'OBJECT')
WHERE subentity_name = '';
REVERT
--SQL Script end

Where databasename is the database to be tested and schemaname.tablename is a random table object you choose in the database.

Let's see what our new user's permissions are at the object level in the non-E1 database with our user as a member of only PUBLIC.

--SQL Script begin
use SpotlightPlaybackDatabase
EXECUTE AS USER = 'Darryl';
SELECT entity_name, permission_name FROM fn_my_permissions ('dbo.spotlight_playback_alarms', 'OBJECT')
WHERE subentity_name = '';
REVERT
--SQL Script end

We get the following:

entity_name	permission_name

At the object level, our user has no permissions. None have been granted to the user and none have been granted via the only role that they are a member of - PUBLIC.

Now let's add the db_datareader role to our user in the SpotlightPlaybackDatabase.

And run our script to determine permissions.

--SQL Script begin
use SpotlightPlaybackDatabase
EXECUTE AS USER = 'Darryl';
SELECT entity_name, permission_name FROM fn_my_permissions ('dbo.spotlight_playback_alarms', 'OBJECT')
WHERE subentity_name = '';
REVERT
--SQL Script end

This returns the following:

entity_name	permission_name
dbo.spotlight_playback_alarms	SELECT

You can see that the user has SELECT rights, and only SELECT rights, to the table, and in fact all other tables in the database. This is exactly what we want to see for a read-only user in a database. Let's see what it looks like for an EnterpriseOne database.

First we add the user to the JDE_CRP database and assign it no roles. It will be a member of the database's PUBLIC role by default.

Now run our script to determine permissions.

--SQL Script begin
use JDE_CRP
EXECUTE AS USER = 'Darryl';
SELECT entity_name, permission_name FROM fn_my_permissions ('CRPDTA.F0101', 'OBJECT')
WHERE subentity_name = '';
REVERT
--SQL Script end

Here's the permissions for our user who has simply been given access to the database:

entity_name	permission_name
CRPDTA.F0101	SELECT
CRPDTA.F0101	UPDATE
CRPDTA.F0101	REFERENCES
CRPDTA.F0101	INSERT
CRPDTA.F0101	DELETE

The user, who has not been added to any roles (except PUBLIC by default), has SELECT, UPDATE, INSERT.....wait, what? UPDATE, INSERT, DELETE? Yep, a user added to an EnterpriseOne database has those permissions without them being explicitly given to the user. This happens because, as mentioned in part 1, the install script DB_SQLSRVR_INSTALL.sql grants those permissions to the PUBLIC role in EnterpriseOne databases and all users in a database inherit permissions of any and all roles they are members of.

Placing our user in the db_datareader gives no additional permissions since they already have the permissions granted by that role. It is only when we start denying rights that we address the security hole.

Let's place our user in the db_denydatawriter role. Microsoft says "Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database." This sounds exactly like what we want for our ad-hoc SQL users.

We'll run our script again:

--SQL Script begin
use JDE_CRP
EXECUTE AS USER = 'Darryl';
SELECT entity_name, permission_name FROM fn_my_permissions ('CRPDTA.F0101', 'OBJECT')
WHERE subentity_name = '';
REVERT
--SQL Script end

The results look more like what we want for our read-only users:

entity_name	permission_name
CRPDTA.F0101	SELECT
CRPDTA.F0101	REFERENCES

It took explicitly denying 'write' rights to our user in the EnterpriseOne database to override the permissions granted to the PUBLIC database role and inherited by our user. Having to do so is a bit out of the norm but knowing this fact will allow you to deal with the problem.

Update (11/1/2010): I have created a script to help identify users that are members of db_datareader but not db_denydatawriter. You can find it here: http://jeffstevenson.karamazovgroup.com/2010/11/sql-server-members-of-dbdatareader-not.html.


Workarounds and Suggestions

We can clearly see that following standard SQL user provisioning procedures for granting read-only access leaves us with a security exposure in EnterpriseOne databases due to a non-standard method of granting permissions in the database by the E1 install scripts. Now that we are aware of this we can address it is several ways.

The methods to deal with the problem fall into two categories: Continuing with PUBLIC permissions as the install configures and overriding them or removing the PUBLIC permissions and granting them only to the JD Edwards system/proxy user(s).

If you choose to continue with the PUBLIC role permissions as they are set by the install it is imperative that you override the INSERT, UPDATE and DELETE permissions for any user granted access to an E1 database. Do this by placing them in the db_denydatawriter database role. It is not technically necessary to place the database user in the db_datareader role but doing so will make permissions easier to interpret. I want to note that this method is not perfect since the dBA has to be sure to take this extra step for every new user in an EnterpriseOne database. It may be a good idea to write a SQL Agent job that periodically checks for users in E1 databases that are not in db_denydatawriter and sends an email alert when the condition is found.

The other choice is to remove the permissions assigned to PUBLIC and assign them instead to a role in which you will place the EnterpriseOne system/proxy user(s). Removing the permissions from the PUBLIC role will mean that logins added to the database, and thus members of PUBLIC, will not have permissions beyond what is expected from that role. Once the permissions are granted to the newly created database role, those same permissions can be revoked for the PUBLIC role.

Oracle has a paper detailing the steps to accomplish this: MS SQL Server 2005 Public Shutdown for JD Edwards EnterpriseOne. If you choose this method it is highly recommended you follow Oracle's instructions. As with our other method, there are minor issues: any table generated in OMW will be created in the database with those pesky full permissions for the PUBLIC role. You will need to remember to revoke those permissions if you generate a table using OMW.

So we have two workarounds, each with their own issues that add to the already heavy administrative burden of managing a JD Edwards E1 system. I'd like to offer some permanent fixes. They involve some code change and should be addressed by Oracle.

1- Have the installation ask for the SQL login(s) that is/are to be used as the system/proxy user(s) and assign the appropriate permissions only to that/those logins. Alternately, have the install create a database role and assign the necessary permissions to the role. If the system/proxy user is changed or one is added E1 should simply copy those permissions to that SQL login or role.

2- If permissions have been removed from PUBLIC have OMW not grant them on table generation.

I welcome any other suggestions. Feel free to leave them in the comments.

Bottom line: Unless you take additional steps, any user for which you do not explicitly deny write and delete permissions will have those rights in EnterpriseOne databases. Be careful out there.


In part 3 of the Default SQL Permissions in EnterpriseOne series we'll discuss database level permissions granted during the install and also the implications of having the GUEST user present in an EnterpriseOne database.

Default SQL Permissions in EnterpriseOne Part 1

So you've gotten EnterpriseOne installed or have had it installed for a long time and you've finally changed the default E1 SQL user (CRPDTA, DD812, PRODCTL, etc.) passwords in SQL Server.  If you haven't at least done that you should stop right here and go change those passwords using the article E1: DB: How to Change the Database Object Owner Passwords for EnterpriseOne Databases [ID 629822.1].  If you don't do this, most everyone in the JD Edwards ecosystem knows your passwords.

After this you think you're locked down pretty good from a SQL perspective so you start adding ad-hoc SQL logins - developers that need to look at data, the IT manager who insists he needs to use SQL tools, the accountant who really, really needs a SQL login to use Access to link tables.  Standard practice would be to create the login, grant the login access to the desired database and place them in that database's db_datareader role, granting them read-only rights to the database.  These actions normally produce the desired read-only user in the database. In EnterpriseOne databases, however, it's not that simple.


SQL Permissions

I'm not going to get into a treatise on SQL security concepts but I will briefly cover the topic as it is relevant to our problem:  Permissions in SQL Server are granted, accumulate, are inherited and are overridden by denial.  A database user will accumulate the permissions from server role memberships, database role memberships and permissions explicitly granted to the user.  A DENY at any level overrides any GRANTS.  These permissions can be at the server, database or object level.  The illustration here shows the basics of SQL Server permissions.

Let's move on to a couple of scenarios to illustrate the main point of this series of articles.


Scenarios

A SQL login is granted access to a database and is automatically placed in that database's PUBLIC role.  The user cannot be removed from the PUBLIC role.  The user is then also placed in that database's db_datareader role, which allows them the permissions granted to db_datareader (Run a SELECT statement against any table or view in the database.)  Their effective permissions at this point are a sum of those granted by virtue of their membership in PUBLIC (None) plus those received from membership in db_datareader (SELECT) plus any granted to the user explicitly (None).  This scenario would produce a database user capable of executing SELECT statements on any table in the database.

Another scenario: A SQL login is granted access to an EnterpriseOne database and is automatically placed in the database's PUBLIC role.  The user cannot be removed from the PUBLIC role.  The user is then also placed in that database's db_datareader role, which allows them the permissions granted to db_datareader (Run a SELECT statement against any table or view in the database.)   In this database however, the PUBLIC role has been granted SELECT, INSERT, UPDATE, DELETE, REFERENCES on every table in the database.  The user's effective permissions  are a sum of those granted by virtue of their membership in PUBLIC (SELECT, INSERT, UPDATE, DELETE, REFERENCES) plus those received from membership in db_datareader (SELECT) plus any granted to the user explicitly (None).   In this scenario, the login that you just granted access to JDE_PRODUCTION, in what you thought was a read-only role, in fact has permissions to also INSERT, UPDATE and DELETE data in all tables in the database.  Not exactly what you had in mind is it?

If the database's PUBLIC role has been granted permissions (as it has in EnterpriseOne databases) then the user will inherit those permissions, in addition to any permissions that may have been explicitly granted to the user, either at the database or object level, or by virtue of the user's membership in another database role such as db_datareader.


Background

Since Oracle has no way of knowing exactly which SQL login is going to be used as your system/proxy user, the EnterpriseOne install cannot easily determine to whom the required permissions should be assigned.  Oracle (and JD Edwards before them) takes the easy way out and assigns the permission to the SQL database role PUBLIC.  The dirty deed is done in the DB_SQLSRVR_INSTALL.sql script:

select @objNew = @szNewOwner + '.' + @tablename
select @szGrantStr = 'GRANT REFERENCES , SELECT , INSERT , DELETE , UPDATE ON ' + @objNew + ' TO public'
exec sp_executesql @szGrantStr

You can clearly see where the code grants REFERENCES, SELECT, INSERT, DELETE, UPDATE on each table to PUBLIC. The result is that anyone who is a member of a database's PUBLIC role (everybody who has access to that database) inherits those permissions.  Unless the dBA explicitly denies the undesired permissions, you have a huge security hole.


In part 2 of the series I'll demonstrate the permissions problem and offer several suggestions on how to  deal with the issue and secure your EnterpriseOne databases.

How Large Are My Tables?

Sometimes I am beating around on a SQL script that I really want to share with others. A couple of scripts I wrote to show the largest tables in a database and the largest tables in all databases on a SQL server is a perfect example.

The scripts make extensive use of the SQL 2005/2008 dynamic management view sys.dm_db_partition_stats. This view displays information about the space used to store data in a database. A concatenation method gives us the three-part name of the table and a simple JOIN brings in the Object Librarian name for the table. The results can be sorted either by row count or size in megabytes by commenting or uncommenting the ORDER BY clause.

The scripts are particularly useful for EnterpriseOne systems to identify tables that are candidates for archiving or tables that need maintenance/purging done. An example is the F98865, a table containing Work Flow Processes that should be purged periodically. With these scripts you can see what the largest tables in your SQL Server databases are.



For E1 Databases - Returns Object Librarian Description

Since we are primarily concerned with EnterpriseOne tables, the first version of the largest tables script is for a single database that is used by JD Edwards. A JOIN is used to bring in the Object Librarian Description from F9860.

--SQL Script begin
USE databasename
GO
IF DB_NAME() LIKE 'JDE%'
BEGIN
SELECT DB_NAME() + '.' + sys.schemas.name + '.' + sys.objects.name as 'SQL Object Name',
a.SIMD as 'Object Librarian Description', st.row_count as 'Rows', CAST(sum (reserved_page_count) * 8.0 / 1024 as DEC (38,2)) as 'Object Size (MB)'
FROM sys.objects
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
FULL JOIN OPENDATASOURCE(

'SQLOLEDB',

'Data Source=sqlserverhostingJDE812database;integrated security=sspi'

).JDE812.OL812.F9860 a on (sys.objects.name = a.SIOBNM COLLATE SQL_Latin1_General_CP1_CI_AS)
JOIN sys.dm_db_partition_stats st on sys.objects.object_id = st.object_id
WHERE sys.objects.type = 'U' and st.index_id in (0,1)
GROUP BY sys.schemas.name, sys.objects.name, a.SIMD, st.row_count
ORDER BY st.row_count DESC
--ORDER BY sum (reserved_page_count) * 8.0 / 1024 DESC
END
ELSE
PRINT 'Non-EnterpriseOne Database'
--SQL Script end

As configured this script will return a results set containing a three-part object name, the Object Librarian name, the number of rows and the size taken by each table's data and indexes.

A sample of the results:

SQL Object Name	Object Librarian Description	Rows	Object Size (MB)
JDE_DEVELOPMENT.TESTCTL.F98865	Task Instance	2649997	5065
JDE_DEVELOPMENT.TESTDTA.F0911	Account Ledger	2080214	5289
JDE_DEVELOPMENT.TESTDTA.F00165	Media Objects storage	1183096	2702
JDE_DEVELOPMENT.TESTCTL.F98860	Process Instance	1060162	2290

Again, the results can be sorted either by the row count (default) or object size. Simply change the commenting in the code to change the sort.


For Non-E1 Databases

Since we cannot join non-EnterpriseOne databases on the Object Librarian Object Name we can simply leave that portion out and still get the other information:

--SQL Script begin
USE databasename
GO
SELECT DB_NAME() + '.' + sys.schemas.name + '.' + sys.objects.name as 'SQL Object Name', st.row_count as 'Rows', CAST(sum (reserved_page_count) * 8.0 / 1024 as DEC (38,2)) as 'Object Size (MB)'
FROM sys.objects
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
JOIN sys.dm_db_partition_stats st on sys.objects.object_id = st.object_id
WHERE sys.objects.type = 'U' and st.index_id in (0,1)
GROUP BY sys.schemas.name, sys.objects.name, st.row_count
ORDER BY st.row_count DESC
--ORDER BY sum (reserved_page_count) * 8.0 / 1024 DESC
--SQL Script end

This yields similar results:

SQL Object Name	Rows	Object Size (MB)
ARCDTA.dbo.F42199	15483088	25242
ARCDTA.dbo.F42119	2420972	4345
ARCDTA.dbo.F4074	2184022	553
ARCDTA.dbo.F49219	814320	399

For all E1 Databases on a SQL Server

In some cases we may wish to gain insight into all EnterpriseOne table sizes in every JD Edwards database. For this one we create a temporary table, insert the results from the same query above but wrapped inside SQL Server's undocumented sp_MSforeachdb stored procedure, then read that temporary table.

In this case, since we are only interested in EnterpriseOne databases we specify that we only want database names like JDE%. This admittedly is a little kludgy and will not work on some releases but you are always free to change my code to your liking.

--SQL Script begin
CREATE TABLE #Results
([SQL Object Name] varchar (200),
[Object Librarian Description] varchar (60),
Rows int,
[Object Size (MB)] DEC (38,2))
GO
INSERT INTO #Results
EXEC sp_MSforeachdb @command1 = 'USE [?];
IF DB_Name() LIKE ''JDE%''
BEGIN
SELECT DB_NAME() + ''.'' + sys.schemas.name + ''.'' + sys.objects.name ,
a.SIMD, st.row_count, sum (reserved_page_count) * 8.0 / 1024
FROM sys.objects
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
FULL JOIN OPENDATASOURCE(

'SQLOLEDB',

'Data Source=sqlserverhostingJDE812database;integrated security=sspi'

).JDE812.OL812.F9860 a on (sys.objects.name = a.SIOBNM COLLATE SQL_Latin1_General_CP1_CI_AS)
JOIN sys.dm_db_partition_stats st on sys.objects.object_id = st.object_id
WHERE sys.objects.type = ''U'' and st.index_id in (0,1)
GROUP BY sys.schemas.name, sys.objects.name, a.SIMD, st.row_count
END'
GO
SELECT * FROM #Results
ORDER BY Rows DESC
--ORDER BY [Object Size (MB)] DESC
GO
DROP TABLE #Results
--SQL Script end

The results will look exactly like the above but will contain data from all E1 databases ordered by row count. Uncomment/comment the ORDER BY clauses to sort by Size.


For All Databases on a SQL Server

Finally, we get to the script that gives us table sizes and rows for each and every database on a SQL server. Since the results could contain both E1 and non-E1 objects we leave out the Object Librarian Description.

--SQL Script begin
CREATE TABLE #Results
([SQL Object Name] varchar (200),
Rows int,
[Object Size (MB)] DEC (38,2))
GO
INSERT INTO #Results
EXEC sp_MSforeachdb @command1 = 'USE [?];
SELECT DB_NAME() + ''.'' + sys.schemas.name + ''.'' + sys.objects.name, st.row_count, sum (reserved_page_count) * 8.0 / 1024
FROM sys.objects
JOIN sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
JOIN sys.dm_db_partition_stats st on sys.objects.object_id = st.object_id
WHERE sys.objects.type = 'U' and st.index_id in (0,1)
GROUP BY sys.schemas.name, sys.objects.name, st.row_count'
GO
SELECT * FROM #Results
ORDER BY Rows DESC
--ORDER BY [Object Size (MB)] DESC
GO
DROP TABLE #Results
--SQL Script end

The results from this script will appear a bit odd since they will contain a mix of E1 and non-E1 tables:

SQL Object Name	Rows	Object Size (MB)
Allegro.dbo.dbaudit	41948619	9715
Allegro72_Test.dbo.dbaudit	38409689	8905
Allegro80_BPC_Arch.dbo.physicalquantity	10241635	2273
JDE_DV812.DV812.F980021	4886400	11472

There you are - a couple of scripts to give one some insight into the size of tables in their databases, both EnterpriseOne and non-EnterpriseOne.

Identify SQL Table Backups

Earlier we discussed methods for executing quick SQL table backups and performing quick SQL table restores as a way to mitigate risk to data during certain operations.  In this article we are going to discuss some low-effort housekeeping methods to keep us from forgetting about the table backups we created.

While it is not a huge deal that a few table backups are hanging out in your databases, having a large number of these backups can make things disorganized and large table backups can take up space unnecessarily.  In general it is a good habit to keep your environment clean, but how much time and effort are we willing to spend doing so?  At some point the benefit of numerous housekeeping chores is outweighed by the time and effort of not only performing the chores but keeping track of them.

Therein lies the beauty of the instructions in this article: once created, the process of identifying old table backups is entirely automated.  It is true that one still does have to manually remove the tables designated - we do not want the machines to have too much autonomy, but by using Transact-SQL functions, SQL Server Agent and SQL Database Mail we can have the database server send us a list of old table backups on a regular basis.


Configuration

The first step is to create the stored procedure that will identify the old table backups.  This user stored procedure will be referenced by code in a SQL Agent job and is the heart of the process.

--SQL Script begin
USE MASTER
GO
if exists (select * from INFORMATION_SCHEMA.ROUTINES where SPECIFIC_NAME =
N'usp_TableBackupsOlderThan2Weeks')
DROP PROC usp_TableBackupsOlderThan2Weeks
GO
CREATE PROC usp_TableBackupsOlderThan2Weeks
as

exec sp_MSforeachdb
@command1='if (select count (*)
from [?].sys.objects
where name like ''F%[_]20%''
and type_desc not like ''FOREIGN_KEY_CONSTRAINT''
and DATEDIFF(day, modify_date, GETDATE()) > 14
or name like ''F%bak%''
and type_desc not like ''FOREIGN_KEY_CONSTRAINT''
and DATEDIFF(day, modify_date, GETDATE()) > 14) > 0
BEGIN
print ''?''
select cast (db_name (DB_ID(''?'')) + ''.'' + [?].sys.schemas.name + ''.'' + [?].sys.objects.name as char(55)) as ''Table Name'',
cast ([?].sys.objects.create_date as char (25)) as ''Created''
from [?].sys.objects
JOIN [?].sys.schemas on [?].sys.objects.schema_id = [?].sys.schemas.schema_id
where [?].sys.objects.name like ''F%[_]20%''
and [?].sys.objects.type_desc not like ''FOREIGN_KEY_CONSTRAINT''
and DATEDIFF(day, [?].sys.objects.modify_date, GETDATE()) > 14
or [?].sys.objects.name like ''F%bak%''
and [?].sys.objects.type_desc not like ''FOREIGN_KEY_CONSTRAINT''
and DATEDIFF(day, [?].sys.objects.modify_date, GETDATE()) > 14
order by ''Table Name''
Print ''

''
END'
--SQL Script end

The two most important parts of the above code are:

where name like ''F%[_]20%''

and

or name like ''F%bak%''

These are the sections that modify the SELECT statement with a WHERE clause that uses pattern matching and SQL wildcard characters to choose records that match the names of table backups created by our earlier script, which produces tables named something like JDE_PRODUCTION.PRODDTA.F0101_200906081807.  Note that in the first example the underscore character is not being used as a SQL wildcard, I am actually looking for an underscore, which is why it is enclosed in brackets.

I have also included a pattern match for tables that begin with 'F' and contain the string 'bak', a typically used naming convention for EnterpriseOne table backups.  You can add or remove additional clauses to tune the query for your particular environment but if all you are using to produce quick table backups is my Quick SQL Table Backups script, then the above will be sufficient.

Another important part of the code is the '> 14' portion of each WHERE clause section.  This dictates that we want records returned only for tables that are older than two weeks.  Feel free to modify this value to suit your needs.


The second configuration step is to create the notification delivery mechanism - a combination of a SQL Agent job and SQL Database Mail.  If you have not already configured Database Mail, here is a very good article on how to do so.


Create a SQL Agent job named E1_Identify SQL Table Backups Older Than 2 Weeks (or whatever time period you specified in the WHERE clause section).

Schedule the job to run every month, preferably on the same day every month.  I choose the second Monday of every month for mine but again, change to suit your needs.

Create a job step called Send Mail or something suitably witty or descriptive, it really doesn't matter much.  Specify Transact-SQL as the type and use the following code as the command:

--SQL Script begin
EXEC databaservername.msdb.dbo.sp_send_dbmail
    @profile_name = 'default',
@recipients = 'email.address@domain.com;email.address2@domain.com',
    @subject = 'Table Backups Older Than 2 Weeks',
    @query = 'exec master.dbo.usp_TableBackupsOlderThan2Weeks',
       @body = 'These table backups are older than 2 weeks and can be removed:

'
--SQL Script end

Be sure to change the red-shaded items above to values that make sense for your system.  The 'profile name' variable should match a Database Mail profile that exists and that you wish to use to send the email. 

Note that the 'query' variable references the user stored procedure we create in the first configuration step. Also note that there is a full blank line in the value for the 'body' variable.  That exists for email formatting and the email is quite ugly without it.

Speaking of formatting, we can now look at what we will receive once a month in our email.


Results

The values specified in the sp_send_mail variables mean that we will receive an email with the subject 'Table Backups Older Than 2 Weeks', an initial body 'These table backups are older than 2 weeks and can be removed:' and the results of the query 'exec master.dbo.usp_TableBackupsOlderThan2Weeks'.

The stored procedure specified in the query, master.dbo.usp_TableBackupsOlderThan2Weeks, makes use of the undocumented stored procedure sp_MSforeachdb which means that we will get the list of tables matching the patterns specified in the WHERE clauses grouped by database. Databases with no tables matching the specified patterns will not be included in the result set.

The emailed results will look something like this:


These table backups are older than 2 weeks and can be removed:


JDE812
Table Name                     Created Date/Time         Modified Date/Time      
------------------------------ ------------------------- -------------------------
dbo.F9006_bak                  Mar 25 2009  4:11PM       Mar 25 2009  4:11PM     
SVM812.F986101_bak2            Feb  6 2009  7:14PM       Feb  6 2009  7:14PM     
SY812.F986101_200907151136     Jul 15 2009 11:36AM       Jul 15 2009 11:36AM     


JDE_PRODUCTION
Table Name                     Created Date/Time         Modified Date/Time      
------------------------------ ------------------------- -------------------------
dbo.F98865_bak                 Dec 17 2008  7:15PM       Dec 17 2008  7:15PM     
PRODDTA.F0011_200910061837     Oct  6 2009  6:37PM       Oct  6 2009  6:37PM     


JDE_DV812
Table Name                     Created Date/Time         Modified Date/Time      
------------------------------ ------------------------- -------------------------
DV812.F983051_200912021511     Dec  2 2009  3:11PM       Dec  2 2009  3:11PM     


JDE_DEVELOPMENT
Table Name                     Created Date/Time         Modified Date/Time      
------------------------------ ------------------------- -------------------------
TESTDTA.F989998_200910230855   Oct 23 2009  8:55AM       Oct 23 2009  8:55AM     
TESTDTA.F989999_200910230855   Oct 23 2009  8:55AM       Oct 23 2009  8:55AM     





As you can see, the email contains tables that match the patterns specified in the WHERE clauses and are older than 2 weeks.  The tables are grouped by database and ordered by schema name.table name with the columns Created Date/Time and Modified Date/Time provided as additional information.


Summary

A certain amount of housekeeping is necessary to keep things organized in your SQL Server installations.  However, keeping track of such tasks can be burdensome and we'd like to do whatever we can to make use of the features of SQL Server to automate items, essentially putting as much of the housekeeping on autopilot.  Using Transact-SQL code, SQL Server Agent and Database Mail we are able to be notified on a periodic basis when there are table backups that can be removed.



Related postings:

Quick SQL Table Backup
http://jeffstevenson.karamazovgroup.com/2009/06/quick-sql-table-backup.html

Quick SQL Table Restore
http://jeffstevenson.karamazovgroup.com/2009/12/quick-sql-table-restore.html

Quick SQL Table Restore

A while back I discussed a method to do quick SQL table backups.  I usually create backups of tables prior to taking an action that has the potential to create the need to restore that table's data.  It's just a good idea, is easier than taking a full backup and gives you a readily available source of the original data should something go wrong with the changes you make.

Not that it has ever happened to me...but occasionally the need may arise to restore this data to the table you just butchered.

If you used the script in quick SQL table backups you ended up with a table backup named something like PRODDTA.F0101_200912071424 with PRODDTA being the schema, F0101 the table name and 200912071424 representing the date and time as YYYYMMDDHHMM.

We can use INSERT INTO to restore the data from this backup table to the original table but it requires us to truncate the original table, deleting all existing records.  The INSERT command appends records and any unique constraints in place on the original table will be observed, resulting in a "Violation of PRIMARY KEY constraint" error if you attempt to restore the data without clearing the original table.

Truncating any table is not a task lightly undertaken and the pucker factor can be pretty high.  Relax though, we do have a copy of the data in a table backup right?


To clear the original table we use the TRUNCATE command in the form:

TRUNCATE TABLE databasename.schemaname.originaltablename

--SQL Script begin
TRUNCATE TABLE JDE_PRODUCTION.PRODDTA.F0101
--SQL Script end


With the original table suitably cleared we can move forward with putting the backup table's data back into the original using this form:

INSERT INTO databasename.schemaname.originaltablename SELECT * from databasename.schemaname.backuptablename

--SQL Script begin
INSERT INTO JDE_PRODUCTION.PRODDTA.F0101 SELECT * from JDE_PRODUCTION.PRODDTA.F0101_200912071424
--SQL Script end


That takes care of getting the data back into the table but we have one last step to complete the recovery - rebuilding indexes and updating statistics.  While the data being restored to the original table is the exact same as what existed before, the storage engine needs to be re-taught what data is where by rebuilding the B-tree for the original table's indexes.

Since rebuilding indexes also accomplishes the goal of updating statistics we are going to execute the index rebuild only in this form:

ALTER INDEX ALL ON databasename.schemaname.originaltablename
            REBUILD

--SQL Script begin
ALTER INDEX ALL ON JDE_PRODUCTION.PRODDTA.F0101
REBUILD
--SQL Script end


That completes our quick SQL table restore.   You're back up and running with minimal interruption.


Related postings:

Quick SQL Table Backup
http://jeffstevenson.karamazovgroup.com/2009/06/quick-sql-table-backup.html

Identify SQL Table Backups
http://jeffstevenson.karamazovgroup.com/2009/12/identify-sql-table-backups.html

Rebuild SQL Indexes in E1 Package Tables

With the change to table-based, XML-format metadata (formerly specs) in EnterpriseOne, first-use dynamic e-generation became a performance issue, impacting end users after a package deployment and affecting their perception of system performance. This article describes an easy method of lessening this impact.

A very short primer on auto package discovery and dynamic egeneration as it applies to the package tables:

Since 8.12/8.96, when a package is built, tables are created in the build environment's Central Objects database containing metadata for the objects in the package. The tables, named CentralObjectsTablePackageName (Ex: F98762BPDOCF001) are used by both the Enterprise servers and Web servers, guaranteeing consistency across the system.

The Enterprise servers access the package tables for objects as they are needed, bringing them across the wire to a local cache in the filesystem. The Java servers access the same package tables, bringing the objects across the wire, serializing them into the Serialized Objects tables and also placing them in cache.

Since the Enterprise and Java server now access the object metadata from the database server instead of local TAM files in the case of the Enterprise server and Serialized Objects (Java servers), there is now an additional burden associated with retrieving object metadata, an increase in the network load between the Enterprise/Java servers and the database server and an increase in the load on the database server.

A read of records in the package tables can be optimized by ensuring that the indexes are properly defragmented and the statistics are up-to-date. In SQL Server the index rebuild process does both.

Since we now have the XML metadata in tables, and we know that the dynamic e-generation is going to be accessing these tables heavily in the period immediately following a package deployment, it behooves us to rebuild the indexes on the package tables.

Execute the SQL2005/SQL2008 transact sql code below after the build completes but prior to deploying the package.


--SQL Script Begin
-- Enter appropriate variables in the SET statements for:
-- @DatabaseName
-- @SchemaOwner
-- @PackageName

declare @DatabaseName varchar (50)
declare @SchemaOwner varchar (50)
declare @PackageName varchar(50)
declare @SQL varchar(8000)

SET @DatabaseName = 'JDE_PD812'
SET @SchemaOwner = 'PD812'
SET @PackageName = 'BPDOCF001'

SET @SQL =
'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98762' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98720' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98711' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98713' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98712' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98710' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98743' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98751' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98750' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98740' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98741' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98306' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98761' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98760' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98745' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98753' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98752' + @PackageName + ' REBUILD ' + CHAR(13)
+ 'ALTER INDEX ALL ON ' + @DatabaseName + '.' + @SchemaOwner + '.F98770' + @PackageName + ' REBUILD ' + CHAR(13)

EXEC (@SQL)
--SQL Script End

Rebuilding the indexes of the package tables helps lessen the burden on the database server during the dynamic (or manual) e-generation process and improves object first-use performance.

EnterpriseOne SQL Security

Permissions in SQL for E1 are granted thru the System User/Multiplexing User's (JDE usually) membership in the database role PUBLIC.

The PUBLIC database role has Create Table permissions.

The PUBLIC database role has SELECT, INSERT, UPDATE, DELETE permissions on all tables within the database.

The object owner (PRODDTA, PRODCTL, etc.) database role has explicitly granted object permissions to allow SELECT, INSERT, UPDATE, DELETE. This allows the object owner to perform certain actions through E1 (R98403, Copy Table, etc.)

Because the PUBLIC role has database and object level permissions it is imperative that any newly created SQL Server login that you wish to have read-only access to a database be placed in the db_denydatawriter database role in addition to the db_datareader database role to explicitly override INSERT, UPDATE, DELETE permissions granted to all database users via their membership in the PUBLIC role.

Note: Due to the way E1 grants object permissions via PUBLIC, it is not technically necessary to place a database user in the db_datareader role. However, doing so will make permissions viewing easier.

It is also important that a newly created SQL Server login be explicitly denied the create table permissions in the database at the user level if table creation is not desired.


Two ways to deal with this:

Continue to use Public security and

1) remove SQL Server guest user from each database (SQL 2000) or revoke CONNECT permission (SQL 2005/SQL 2008) and
2) be sure to place ad-hoc (non-E1 app) users in the db_datareader and db_denydatawriter roles for each database
3) Deny CREATE TABLE to new SQL Login

or

Perform Oracle's MS SQL Server 2005 Public Shutdown for JD Edwards EnterpriseOne

I prefer the first method.

Quick SQL Table Backup

Occasionally one needs to perform an action in either EnterpriseOne or SQL Server that places the data in one or more tables at risk. Examples include an index or (obviously) a table generation in OMW, a direct update to the data using a query, the first run of a custom UBE that updates data, etc. Best practices dictate that you should get a backup of the data.

Rather than using the time consuming process of backing up the entire database, and in the absence of table-specific backup/restore tools, one can quickly and easily make a copy of the table using standard SQL T-SQL code.

The command is SELECT INTO, or actually a combination of the SELECT command and the INTO clause. The basic statement looks something like


SELECT *
INTO new_table_name
FROM old_tablename

or

SELECT *
INTO JDE_PRODUCTION.PRODDTA.F0101_BAK
FROM JDE_PRODUCTION.PRODDTA.F0101


While this simple piece of code is sufficient to perform the copy, several problems arise. First, the code above requires you to correctly specify both the source and target table. Also, the SELECT INTO method will create a new table but it will not overwrite a table with the existing name. So, unless you feel like checking for the existence of a table named "JDE_PRODUCTION.PRODDTA.F0101_BAK" every time you perform this action I would suggest using the code below.

In this version we get a uniquely named table as long as you don't run it twice within a minute. The name will consist of the original table name plus characters representing the year, month, day, hour and minute. To do this, we grab the current date and time using GETDATE, convert it to text, replace characters like spaces, commas, etc. that we do not want in the table name, build this into a string, then use EXEC (@SQL) to run the query.

The code looks like this:

--SQL Script begin
DECLARE @Tablename NVARCHAR(500)
DECLARE @BuildStr NVARCHAR(500)
DECLARE @SQL NVARCHAR(500)
SET @Tablename = 'JDE_PRODUCTION.PRODDTA.F0101'
SET @BuildStr = CONVERT(NVARCHAR(16),GETDATE(),120)
SET @BuildStr = REPLACE(REPLACE(REPLACE(REPLACE(@BuildStr,'
',''),':',''),'-',''),' ','')
SET @SQL = 'select * into '+@Tablename+'_'+@BuildStr+' from '+@Tablename
SELECT @SQL
--Remove dashes on the line below to execute
--EXEC (@SQL)
--SQL Script end

and generates a query that looks like this:

select * into JDE_PRODUCTION.PRODDTA.F0101_200906081807 from JDE_PRODUCTION.PRODDTA.F01012


In the example above we have specified the three part name "JDE_PRODUCTION.PRODDTA.F0101" in the @Tablename variable. You would change this variable to specify your table. Figured I'd mention that 'cause you just never know.

As constructed above, the query runs SELECT @SQL and will simply return the query string we built. It will not execute the copy. Once you are satisfied that the query string built is correct, remove the dashes from in front of EXEC (@SQL) to run the table copy.

You will see something like this when properly executed:

select * into JDE_PRODUCTION.PRODDTA.F0101_200906081807 from JDE_PRODUCTION.PRODDTA.F0101

(1 row(s) affected)

(65436 row(s) affected)


Once you have completed the quick table copy you can now continue with the action that placed the data in danger knowing that you have a copy of the data.

Some caveats apply: the copy only brought over the data. No indexes exist on the backup copy of the table. Also, you now have an easily forgotten extra copy of what might be a very large table. Clean it up as a part of your scheduled maintenance, set a reminder to delete it later or use this method to automatically identify SQL Table Backups for removal.


Related postings:

Quick SQL Table Restore
http://jeffstevenson.karamazovgroup.com/2009/12/quick-sql-table-restore.html

Identify SQL Table Backups
http://jeffstevenson.karamazovgroup.com/2009/12/identify-sql-table-backups.html

Determine Objects Owned by a SQL Login in all Databases

I was trying to delete a SQL login and received the following message:

"You cannot drop the selected ID because that login ID owns objects in one or more databases"

I wrote a little script that utilizes the always useful sp_MSForEachDB stored procedure to walk through each database and find the objects owned by a specified user.

SQL 2000

=======

--SQL Script begin

EXEC sp_MSForEachDB 'USE [?]; PRINT ''?''; select * from sysobjects where uid =
(select uid from sysusers where name = ''username'')'

--SQL Script end


SQL 2005

=======

--SQL Script begin

EXEC sp_MSForEachDB 'USE [?]; PRINT ''?''; select * from sys.objects where schema_id =

(select principal_id from sys.database_principals where name = ''username'')'

--SQL Script end

It is probably best to use CTL-T to have the output in text instead of grid format.

Script file for queries used in this article is here:

http://sites.google.com/a/karamazovgroup.com/blogfiles/Home/sql-server-scripts/Objectsownedbyauserinalldatabases.sql?attredirects=0

All SQL script files are here:

http://blogfiles.karamazovgroup.com/Home/sql-server-scripts

List Submitted Jobs From All Servers

There are times when CNC administrators need to view a list of all submitted jobs in a single result set. E1 tools or standard SQL scripts do not satisfy this requirement but the script below will allow one to view all submitted UBE's from all servers in one list.

The script makes use of the OPENDATASOURCE method, Integrated Security authentication, and the Union operator to allow the script to be run on any SQL server, without the need to hard code password information, and combines the results into a single grid.

Notes: In the OPENDATASOURCE section there are two variables that must be modified to specify your connection information.

1- Change "Data Source" to your SQL Server:

Data Source=sqlserver_hosting_batch_server's_server_map

and

2- Change schema name or object owner in the fully qualified object name section:

(jde812.svm812.f986110)


Add or remove Union and Select sections in the script to suit your needs. Every batch server that has a separate server map requires its own section.


--SQL Script begin
--Batch Server 1
select jcjobque as 'Job Queue', jcfndfuf2 as 'Job Name', jcuser as 'User', jcenhv as 'Environment', cast(jcsbmdate as float(6))as 'Date Submitted', cast(jcsbmtime as float(6))as 'Time Submitted', cast(jcactdate as float(6))as 'Date Completed', cast(jcacttime as float(6))as 'Time Completed', cast(jcexehost as char(10))as 'Server', cast(jcorghost as char(10))as 'Subm. Host', jcjobsts as 'Status'
from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=sqlserver_hosting_batch_server1_server_map;integrated security=sspi'
).jde812.svm812.f986110

union all

--Batch Server 2
select jcjobque as 'Job Queue', jcfndfuf2 as 'Job Name', jcuser as 'User', jcenhv as 'Environment', cast(jcsbmdate as float(6))as 'Date Submitted', cast(jcsbmtime as float(6))as 'Time Submitted', cast(jcactdate as float(6))as 'Date Completed', cast(jcacttime as float(6))as 'Time Completed', cast(jcexehost as char(10))as 'Server', cast(jcorghost as char(10))as 'Subm. Host', jcjobsts as 'Status'
from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=sqlserver_hosting_batch_server2_server_map;integrated security=sspi'
).jde812.svm812a.f986110

union all

--Batch Server 3
select jcjobque as 'Job Queue', jcfndfuf2 as 'Job Name', jcuser as 'User', jcenhv as 'Environment', cast(jcsbmdate as float(6))as 'Date Submitted', cast(jcsbmtime as float(6))as 'Time Submitted', cast(jcactdate as float(6))as 'Date Completed', cast(jcacttime as float(6))as 'Time Completed', cast(jcexehost as char(10))as 'Server', cast(jcorghost as char(10))as 'Subm. Host', jcjobsts as 'Status'
from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=sqlserver_hosting_batch_server3_server_map;integrated security=sspi'
).jde812.svm812b.f986110

union all

--Batch Server 4
select jcjobque as 'Job Queue', jcfndfuf2 as 'Job Name', jcuser as 'User', jcenhv as 'Environment', cast(jcsbmdate as float(6))as 'Date Submitted', cast(jcsbmtime as float(6))as 'Time Submitted', cast(jcactdate as float(6))as 'Date Completed', cast(jcacttime as float(6))as 'Time Completed', cast(jcexehost as char(10))as 'Server', cast(jcorghost as char(10))as 'Subm. Host', jcjobsts as 'Status'
from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=sqlserver_hosting_batch_server4_server_map;integrated security=sspi'
).jde812.svm812c.f986110

union all

--Batch Server 5
select jcjobque as 'Job Queue', jcfndfuf2 as 'Job Name', jcuser as 'User', jcenhv as 'Environment', cast(jcsbmdate as float(6))as 'Date Submitted', cast(jcsbmtime as float(6))as 'Time Submitted', cast(jcactdate as float(6))as 'Date Completed', cast(jcacttime as float(6))as 'Time Completed', cast(jcexehost as char(10))as 'Server', cast(jcorghost as char(10))as 'Subm. Host', jcjobsts as 'Status'
from OPENDATASOURCE(
'SQLOLEDB',
'Data Source=sqlserver_hosting_batch_server5_server_map;integrated security=sspi'
).jde812.svm812d.f986110

order by cast(jcactdate as float(6)) desc, cast(jcacttime as float(6)) desc
--SQL Script end


The result set will appear as a single list of all jobs submitted to all E1 servers, in descending order by date of last activity.



Functional Possibilities of the Script

In addition to being used to view submitted jobs from all servers in one place, the concept of the script could be used to correct what I consider to be a significant shortcoming in EnterpriseOne.

A major design goal of E1 (fka OneWorld) was the separation of the technology from the user. The realization of this goal is obvious in many places including database independent data sources, OCM's and multi-platform code. Most end users have no idea what "platform" the "system" runs on and care only that it runs......all day, every day.

I would say that the goal of isolating the user from the configuration was wonderfully accomplished in most cases but EnterpriseOne, in some applications still (as of 8.12/8.97) makes an end user choose a server from a list in order to view their submitted job.

Imagine yourself as an accounting clerk, skilled primarily in the art of numbers and financial concepts submitting a report and being faced with the following screen in order to find the results:



Figure 1 View Job Status - Work With Servers (P986116|ZJDE0001 W986116A)


A list of servers, data sources and Server Map data sources being presented to an end user is contrary to the concept of separation of function and technology and clearly takes us well away from this design goal. Why in the world would it ever be necessary for an E1 user to see or know about this information?

Utilizing the concept of abstracting the servers when users are trying to find their submitted job would bring the View Job Status - Work With Servers application in line with the original design goal of separating technology from functionality and would greatly simplify the end user task of finding their report output.

Script file for queries used in this article is here:

http://sites.google.com/a/karamazovgroup.com/blogfiles/Home/sql-server-scripts/WSJfromallservers.sql?attredirects=0

All SQL script files are here:

http://blogfiles.karamazovgroup.com/Home/sql-server-scripts

Identifying High CPU SQL Processes

Executive Summary

As a SQL Server administrator you are probably familiar with this scenario: The phone rings and it is one of your users saying “the system seems really slow today”, or if you have monitoring and alerting set up you get an email telling you the CPU(s) on your SQL server is maxed at 100%. Regardless of the method of alert, you have to figure out what is causing the problem. You investigate and find that the sqlservr.exe process is using most, if not all of the available CPU cycles. Beyond that however, you have no idea of exactly what in SQL Server is causing the CPU to be pegged at 100%.

When faced with the issue of identifying the exact runaway process or statement that is bringing your SQL Server to its knees you will find that there is no easy solution. There is no single place in the provided tools to dynamically list the percent of CPU taken by each SQL Server thread process.

Even identifying the specific SQL Server process leaves you grasping for more information. You really want to know the exact code or statement that is soaking up precious CPU cycles. Using the techniques described in this paper you will be able to identify the culprit and react appropriately, adding efficiency and value to your organization.


Purpose

The purpose of this paper is to describe the methods that can be used to determine what SQL Server process is consuming CPU cycles. Through the use of Performance Monitor, Query Analyzer, Enterprise Manager and Profiler, tools provided with Microsoft Windows and Microsoft SQL Server, the administrator can pinpoint the specific query or operation that is causing high CPU.

Step-by-step instructions, including screen captures, detailing the setup and execution are provided to enable the system administrator to quickly and accurately determine the exact cause and start him on the correct course to fix the problem. A special section addressing cursors and stored procedures is included to deal with situations where the statement causing the problem is not readily apparent as is common in applications such as JD Edwards EnterpriseOne.

A bonus section is included with additional tips to make the methods described in the paper quick and easy to repeat. Several additional tips related to Oracle JD Edwards EnterpriseOne are included.

Total Pages - 22


Problem

When a high CPU condition caused by SQL Server presents itself on your server, there is no quick, simple way to determine exactly what SQL Server operation is causing the problem.

The steps to get to the answer are not readily apparent, not integrated and do not lend themselves to an easy approach to a solution. We simply need a way to pinpoint the exact operation within SQL Server that is causing the high CPU condition.


Solution Description

To achieve our goal of determining the statement or operation causing high SQL Server CPU we will make use of the multiple SQL Server tools necessary to get to the detail level we desire.

We will follow a troubleshooting trail from Performance Monitor to Query Analyzer to Enterprise Manager to Profiler to get to the bottom of the problem. When completed, you will be able to quickly determine which operation is causing the high CPU problem.

Each step in the solution has detailed setup and execution instructions, complete with screen shots and descriptions.

In order to determine exactly what statement or operation is causing high CPU on the SQL server, it is necessary to follow a path that looks like this:

Instance/Thread->ID Thread->KPID->SPID

To get from the application that can tell you there is a high CPU issue (Performance Monitor) to where you can view the command causing the issue (Enterprise Manager or Profiler) you must follow the trail mentioned above.

To view the statement or operation causing the problem, it is necessary to know the SPID (Server Process ID). To get the SPID you must know the KPID (Kernel Process ID). To get the KPID you must have the ID Thread. To get the ID Thread, you must know the Thread or Instance number. So, you follow the trail above to achieve the goal of determining the exact command causing your high CPU state.


Solution

Setup

Note:  See comments at the end of the article if you would like to use Sysinternals' Process Explorer to determine the KPID instead of perfmon.  It is indeed a little easier.

In Performance Monitor, you will view the SQL threads' CPU consumption. Open perfmon and add the appropriate counters by selecting the "thread" performance object, the counter object “% Processor Time”, and all of the "sqlservr" instances.



Figure 1 Adding thread instances


This will display %Processor Time by thread instance number and allow you to identify the high CPU thread.

The Performance Monitor window will show each instance as a line in graph mode or as bars in histogram mode. The instance counters will be listed at the bottom as well.



Figure 2 Perfmon showing thread instances


Start a second session of perfmon, and click the View/Report button. Then add the performance object "thread", the counter object "ID Thread", and all the "sqlservr" instances.



Figure 3 Adding ID threads


This will appear as a tabular report in Performance Monitor with the ID thread number listed below the corresponding thread instance number.



Figure 4 Perfmon showing thread-ID to thread correlation


Execution

When the occasion arises where you must find out what SQL Server SPID is causing the high CPU, execute the steps below.

In Performance Monitor session #1 find the thread instance that shows sustained high CPU %. Note that on a busy system there may be several thread instances showing high CPU %. If you are troubleshooting a long-running process, the obvious thread will show after a prolonged period of watching. You may need to switch to histogram view in Performance Monitor if there are a large number of active SQL Server threads. Note that it may not be necessary to choose one thread as the process can be spread among multiple threads that all correlate back to a single SPID. For example you may find that instance 28 and instance 50, while having different ID Threads, may have the same SPID and the SPID is what we are trying to find.

Once you choose a thread instance, double-click on the graph line (or bar in histogram view), highlighting the instance in the bottom panel and enabling you to identify the insance number. Note the instance number, in this case instance 28.



Figure 5 Perfmon showing high CPU thread selected


Switch to performance monitor #2 and find the instance number noted above in the Thread row. Note the ID Thread listed below the thread. In this case the thread is 28 and the ID Thread is 2936.



Figure 6 Perfmon showing thread-ID to thread correlation


The ID Thread correlates with KPID (Kernel Process ID). Once we have the KPID we are one step closer to our goal of getting the SPID of the high CPU SQL process. The KPID to SPID relationship can be determined by executing the following SQL statement on the affected server while the offending process is running:

select spid, kpid, status, hostname, dbid, cmd from master..sysprocesses

where kpid != 0 order by kpid

The results show which SPID (Server Process ID) correlates with the KPID. In our case KPID 2936 is associated with SPID 111.



Figure 7 Query Analyzer results correlating KPID-SPID


With this information you can now use Enterprise Manger, sp_who2 or other tools to find out more about what the SPID is doing.

In Enterprise Manager select the appropriate SQL Server then expand Management/Current Activity/Process Info. In the list of Process IDs find the SPID you determined in the previous step. You can double-click the SPID record to see exactly what the last batch command was. In the case of SPID 111, we see that a select statement was being run against CRPDTA.F0911, a table with over 8 million records, causing significant CPU usage by SQL Server.



Figure 8 Enterprise Manager showing process' last command


Cursor Issues

An issue arises however, if the activity you are trying to identify is typical of EnterpriseOne, which makes extensive use of cursors. When correlating back to a SPID, the activity you will likely see is a stored procedure cursor command such as sp_cursorprepexec or sp_cursorfetch instead of an actual SQL statement. All this indicates is that a cursor is being executed and tells you little about the underlying activity that is causing your CPU increase.



Figure 9 Enterprise Manager showing cursor operation


You can still get to the root of your problem if the SPID correlates back to a cursor; it will just take a little more work.


Profiler Setup

To find what activity is associated with the cursor operation you will use SQL Server Profiler, a performance analysis tool that comes with SQL Server and can be used to trace events and activities on your SQL server.

Since Profiler tracing adds overhead, we must very narrowly define our criteria for analysis in Profiler in order to not overburden the server being monitored. Since we are still operating from the assumption that we are searching for the root cause of an activity causing high CPU on SQL Server, it would not be helpful to add to the load unnecessarily.

Open Profiler and select New/Trace from the File menu. Select the affected server from the dropdown list.

On the “General” tab enter the information about your trace. Name your trace, select Template Name “Blank”, select a location to save the trace and set the maximum rows. Since we have the option to save the trace information to a table we shall do so.



Figure 10 Configuring Profiler trace


Select the “Events” tab and add the following events:

Cursors: CursorClose, CursorExecute, CursorOpen,

Performance: Show Plan Text

Stored Procedures: RPC:Completed, RPC:Starting, SP: StmtCompleted, SP: StmtStarting



Figure 11 Configuring Profiler events


These are the events that we will trace in Profiler. Select the “Data Columns” tab and add the following to the “Columns” section:

Start Time, BinaryData, TextData. These values will join EventClass and SPID which are already present.



Figure 12 Configuring Profiler Columns


These are the data items that will be visible in the trace results.

Select the “Filters” tab to set up Profiler to only show the events from the SPID that we discovered in the earlier sections to be the offending process. In the example picture we will presume that the offending SPID was found to be 111. Enter 111 in the SPID/Equals filter to see only events associated with SPID 111.



Figure 13 Configuring Profiler filter


Profiler Execution

Click the Run button and you should see data scrolling by. If the operation you are trying to trace has already gone into the use of cursors you may have difficulties telling exactly what is causing the high CPU. If you are fortunate, a quick examination of the trace results will point to the exact cause.

The trace below shows several select statements interspersed with the cursor executions.



Figure 14 Profile trace results


You should be able to analyze the trace results to determine your high CPU culprit, even if it involves a cursor action. Some detective work may be required but it is still much simpler than guessing what SQL Server process is causing your problem.


Conclusions

Using the methods provided in the solution detailed, an administrator will be able to pinpoint the cause of high CPU in SQL Server.

This solution will be quick, efficient, repeatable and will work regardless of whether the operation causing the increased CPU usage is a simple SQL statement or a stored procedure.

The SQL Server administrator now possesses a reliable method to help answer the question: “Why does the system seem slow today”


Additional Tips

To allow fast reaction time to a high CPU condition you can save the Performance Monitor console settings once you have set them as described in the document. By saving each perfmon session you can quickly open the .msc file and begin observing the SQL Server activity.

By also saving the SQL statement that correlates the KPID to SPID you can also quickly execute it.

You can save the Profiler template to a file that preserves the settings for quick access. Opening the Profiler template and changing the SPID ID filter to the appropriate SPID is all you have to do to start capturing transaction information.

The KPID can be associated back to a EnterpriseOne batch job or OneWorld kernel process. Simply take the KPID found in the ID Thread row of perfmon #2 and check task manager on the enterprise server the UBE or BSFN is running on. Sort the list by PID and look for the ID Thread (KPID) number. You can also find the server log for this process in C:\JDEdwardsOneWorld\ddp\B7334\Log\jde_nnnn.log where nnnn is the KPID number.

In the above, if the KPID is a OneWorld batch job, you can go to Work With Submitted Jobs and enter the KPID in the Process ID field to determine which UBE is causing the issue.

If the above is a JAS-related process one can try to find the KPID in the Host-PID field of the User List of SAW WEB

Whitepaper is here: http://blogfiles.karamazovgroup.com/Home/whitepapers/SQL%2CIdentifyhighCPUSQLProcessesv1.00.doc?attredirects=0