Sage 100

What is the equivalent of ODBC security with Premium?  Have a Crystal user we need to keep out of Payroll and GL.

------------------------------
Beth Bowers
Mom to Samson, Peanut, ChiChi, Canton, Cagney and Daisy (NO Oxford comma - shriek!)
Beth Bowers
269-445-1625
------------------------------

In SQL Management Studio
Create a SQL user - see https://www.guru99.com/sql-server-create-user.html if you need to see how to do this.
Assign only the public role on the MAS_XXX database.
For this example it is user_read_only.
Click New Query and copy and paste the following in:
USE MAS_XXX;
SELECT 'GRANT SELECT ON "' + TABLE_SCHEMA + '"."' + TABLE_NAME + '" TO "user_read_only"' FROM information_schema.tables
WHERE LEFT(TABLE_NAME,2) IN ('AR','AP','CI','IM','PO','SO')

Replace XXX with the company code, and add the prefix for any other modules that need access to the IN section.
Execute the query
In the results section, select all rows, copy to clipboard.
Click New Query and type USE MAS_XXX;, press enter, and paste the results from the first query
Execute.

If there are multiple companies that need this, just change the USE MAS_XXX line and execute again.



------------------------------
Phil McIntosh
President
Friendly Systems, Inc.
Asheville NC
678.273.4010 ext 5
------------------------------

THANK YOU, @Phil McIntosh!!!!​

------------------------------
Beth Bowers
Mom to Samson, Peanut, ChiChi, Canton, Cagney and Daisy (NO Oxford comma - shriek!)
Beth Bowers
269-445-1625
------------------------------

Original Message:
Sent: 05-21-2020 12:51
From: Phil McIntosh
Subject: ODBC Security

In SQL Management Studio
Create a SQL user - see https://www.guru99.com/sql-server-create-user.html if you need to see how to do this.
Assign only the public role on the MAS_XXX database.
For this example it is user_read_only.
Click New Query and copy and paste the following in:
USE MAS_XXX;
SELECT 'GRANT SELECT ON "' + TABLE_SCHEMA + '"."' + TABLE_NAME + '" TO "user_read_only"' FROM information_schema.tables
WHERE LEFT(TABLE_NAME,2) IN ('AR','AP','CI','IM','PO','SO')

Replace XXX with the company code, and add the prefix for any other modules that need access to the IN section.
Execute the query
In the results section, select all rows, copy to clipboard.
Click New Query and type USE MAS_XXX;, press enter, and paste the results from the first query
Execute.

If there are multiple companies that need this, just change the USE MAS_XXX line and execute again.



------------------------------
Phil McIntosh
President
Friendly Systems, Inc.
Asheville NC
678.273.4010 ext 5
------------------------------