Easy way to encrypt

Is there a way to encrypt all of my custom stored procedures by issuing a
command from the query designer? Something like sp_ Encrypt
AllStoredPrecoduresNow? Or do I have to go one by one and recompile them
with the "WITH ENCRYPTION" option? I have too many and this will take
forever.
Thanks.Rene,
To my knowledge, you will have to perform an ALTER PROC statement for each
stored procedure to add the WITH ENCRYPTION clause.
Prior to doing this understand that SQL Server does not provide a way to
unencrypt encrypted objects such as stored procedures. All DDL should be
retained in a secure location. Also, encrypted objects cannot be scripted
out which can cause issues with other functionalites like replication.
HTH
Jerry
"Rene" <nospam@.nospam.com> wrote in message
news:eXHrYCxuFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Is there a way to encrypt all of my custom stored procedures by issuing a
> command from the query designer? Something like sp_ Encrypt
> AllStoredPrecoduresNow? Or do I have to go one by one and recompile them
> with the "WITH ENCRYPTION" option? I have too many and this will take
> forever.
> Thanks.
>
>|||What a pain in the butt!! To make things worst, it looks like the SQL Server
encryption can be easily decrypted!! What a bummer.
Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OJ359GxuFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Rene,
> To my knowledge, you will have to perform an ALTER PROC statement for each
> stored procedure to add the WITH ENCRYPTION clause.
> Prior to doing this understand that SQL Server does not provide a way to
> unencrypt encrypted objects such as stored procedures. All DDL should be
> retained in a secure location. Also, encrypted objects cannot be scripted
> out which can cause issues with other functionalites like replication.
> HTH
> Jerry
> "Rene" <nospam@.nospam.com> wrote in message
> news:eXHrYCxuFHA.2008@.TK2MSFTNGP10.phx.gbl...
>> Is there a way to encrypt all of my custom stored procedures by issuing a
>> command from the query designer? Something like sp_ Encrypt
>> AllStoredPrecoduresNow? Or do I have to go one by one and recompile them
>> with the "WITH ENCRYPTION" option? I have too many and this will take
>> forever.
>> Thanks.
>>
>|||Hi Rene,
Yes, I understood it would not be an easy job ALTER all the stored
procedures one by one, however this is the only method available now.
Admittedly, encrypted stored procedures could be decrypted by some third
party applications and we do not have better solution on this side. I
believe we should use other method to ensure the security of SQL Server.
Here are some articles about SQL Server security for your reference.
Injection Protection
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag04/
html/InjectionProtection.asp
A Security Roadmap
http://www.sql-server-performance.com/sql_server_security_distilled_chap1_ex
cept.asp
Overview of the SQL Server Security Model and Security Best Practices
http://www.sql-server-performance.com/vk_sql_security.asp
Chapter 18 - Securing Your Database Server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/ht
ml/THCMCh18.asp
Hope this helps.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

Easy way to encrypt

Is there a way to encrypt all of my custom stored procedures by issuing a
command from the query designer? Something like sp_ Encrypt
AllStoredPrecoduresNow? Or do I have to go one by one and recompile them
with the "WITH ENCRYPTION" option? I have too many and this will take
forever.
Thanks.
Rene,
To my knowledge, you will have to perform an ALTER PROC statement for each
stored procedure to add the WITH ENCRYPTION clause.
Prior to doing this understand that SQL Server does not provide a way to
unencrypt encrypted objects such as stored procedures. All DDL should be
retained in a secure location. Also, encrypted objects cannot be scripted
out which can cause issues with other functionalites like replication.
HTH
Jerry
"Rene" <nospam@.nospam.com> wrote in message
news:eXHrYCxuFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Is there a way to encrypt all of my custom stored procedures by issuing a
> command from the query designer? Something like sp_ Encrypt
> AllStoredPrecoduresNow? Or do I have to go one by one and recompile them
> with the "WITH ENCRYPTION" option? I have too many and this will take
> forever.
> Thanks.
>
>
|||What a pain in the butt!! To make things worst, it looks like the SQL Server
encryption can be easily decrypted!! What a bummer.
Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OJ359GxuFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Rene,
> To my knowledge, you will have to perform an ALTER PROC statement for each
> stored procedure to add the WITH ENCRYPTION clause.
> Prior to doing this understand that SQL Server does not provide a way to
> unencrypt encrypted objects such as stored procedures. All DDL should be
> retained in a secure location. Also, encrypted objects cannot be scripted
> out which can cause issues with other functionalites like replication.
> HTH
> Jerry
> "Rene" <nospam@.nospam.com> wrote in message
> news:eXHrYCxuFHA.2008@.TK2MSFTNGP10.phx.gbl...
>
|||Hi Rene,
Yes, I understood it would not be an easy job ALTER all the stored
procedures one by one, however this is the only method available now.
Admittedly, encrypted stored procedures could be decrypted by some third
party applications and we do not have better solution on this side. I
believe we should use other method to ensure the security of SQL Server.
Here are some articles about SQL Server security for your reference.
Injection Protection
http://msdn.microsoft.com/library/de...us/dnsqlmag04/
html/InjectionProtection.asp
A Security Roadmap
http://www.sql-server-performance.co...illed_chap1_ex
cept.asp
Overview of the SQL Server Security Model and Security Best Practices
http://www.sql-server-performance.co...l_security.asp
Chapter 18 - Securing Your Database Server
http://msdn.microsoft.com/library/de...us/dnnetsec/ht
ml/THCMCh18.asp
Hope this helps.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

