waldo’s blog

Microsoft Dynamics NAV

Deny object changes in SQL Server

Internally at iFacto, we have been working quite heavily on our infrastructure. And if I say “heavily” .. I mean unbefreakinglievable heavily. In a way: we threw everything out .. and we placed a lot in. But we only did this after going very deep in pre-defining about everything we could predefine: user security, SQL security, How to set up and maintain development databases, QA databases, Internal stuff, what do we back up, how much, .. . You know, the usual. And probably you also know that doing that as an IT partner, isn’t that easy to do: you have to foresee IT for yourself, and for your customer’s IT (at least - for its developents..), for products .. Releasemanagement, product management and sh<bleep>t … .


 

Anyway … it would take me too long to explain everything (although, I’m willing to expand on this if you want .. just drop me a comment). It’s just .. by doing this, we created a lot of scripts, tips and tricks .. which I’m going to share over the few months of blogging..


 

When we were thinking about how to deny the object changes for released databases, we were thinking about how it was usually done: write some kind of trigger on the object table, and go from there.. . Or what I also heard: make a database “Read Only” in SQL Server.

But there is a much cleaner solution for doing something like this. With setting simple SQL Server Permissions, you
can
“Deny”
object
changes
simply
by
setting
GRANTing
and
DENYing
the
right
permissions
on
the
Object
Table.

In our case, after setting up our new domain, we only grant permissions (all kinds of permissions) to User Groups (which is going to bite us in our *ss with NAV2013 (it seems) .. but anyway..). In this case, all users that have access to the database, are part of the windows group “RC-NAV-TECHNICAL”.

Only thing we have to do, is to execute this “script” to the database that we don’t want users to change objects:

GRANT CONTROL ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL]

GRANT SELECT ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL]

GRANT VIEW CHANGE TRACKING ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL]

GRANT VIEW DEFINITION ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL]

DENY ALTER ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL]

DENY DELETE ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL]

DENY INSERT ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL]

DENY REFERENCES ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL]

DENY TAKE OWNERSHIP ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL]

DENY UPDATE ON [dbo].[Object] TO [IFACTO\RC-NAV-TECHNICAL]

If you’re not that familiar with T-SQL, you can use your SQL Server Management Studio to set up the permissions. Just select your database, go to security, double click the user (or group), select securables, click “Search”, select “Specific Objects” and click OK. Graphically:

Next, select “Tables” from the Object Types, and try to find the Object-table:

Now, it’s all about DENY-ing and GRANT-ing the different permissions you have available for your Object Table. In my opinion, this is the right setup:

When you generate a script from this, you’ll see you get the same as I showed above.

So, what happens if I do try to change an object?

Well, as you would expect, you get this SQL Server Permission Error:

Same for insert or deleting an object.

I particularly like this way of denying permissions, not only because it makes most sense, but also: I’m still able to work with data in the database (post invoices and such - this won’t be possible in a “Read Only” database), and I don’t need complex triggers to figure out why something is denied or not.

Are there no buts?

Sure. You can only do this on the entire Object table. You have to keep that in mind when you’re implementing this. For example, users won’t be able to update a menu suite.

Probably most of you figured this already out. We actually never cared until our infrastructural change .. and I couldn’t find a decent solution online .. so I thought: “let’s share!” ;-).

Enjoy!

August 1st, 2012 Posted by waldo | Dynamics NAV | 3 comments

3 Comments »

  1. Very interesting post!
    But I guess this is only handy for big customers that has agreed to work in a “Release way”.

    Comment by Capone | August 2, 2012

  2. Why would that be?

    You can deny this per user. You Shouldn’t do this for users who should be able to change objects in the database.. .

    But in our case, we applied it to our Release databases indeed (internally), and for some people in our organisation on DEV databases of customers.

    Comment by waldo | August 2, 2012

  3. Of course you are totally right! Just me that lacked some caffeine :)

    Comment by Capone | August 2, 2012

Leave a comment

*
To prove you're a person (not a spam script), type the security text shown in the picture. Click here to regenerate some new text.
Click to hear an audio file of the anti-spam word