Lets say you have Sage 500 user who also has SQL Server Management Studio(SSMS), and knows how to access the data through SSMS. Is there a way to prevent this user to see data from SSMS but still have access through the Sage 500 application?  The answer is yes!

Sage 500 can make use of a T-SQL application role to restrict access to the application database.  Users are added to the Application Role group and the role uses a special password. If the password is valid, then the application can continue, if it is not valid, the application prevents access to the database.

This means that if aUser has their Application Role turned on within Sage 500, that they can access the Sage 500 as defined by their Sage 500 security group. However, if they happen to have SQL Server Management Studio installed locally, they can log into the same server where the Sage 500 application is located. However, they will not be able to access the objects within it however.

Lets look at how to set that up.  Navigate to System Manager, Maintenance, Maintain Users. Then,  lets create user named aUser

Enter user information and place a checkmark next to  Use Application Role and save record

We can log into Sage 500 and perform tasks as defined by our Sage 500 security.

If the user had access to SQL Server Management Studio, they would still be able to login and change the database context to point to the Sage 500 application database. But if they tried to run a T-SQL query to read the data from the tables, they would encounter an error such as the below:

The user can navigate through SQL Server Management Studio, expand the list of objects under the Sage 500 application database and see the object names, indexes, triggers, etc.. But will not be able to perform DML statements against the tables and views.  They can perform sp_helptext 'stored procedure' or 'sql view' statements to view the stored procedure or view definition. But will not be able to modify either one. Also, they will not be able to create tables, views, or stored procedures as well.

If you are looking to provide tighter control over how your data is accessed, give this a try.