Easy way to encrypt

Is there a way to encrypt all of my custom stored procedures by issuing a
command from the query designer? Something like sp_ Encrypt
AllStoredPrecoduresNow? Or do I have to go one by one and recompile them
with the "WITH ENCRYPTION" option? I have too many and this will take
forever.
Thanks.Rene,
To my knowledge, you will have to perform an ALTER PROC statement for each
stored procedure to add the WITH ENCRYPTION clause.
Prior to doing this understand that SQL Server does not provide a way to
unencrypt encrypted objects such as stored procedures. All DDL should be
retained in a secure location. Also, encrypted objects cannot be scripted
out which can cause issues with other functionalites like replication.
HTH
Jerry
"Rene" <nospam@.nospam.com> wrote in message
news:eXHrYCxuFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Is there a way to encrypt all of my custom stored procedures by issuing a
> command from the query designer? Something like sp_ Encrypt
> AllStoredPrecoduresNow? Or do I have to go one by one and recompile them
> with the "WITH ENCRYPTION" option? I have too many and this will take
> forever.
> Thanks.
>
>|||What a pain in the butt!! To make things worst, it looks like the SQL Server
encryption can be easily decrypted!! What a bummer.
Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OJ359GxuFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Rene,
> To my knowledge, you will have to perform an ALTER PROC statement for each
> stored procedure to add the WITH ENCRYPTION clause.
> Prior to doing this understand that SQL Server does not provide a way to
> unencrypt encrypted objects such as stored procedures. All DDL should be
> retained in a secure location. Also, encrypted objects cannot be scripted
> out which can cause issues with other functionalites like replication.
> HTH
> Jerry
> "Rene" <nospam@.nospam.com> wrote in message
> news:eXHrYCxuFHA.2008@.TK2MSFTNGP10.phx.gbl...
>|||Hi Rene,
Yes, I understood it would not be an easy job ALTER all the stored
procedures one by one, however this is the only method available now.
Admittedly, encrypted stored procedures could be decrypted by some third
party applications and we do not have better solution on this side. I
believe we should use other method to ensure the security of SQL Server.
Here are some articles about SQL Server security for your reference.
Injection Protection
http://msdn.microsoft.com/library/d...-us/dnsqlmag04/
html/InjectionProtection.asp
A Security Roadmap
http://www.sql-server-performance.c...tilled_chap1_ex
cept.asp
Overview of the SQL Server Security Model and Security Best Practices
http://www.sql-server-performance.c...ql_security.asp
Chapter 18 - Securing Your Database Server
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/THCMCh18.asp
Hope this helps.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

easy (hopefully) matrix question

I'm working with a matrix in the designer and noticed I need to add a new row in between two existing rows. When I right click and choose add row a row is added at the very bottom of the matrix. What's the easiest way to add a matrix row to the middle of a matrix, one that doesn't include editing the rdl directly.

thanks

I had this exact problem!

What I found out is that if you carefully (yes, carefully) drag your field between 2 existing data fields, there's a thin white bracket line that shows up on the outside of the nearest existing textbox. This is to alert you where the field will be placed. It will either be on the left, right, top or bottom. If none of these areas are white then keep looking because otherwise you'll replace the destination cells - (bad).

|||Thanks for the response! I can insert a row below the first row but every other row is missing that white line that indicates I can insert.

Can you insert between every row or just between the first and second rows?|||You should be able to do it anywhere.. try moving the mouse around more.
The only time I see that you don't get the white bar is when you don't even have an existing data field in the nearest textbox.|||
You're right. After expanding the box and some very careful mouse work I got it to insert. Hopefully the MSFT guys will make this very basic function easier to use. Thanks again for the help.

Dynamic SQL queries

Hi,
I am trying to use dynamic queries in RDL from the data tab in the report
designer. The columns to be selected and the name of the table from which to
select are supplied as report parameters. Can someone give me an example of
how to do this without using stored procedures or UDFs? Is it possible at
all? I am using the reporting services from MS SQL 2000.
Thanks in advance,
PavanI've done this a few times, but then I've used stored procedures or UDF. But
you should be able to do something similar without stored procedures. I've
done something like this with MDX, should work out with SQL queries as well.
I'd try something like this:
4 Parameters - param1, param2, param3 and param4, paramTable
Query:
="Select " & Parameters!param1.Value & " as P1, " & Parameters!param2.Value
& " as P2, " & Parameters!param3.Value & " as P3, " &
Parameters!param4.Value & " as P4 from " & Parameters!paramTable.Value& "
where 1=1"
(Everything needs to be on the "same" line, no line breaks. Test in NotePad
without wordwrap to see that everything is on the same line, even if the
lines break in the editor.)
This should give you a dataset with fields P1, P2, P3 and P4, which you now
can use in your report.
You will need to be sure that all the options are available in all tables.
Or you could do cascading parameters where you choose the table first, and
then the drop down list of all parameters gets filled with valid choices.
Kaisa M. Lindahl Lervik
"Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I am trying to use dynamic queries in RDL from the data tab in the report
> designer. The columns to be selected and the name of the table from which
> to select are supplied as report parameters. Can someone give me an
> example of how to do this without using stored procedures or UDFs? Is it
> possible at all? I am using the reporting services from MS SQL 2000.
> Thanks in advance,
> Pavan
>|||One additional point. First have regular SQL so you get your field list.
Then change it as below. Once it is an expression the field list will not
update.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
news:%23LaW$xE6GHA.2120@.TK2MSFTNGP03.phx.gbl...
> I've done this a few times, but then I've used stored procedures or UDF.
> But you should be able to do something similar without stored procedures.
> I've done something like this with MDX, should work out with SQL queries
> as well.
> I'd try something like this:
> 4 Parameters - param1, param2, param3 and param4, paramTable
> Query:
> ="Select " & Parameters!param1.Value & " as P1, " &
> Parameters!param2.Value & " as P2, " & Parameters!param3.Value & " as P3,
> " & Parameters!param4.Value & " as P4 from " &
> Parameters!paramTable.Value& " where 1=1"
> (Everything needs to be on the "same" line, no line breaks. Test in
> NotePad without wordwrap to see that everything is on the same line, even
> if the lines break in the editor.)
> This should give you a dataset with fields P1, P2, P3 and P4, which you
> now can use in your report.
> You will need to be sure that all the options are available in all tables.
> Or you could do cascading parameters where you choose the table first, and
> then the drop down list of all parameters gets filled with valid choices.
> Kaisa M. Lindahl Lervik
> "Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
> news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I am trying to use dynamic queries in RDL from the data tab in the report
>> designer. The columns to be selected and the name of the table from which
>> to select are supplied as report parameters. Can someone give me an
>> example of how to do this without using stored procedures or UDFs? Is it
>> possible at all? I am using the reporting services from MS SQL 2000.
>> Thanks in advance,
>> Pavan
>|||Bruce,
I am trying to follow the BOL tutorial on dynamic queries. The expression
that they have will not fit as a one-liner in the generic query designer? Do
you have a work-around so that i can preview the result? Expression below:
="SELECT c.firstname, c.lastname, e.title, d.departmentID " &"From
HumanResources.EmployeeDepartmentHistory D " &"INNER JOIN
HumanResources.Employee E " & "ON D.EmployeeID = E.EmployeeID " & "INNER JOIN
Person.Contact C " &"ON E.ContactID = C.ContactID " &
Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " &
Parameters!Department.Value) & "ORDER BY C.LastName"
Thanks!
"Bruce L-C [MVP]" wrote:
> One additional point. First have regular SQL so you get your field list.
> Then change it as below. Once it is an expression the field list will not
> update.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
> news:%23LaW$xE6GHA.2120@.TK2MSFTNGP03.phx.gbl...
> > I've done this a few times, but then I've used stored procedures or UDF.
> > But you should be able to do something similar without stored procedures.
> > I've done something like this with MDX, should work out with SQL queries
> > as well.
> >
> > I'd try something like this:
> >
> > 4 Parameters - param1, param2, param3 and param4, paramTable
> >
> > Query:
> > ="Select " & Parameters!param1.Value & " as P1, " &
> > Parameters!param2.Value & " as P2, " & Parameters!param3.Value & " as P3,
> > " & Parameters!param4.Value & " as P4 from " &
> > Parameters!paramTable.Value& " where 1=1"
> >
> > (Everything needs to be on the "same" line, no line breaks. Test in
> > NotePad without wordwrap to see that everything is on the same line, even
> > if the lines break in the editor.)
> > This should give you a dataset with fields P1, P2, P3 and P4, which you
> > now can use in your report.
> > You will need to be sure that all the options are available in all tables.
> > Or you could do cascading parameters where you choose the table first, and
> > then the drop down list of all parameters gets filled with valid choices.
> >
> > Kaisa M. Lindahl Lervik
> >
> > "Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
> > news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
> >> Hi,
> >>
> >> I am trying to use dynamic queries in RDL from the data tab in the report
> >> designer. The columns to be selected and the name of the table from which
> >> to select are supplied as report parameters. Can someone give me an
> >> example of how to do this without using stored procedures or UDFs? Is it
> >> possible at all? I am using the reporting services from MS SQL 2000.
> >>
> >> Thanks in advance,
> >> Pavan
> >>
> >
> >
>
>|||Have a report with nothing but your parameters and a textbox. Set your
textbox to the expression so you can see the results.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:42F27824-479E-4B10-A232-083F860313EA@.microsoft.com...
> Bruce,
> I am trying to follow the BOL tutorial on dynamic queries. The expression
> that they have will not fit as a one-liner in the generic query designer?
> Do
> you have a work-around so that i can preview the result? Expression below:
> ="SELECT c.firstname, c.lastname, e.title, d.departmentID " &"From
> HumanResources.EmployeeDepartmentHistory D " &"INNER JOIN
> HumanResources.Employee E " & "ON D.EmployeeID = E.EmployeeID " & "INNER
> JOIN
> Person.Contact C " &"ON E.ContactID = C.ContactID " &
> Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " &
> Parameters!Department.Value) & "ORDER BY C.LastName"
> Thanks!
>
> "Bruce L-C [MVP]" wrote:
>> One additional point. First have regular SQL so you get your field list.
>> Then change it as below. Once it is an expression the field list will not
>> update.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> wrote in message
>> news:%23LaW$xE6GHA.2120@.TK2MSFTNGP03.phx.gbl...
>> > I've done this a few times, but then I've used stored procedures or
>> > UDF.
>> > But you should be able to do something similar without stored
>> > procedures.
>> > I've done something like this with MDX, should work out with SQL
>> > queries
>> > as well.
>> >
>> > I'd try something like this:
>> >
>> > 4 Parameters - param1, param2, param3 and param4, paramTable
>> >
>> > Query:
>> > ="Select " & Parameters!param1.Value & " as P1, " &
>> > Parameters!param2.Value & " as P2, " & Parameters!param3.Value & " as
>> > P3,
>> > " & Parameters!param4.Value & " as P4 from " &
>> > Parameters!paramTable.Value& " where 1=1"
>> >
>> > (Everything needs to be on the "same" line, no line breaks. Test in
>> > NotePad without wordwrap to see that everything is on the same line,
>> > even
>> > if the lines break in the editor.)
>> > This should give you a dataset with fields P1, P2, P3 and P4, which you
>> > now can use in your report.
>> > You will need to be sure that all the options are available in all
>> > tables.
>> > Or you could do cascading parameters where you choose the table first,
>> > and
>> > then the drop down list of all parameters gets filled with valid
>> > choices.
>> >
>> > Kaisa M. Lindahl Lervik
>> >
>> > "Pavan Edara" <epavan_vrce@.hotmail.com> wrote in message
>> > news:%23ZbqDyD6GHA.4064@.TK2MSFTNGP03.phx.gbl...
>> >> Hi,
>> >>
>> >> I am trying to use dynamic queries in RDL from the data tab in the
>> >> report
>> >> designer. The columns to be selected and the name of the table from
>> >> which
>> >> to select are supplied as report parameters. Can someone give me an
>> >> example of how to do this without using stored procedures or UDFs? Is
>> >> it
>> >> possible at all? I am using the reporting services from MS SQL 2000.
>> >>
>> >> Thanks in advance,
>> >> Pavan
>> >>
>> >
>> >
>>