Deny access to some AX tables from Sql Server Management Studio

Questions:

I’m setting up a new SQL Server  2005 instance which will host a Dynamics AX DB.

I needed to deny access to some AX tables to some users, so I set up the rights from within AX and everything is fine. Now I want to make sure that a user who can’t access tables from within AX can’t access them from, say, SQL Server Management Studio or another external tool. I know I need to assign or deny permissions to the tables to each user, or even to roles I’ll later bind to users, but the problem is that the work is huge (I have custom objects which bring the number of tables well over 1000).

Is there a standard/fast way to accomplish my goal, or do I have to think about it from scratch? I already have ideas, but I hoped a best practice already existed…Well, it doesn’t matter which AX "Security Model" is set. All "normal" users are assigned to the server- and database-role "public". Thus, if they are able to logon to the server/database e.g. with non-AXtools, they have all access-rights of this role.

Solution:

If you want to restrict access I could think about two options:

1) Modify the database role "public" by DENYING the access-right for those tables you want to restrict. For example:

USE [AX]
GO
DENY SELECT, INSERT, UPDATE, DELETE ON [dbo].[CRONUS$Item] TO [public]
GO

This would mean, if the user is accessing the db via AXthe "application role" ("$ndo$shadow" with "Standard", "$ndo$ar$…" with "Enhanced" Security Model); else the – modified – "database role" "public" would be used.

But actually it is not really recommended to fiddle with standard roles. So I would go for this option:

2) Create a new "database role" where you set up all table restrictions, e.g. "ax_restrictions":

USE [AX]
GO
CREATE ROLE [ax_restrictions] AUTHORIZATION [dbo]
GO
DENY SELECT, INSERT, UPDATE, DELETE ON [dbo].[CRONUS$Item] TO [ax_restrictions]
GO

 Assign this role to the relevant users, thus these users have the roles "public" and "ax_restrictions"!

USE [AX]
GO
EXEC sp_addrolemember N’ax_restrictions‘, N’UserXYZ’
GO

Have in mind that a DENY right will always overrule a REVOKE!

If you want to deny access to all tables from this database you don’t need to create your own db role, then you could simply assign the db role "db_denydatareader":

USE [AX]
GO
EXEC sp_addrolemember N’db_denydatareader‘, N’UserXYZ’
GO

 

Again, these roles are only effective if the user is accessing without AX – with AX always an "application role" will be used which overrules the "database roles".

Advertisements
This entry was posted in Dynamics AX and SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s