Can I create a Policy that will enforce a standard?

Question

Can I create a Policy that will enforce a standard?

jason.stamm

Grasshopper

Points: 17
More actions
September 15, 2011 at 1:18 pm

#257532

We have a few databases where we do not want the developers to write queries that cross databases. I was thinking perhaps I could enforce this as a policy. Anyone have any experience with this?

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

I am not completely sure, but this link is great for learning about policy management: http://www.databasejournal.com/features/mssql/article.php/3739386/Policy-based-Management-in-SQL-Server-2008-150-Part-I.htm

Also, a suggestion is to not have each developer have their own login. Set up a login for each database specific for the application/database combination. Then, limit the permission of each user to the proper databases. i.e. Database Application1 has user named Application1_dev and this only has access to database Application1.

Thanks,

Jared

Jared
CE - Microsoft

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 2

jason.stamm (9/15/2011)
We have a few databases where we do not want the developers to write queries that cross databases. I was thinking perhaps I could enforce this as a policy. Anyone have any experience with this?

Perhaps a DDL trigger that looks for the 3 and 4 part naming conventions, splits it out, and checks the databases names.

As a bit of a sidebar, do your developers have the privs to promote their own code in production?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

You should be able to do that through Policy Based Mgmt (PBM). Look into that and test it out.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

jason.stamm Grasshopper Points: 17 More actions · Answer 4

Thanks for all the replies. Our developers DO NOT push code to PROD and we have an NT Group which the permissions are based on for each environment, so no worries there.

I know I can do this in a DDL trigger, but what is the difference between using a DDL trigger vs. Policy? I know if you have dbo permissions you can bypass the DDL trigger (disabling it), if you have dbo permission can you also bypass the policy?

SQLRNNR SSC Guru Points: 281344 More actions · Answer 5

You cannot bypass the policy with DBO permission.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

jason.stamm Grasshopper Points: 17 More actions · Answer 6

Not trying to be lazy here at all, but I am new to Policies and there is a lot of value in these for our standards enforcement. I have been trying to find examples that I can interrogate the SQL text in a procedure, but I cannot find anything. Has anyone come across an example? Maybe I need to nest this with another view or some combination to get the results I need.

Again, many thanks to all that replied!