August 31, 2009 at 11:49 pm
Dear All,
My manager says that all the stored procedures can be converted into dotnet assembly.He's asking me to work on it, saying it's more secured as none will be ablt to see SPs in database.I just can't agree with this.
Can anyone tell me the advantages and disdvantages of this.? Has anyone tried the same.?
Thanks in advance.
September 1, 2009 at 2:25 am
Tell your manager he's an idiot.
Yes, it's possible, it is however in no way a good idea. CLR is an alternative to T-SQL for procedures, not a replacement. It's good in the areas T-SQL is weak (string manipulation, regex, possibly intensive mathematical calculations) and should not be used in areas T-SQL is strong (data access and manipulation)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2009 at 3:40 am
Thanks Gila.
I myself felt it's not a good idea.
Can you eplain in detail the plus and minus of it.
Or any link for the same.
So that I can explain it in detail to manager.
Once again thanks a lot.
September 1, 2009 at 4:12 am
As Gail stated, CLR is not a replacement for TSQL procedures, it is just an addition to help where TSQL isn't performing well or just cannot do it.
Regarding content visibility:
- Also your clr assemblies can be disassembled !
- you can just deny view of definition of any procedure !
DENY VIEW DEFINITION ON yoursproc TO ...
- and last but not least, you could create your sproce using "with encryption", just to make it a bit more difficult for anyone trying to have a look at your sproc.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 1, 2009 at 5:40 am
Thank you somuch.
September 2, 2009 at 5:42 am
I'm with Gail on her assessment of your boss's 'strategy'.
Firstly - just because you *can* do something, it does not follow that one should. It is possible to cross a busy road with your eyes shut - it's not a good idea though.
The following link contains several references to CLR sp's compared to T-SQL sp's - compiled by a very well respected source
Just as, well - actually much more, importantly; where on earth is the business case here? All your boss is doing is increasing risk to the business at great cost, to no discernable benefit. When things go wrong, through bugs or issues caused by degraded performance due to using an inappropriate tool - he's going to have to explain allocating significant resources to a project providing no business benefits, just doing the same thing (hopefully) a different way. And that's if things go really well.
It's the kind of decision often described as "Career Limiting".
Quote from http://www.joelonsoftware.com/articles/fog0000000069.html
"When you throw away code and start from scratch, you are throwing away all that knowledge. All those collected bug fixes. Years of programming work."
September 2, 2009 at 7:33 am
FWIW: concering the "dangers" of CLR modules ....
- http://www.sqlservercentral.com/Forums/FindPost457289.aspx
- http://www.sqlservercentral.com/Forums/Topic483423-386-1.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 3, 2009 at 1:27 am
Your boss obviously likes to waste company money and time!
If he's so concerned about people seeing the stored procedures in the database he should tell you to put your time and effort into database security.
It would be far more cost effective for you to sort the logons so users have only execute permissions on the stored procedures and these do everything including selects on tables/views. Then they can't see the tables, views, stored procs contents or anything that matters.
September 3, 2009 at 2:11 am
Sounds like your boss has read a magazine which recommends it, maybe you should ask where he got his information from, so you can disapprove it, based on his source.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
May 3, 2012 at 7:01 am
It is possible to write into assemblies for functions
in sql stored procedure you have to call dll file of c# for getting string in your required format which dll should be placed in GAC folder
as per example
you want to format string into seperating charecters of[,%,&] and insert in to temptable there you compare with sql query but it's not easy for maintainence.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply