Is using Enterprise Manage across the internet secure?

If I am using enterprise manager across the internet to access an SQL Server
, is it secure? Can I make database changes, passwords changes and view con
fidential table data securely. Or do I need to use another mechanism.Enterprise Manager is just an application, it sounds like you are worried
about the data transferred over the wire. If so, you should look into SSL
encryption.
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server http://support.microsoft.com/?id=276553
316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft...
http://support.microsoft.com/?id=316898
324777 WebCast: Microsoft SQL Server 2000: How to Configure SSL Encryption
http://support.microsoft.com/?id=324777
318605 INF: How SQL Server Uses a Certificate When the Force Protocol
http://support.microsoft.com/?id=318605
325757 INF: Using SQL Server 2000 with FIPS 140-1 Ciphers
http://support.microsoft.com/?id=325757
302409 FIX: Unable to Connect to SQL Server 2000 When Certificate
Authority... http://support.microsoft.com/?id=302409
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Is this method possible?

Hi, I am just wondering is it possible to use report parameter values to create a view for the report?
For example I have 5 databases in SQL Server 2005: db1,db2,db3,db4 and db5. Each of the databases has the same table format but storing different datas. I need to create a report that will take in two parameters: parameter START and parameter END, lets say START=db1 and END=db4, then from these values it will create a View that contains all information from db1 up to db 5 and finally the report will be generated base on the values in the View table.
Or if anyone know of a better way to overcome this method, do let me know.
Thanks.
Hi,
I am just wondering if this question is not clear enough, there is just no answer to this particular question, or this question is being posted at the wrong section of the forum?
Please do reply something so that I know what to do.
Thanks.
|||You will need to create a stored proc that will form your desired data and output that to reporting services as a result set.|||Hi Joseph, thanks for replying. I know this method of yours will work if I only have to query from one database, but if my result set comes from a combination of a few databases, how can I create one general stored proc to do that? Thanks.
|||if the databases is on the same server, you can use the convention <database name>.dbo.<table name> for your query tables. If it's on another server, you might need to employ linked server functionalities of sql server.|||

I managed to come out with a stored proc to use the convention <database name>.dbo.<table name>. Below is the sample I used:
databases: db1, db2, db3
all has same table and column information as below:
table name: tabledb
column: data
the detail information for each of the databases are as follow:
db1: data values are 1,2,3
db2: data values are 4,5,6
db3: data values are 7,8,9
An example of the values I will have in the result set is 1,2,3,4,5,6,7,8,9 if I have parameters @.from = db1 and @.to = db3.
Below is the stored proc for it:

CREATE PROCEDURE [dbo].[GrabInfo]
@.from int, --1 as db1, 2 as db2, ...
@.to int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @.sql nvarchar(MAX)
DECLARE @.temp nvarchar(17)
SET @.temp='db'+CAST(@.from AS nvarchar(1))+'.dbo.tabledb'
SELECT @.sql = 'SELECT data FROM '+@.temp
WHILE (@.from < @.to)
BEGIN
SET @.from = @.from + 1
SET @.temp = 'db' + CAST(@.from AS nvarchar(1)) + '.dbo.tabledb'
SELECT @.sql = @.sql + ' UNION SELECT data FROM '+@.temp
END
EXEC(@.sql)
RETURN
END

One more question I have, can it be done with View instead of Stored Proc?

Is this Index supposed to make view faster?

Ok,
When I did this to add the index on the view, the view is no faster at
all...Did I do something wrong? :
USE tsNess
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENTIF
IER,ANSI_NULLS
ON
GO
CREATE VIEW V1
WITH SCHEMABINDING
AS
SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type,
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
FROM dbo.tblTravelDetail t1 INNER JOIN
dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
= t2.TravelDetailId INNER JOIN
dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
= t4.TravelDetailId INNER JOIN
dbo.tblTravelEvent t5 ON t1.TravelEventId =
t5.TravelEventId INNER JOIN
dbo.amount_type t6 ON t2.amountTypeId =
t6.amount_type_id INNER JOIN
dbo.period t8 ON t1.PeriodID = t8.period_id
WHERE (t1.MemberId = '222') AND (t2.amount <> 0)
GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type
Thanks,
TrintHi
select <column lists> from V1 with (noexpand)
See an execution plan for the query
"trint" <trinity.smith@.gmail.com> wrote in message
news:1123505778.551931.242730@.o13g2000cwo.googlegroups.com...
> Ok,
> When I did this to add the index on the view, the view is no faster at
> all...Did I do something wrong? :
> USE tsNess
> GO
> SET NUMERIC_ROUNDABORT OFF
> GO
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENT
IFIER,ANSI_NULLS
> ON
> GO
> CREATE VIEW V1
> WITH SCHEMABINDING
> AS
> SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type,
> SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
> WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
> SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
> WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
> SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
> WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
> SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
> WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
> SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
> WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
> SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
> WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
> SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
> WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
> SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
> WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
> FROM dbo.tblTravelDetail t1 INNER JOIN
> dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
> = t2.TravelDetailId INNER JOIN
> dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
> = t4.TravelDetailId INNER JOIN
> dbo.tblTravelEvent t5 ON t1.TravelEventId =
> t5.TravelEventId INNER JOIN
> dbo.amount_type t6 ON t2.amountTypeId =
> t6.amount_type_id INNER JOIN
> dbo.period t8 ON t1.PeriodID = t8.period_id
> WHERE (t1.MemberId = '222') AND (t2.amount <> 0)
> GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type
> Thanks,
> Trint
>|||Did you create indexes on the view?
Once you create an indexed view, make sure your view does not use the base
table indexes.
The way to do that is use the option (noexpand)
eg:-
Select PeriodID from V1 (noexpand)
where ...
To get the adv of indexed view, you should make sure its using the indexes
on the view rather than the old base table indexes.
The next thing is see the reads/Cpu and Dur with and without the option
noexpand. Moreover dont leave out exe plan.
Before you go for indexed view it wud be good to see how much time it would
take to create an index on a prod server.
The retireval performance should not be an overhead while saving
or in other words see whether the tables used in your indexed view are
updated/inserted frequently. If so I dont think its a good idea to go for
indexed view.
Thanks,
Prad
"trint" <trinity.smith@.gmail.com> wrote in message
news:1123505778.551931.242730@.o13g2000cwo.googlegroups.com...
> Ok,
> When I did this to add the index on the view, the view is no faster at
> all...Did I do something wrong? :
> USE tsNess
> GO
> SET NUMERIC_ROUNDABORT OFF
> GO
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENT
IFIER,ANSI_NULLS
> ON
> GO
> CREATE VIEW V1
> WITH SCHEMABINDING
> AS
> SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type,
> SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
> WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
> SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
> WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
> SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
> WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
> SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
> WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
> SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
> WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
> SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
> WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
> SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
> WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
> SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
> WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
> FROM dbo.tblTravelDetail t1 INNER JOIN
> dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
> = t2.TravelDetailId INNER JOIN
> dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
> = t4.TravelDetailId INNER JOIN
> dbo.tblTravelEvent t5 ON t1.TravelEventId =
> t5.TravelEventId INNER JOIN
> dbo.amount_type t6 ON t2.amountTypeId =
> t6.amount_type_id INNER JOIN
> dbo.period t8 ON t1.PeriodID = t8.period_id
> WHERE (t1.MemberId = '222') AND (t2.amount <> 0)
> GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type
> Thanks,
> Trint
>|||Ok,
Uri and Pradeep, I get this error when trying to create an index on
view one or V1:
An index cannot be created on the view 'V1' because the view definition
includes an unknown value (the sum of a nullable expression).
Thanks,
Trint|||Trint,
Another reason why we dont go for indexed view always...
See BOL you have a lot of requirements to be satisfied in creating one apart
from the time it takes and the ovehead while saving...
One more thing that I have obsereved is the fields on which u intend to
create index should not have duplicates...
You cannot have :
a.. A derived table.
a.. Rowset functions.
a.. UNION operator.
a.. Subqueries.
a.. Outer or self joins.
a.. TOP clause.
a.. ORDER BY clause.
a.. DISTINCT keyword.
a.. COUNT(*) but (COUNT_BIG(*) is allowed.)
a.. A SUM function that references a nullable expression.
a.. The full-text predicates CONTAINS or FREETEXT.
a.. COMPUTE or COMPUTE BY clause.
a.. If GROUP BY is not specified, the view select list cannot contain
aggregate expressions.
a.. If GROUP BY is specified, the view select list must contain a
COUNT_BIG(*) expression, and the view definition cannot specify HAVING,
CUBE, or ROLLUP.
a.. A column resulting from an expression that either evaluates to a float
value or uses float expressions for its evaluation cannot be a key of an
index in an indexed view or a table.
From BOL
Thanks,
Pradeep Kutty
"trint" <trinity.smith@.gmail.com> wrote in message
news:1123514588.376478.113330@.g43g2000cwa.googlegroups.com...
> Ok,
> Uri and Pradeep, I get this error when trying to create an index on
> view one or V1:
> An index cannot be created on the view 'V1' because the view definition
> includes an unknown value (the sum of a nullable expression).
> Thanks,
> Trint
>|||Trint,
To start off, I would like to ask if you have already indexed the base
tables? Indexing a view is not the place to start. Under normal
conditions, view performance is just fine if you properly index the base
tables.
For the rest of the reply, I will assume you have a properly normalized
data model that is properly indexed.
If you must index the view, then make sure sure:
- dbo.tblTravelDetailAmount.amount is defined as NOT NULL;
- the expressions never evaluate to NULL. Currently your CASE
expressions will evaluate to NULL for each amountTypeID that is not
explicitely mentioned in the expression. You could add "ELSE 0" to each
CASE expression to solve that;
- in the case of dbo.tblTravelDetail.MemberId that you match the
literal's data type to the column's. For example, if the MemberId is
defined as int, then change the predicate to WHERE t1.MemberId = 222;
- you add COUNT_BIG(*) to the selection list.
Maybe then you can index the view.
Note that if only few rows in table dbo.tblTravelDetailAmount have
amount=0, then the predicate WHERE t2.amount<>0 may not help performance
(it may even hurt performance) if you select from the base tables.
HTH,
Gert-Jan
trint wrote:
> Ok,
> When I did this to add the index on the view, the view is no faster at
> all...Did I do something wrong? :
> USE tsNess
> GO
> SET NUMERIC_ROUNDABORT OFF
> GO
> SET
> ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENT
IFIER,ANSI_NULLS
> ON
> GO
> CREATE VIEW V1
> WITH SCHEMABINDING
> AS
> SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type,
> SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
> WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
> SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
> WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
> SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
> WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
> SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
> WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
> SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
> WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
> SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
> WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
> SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
> WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
> SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
> WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
> FROM dbo.tblTravelDetail t1 INNER JOIN
> dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
> = t2.TravelDetailId INNER JOIN
> dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
> = t4.TravelDetailId INNER JOIN
> dbo.tblTravelEvent t5 ON t1.TravelEventId =
> t5.TravelEventId INNER JOIN
> dbo.amount_type t6 ON t2.amountTypeId =
> t6.amount_type_id INNER JOIN
> dbo.period t8 ON t1.PeriodID = t8.period_id
> WHERE (t1.MemberId = '222') AND (t2.amount <> 0)
> GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
> t6.amount_type
> Thanks,
> Trint

Is this an installation problem??

I am using the Server Installation strategy, the install is complete (I
think), I have deployed my reports but when I try to view any report I am
getting this error. I have already installed the Oracle Client software
(9.2), and the data source is deployed. Any else get this error before?
HELP!!
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'entpar1- SCARDBA'.
(rsErrorOpeningConnection) Get Online Help
System.Data.OracleClient requires Oracle client software version 8.1.7 or
greater.Did you get a solution? We're having the same problem since we've installed
the oracle db on the reporting services server.
Regards
Antoon
"OriginalStealth" wrote:
> I am using the Server Installation strategy, the install is complete (I
> think), I have deployed my reports but when I try to view any report I am
> getting this error. I have already installed the Oracle Client software
> (9.2), and the data source is deployed. Any else get this error before?
> HELP!!
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'entpar1- SCARDBA'.
> (rsErrorOpeningConnection) Get Online Help
> System.Data.OracleClient requires Oracle client software version 8.1.7 or
> greater.
>

IS this a Bug?

before reporting to MS -Connect , i just want clarify

I have a table

Create table Permssions(

GroupPermissionID int ,

List smallint,

[View] smallint,

Manage smallint)

i opened this table in Management studio and entered 1 in all columns when i try to do save and exit

"No row was updated

the data in row 1 was not committed.

Error Source : .Net SqlClient Dataprovider

Error Message : Conversion failed when converting the varchar value 'List' to datatype int

Correct the error and retry or press escape to cancel the changes "

basically the problem was with the List Column name.

Madhu

Hi Madhu,

yes I think so - it is problem with the column name 'List'; but if you run sql the data will be inserte.

CU

tosc

|||

yes i did try that... but Management studio has problem with it

Madhu

|||

yes, the management studio has a problem -> .Net SqlClient Dataprovider

tosc

Is this a bug in IsNull?

I think I uncovered a bug in IsNull. Could someone check to see If I am
missing something here?
My view has the following code in it.
SELECT
v.QuoteId,
v.QuoteLineID,
pmc.BadLine AS BadLine1,
pic.BadLine AS BadLine2,
IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage,
(CASE
WHEN pmc.BadLine IS NULL
THEN IsNull(pmc.BadLine, 0)
ELSE pmc.BadLine
END) +
(CASE
WHEN pic.BadLine IS NULL
THEN IsNull(pic.BadLine, 0)
ELSE pic.BadLine
END) InvalidPackage2
FROM QuoteLineValues v
LEFT JOIN PackageInvalidComponents pic
ON v.QuoteID = pic.QuoteID
AND v.QuoteLineID = pic.QuoteLineID
LEFT JOIN PackageMissingComponents pmc
ON v.QuoteID = pmc.QuoteID
AND v.QuoteLineID = pmc.QuoteLineID
When I select from this view I get
BadLine1 = NULL
BadLine2 = NULL
InvalidPackage = 2
InvalidPackage2 = 0
InvalidPackage should have the same exact value as InvalidPackage2. But it
doesn't. Upon investigation I have found that IsNull(pmc.BadLine, 0)
evalutate to 1 when pmc.BadLine by itself is NULL.
Why does IsNull(pmc.BadLine, 0) return 1 when it should return 0? Or am I
missing something?
JeremySELECT
> v.QuoteId,
> v.QuoteLineID,
> pmc.BadLine AS BadLine1,
> pic.BadLine AS BadLine2,
> IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage,
> (CASE
> WHEN pmc.BadLine IS NULL
--Why not Then 0 if you checked if it is null it is really null there is no
need to check it again ?
> THEN IsNull(pmc.BadLine, 0)
> ELSE pmc.BadLine
> END) +
> (CASE
> WHEN pic.BadLine IS NULL
--Why not Then 0 if you checked if it is null it is really null there is no
need to check it again ?
> THEN IsNull(pic.BadLine, 0)
> ELSE pic.BadLine
> END) InvalidPackage2
Jens.
"Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> schrieb im
Newsbeitrag news:9AFA4D52-7BAC-4583-973F-5CD9386B052B@.microsoft.com...
>I think I uncovered a bug in IsNull. Could someone check to see If I am
> missing something here?
> My view has the following code in it.
> SELECT
> v.QuoteId,
> v.QuoteLineID,
> pmc.BadLine AS BadLine1,
> pic.BadLine AS BadLine2,
> IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage,
> (CASE
> WHEN pmc.BadLine IS NULL
> THEN IsNull(pmc.BadLine, 0)
> ELSE pmc.BadLine
> END) +
> (CASE
> WHEN pic.BadLine IS NULL
> THEN IsNull(pic.BadLine, 0)
> ELSE pic.BadLine
> END) InvalidPackage2
> FROM QuoteLineValues v
> LEFT JOIN PackageInvalidComponents pic
> ON v.QuoteID = pic.QuoteID
> AND v.QuoteLineID = pic.QuoteLineID
> LEFT JOIN PackageMissingComponents pmc
> ON v.QuoteID = pmc.QuoteID
> AND v.QuoteLineID = pmc.QuoteLineID
> When I select from this view I get
> BadLine1 = NULL
> BadLine2 = NULL
> InvalidPackage = 2
> InvalidPackage2 = 0
> InvalidPackage should have the same exact value as InvalidPackage2. But it
> doesn't. Upon investigation I have found that IsNull(pmc.BadLine, 0)
> evalutate to 1 when pmc.BadLine by itself is NULL.
> Why does IsNull(pmc.BadLine, 0) return 1 when it should return 0? Or am I
> missing something?
> Jeremy|||Jens,
You are right in saying that there is no reason to check it again since I
already checked for null in the case statement. I was trying to exagerate th
e
absurdity of the values that IsNull returns. e.g. Inside a case statement
IsNull(pic.BadLine, 0) return a 0 as expected. Outside the case statement
IsNull(pic.BadLine, 0) returns 1.
Does that make any sense?
Jeremy
"Jens Sü?meyer" wrote:

> SELECT
> --Why not Then 0 if you checked if it is null it is really null there is n
o
> need to check it again ?
> --Why not Then 0 if you checked if it is null it is really null there is n
o
> need to check it again ?
> Jens.
> "Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:9AFA4D52-7BAC-4583-973F-5CD9386B052B@.microsoft.com...
>
>|||Try COALESCE().
"Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> wrote in message
news:EC91E50A-6D5F-4F67-9E96-7C37787B51A9@.microsoft.com...
> Jens,
> You are right in saying that there is no reason to check it again since I
> already checked for null in the case statement. I was trying to exagerate
> the
> absurdity of the values that IsNull returns. e.g. Inside a case statement
> IsNull(pic.BadLine, 0) return a 0 as expected. Outside the case statement
> IsNull(pic.BadLine, 0) returns 1.
> Does that make any sense?
> Jeremy
> "Jens Smeyer" wrote:
>|||> IsNull(pic.BadLine, 0) return a 0 as expected. Outside the case statement
> IsNull(pic.BadLine, 0) returns 1.
> Does that make any sense?
Unless pic.BadLine = 1 then not possible. How can we reproduce this in our
computer?
What data type is pic.BadLine?
AMB
"Jeremy Lubich" wrote:
> Jens,
> You are right in saying that there is no reason to check it again since I
> already checked for null in the case statement. I was trying to exagerate
the
> absurdity of the values that IsNull returns. e.g. Inside a case statement
> IsNull(pic.BadLine, 0) return a 0 as expected. Outside the case statement
> IsNull(pic.BadLine, 0) returns 1.
> Does that make any sense?
> Jeremy
> "Jens Sü?meyer" wrote:
>|||Mike, et al.,
I tried COALESCE and got an interesting result. Here is what is returned by
QA.
pic.BadLine = NULL
IsNull(pic.BadLine, 0) = 1
COALEASCE(pic.BadLine, 0) = 0
Your workaround works. But, I am still stuck with the fact that IsNull looks
like it has a bug. I am worried about other parts of our database where we
also use IsNull.
I would think both of these values would return 0. Does it make any sense
why the IsNull and COALEASCE funtions do not return the same value when the
first argument is NULL?
If someone replies to this thread and says, "yup, that looks like a bug to
me", then I'll contact Microsoft to have it investigated. I just didn't want
to be presumptious in my hypothesis before I threw this out into the
community discussion.
Jeremy
"Michael C#" wrote:

> Try COALESCE().
> "Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> wrote in message
> news:EC91E50A-6D5F-4F67-9E96-7C37787B51A9@.microsoft.com...
>
>|||What data type is BadLine?
AMB
"Jeremy Lubich" wrote:
> Mike, et al.,
> I tried COALESCE and got an interesting result. Here is what is returned b
y
> QA.
> pic.BadLine = NULL
> IsNull(pic.BadLine, 0) = 1
> COALEASCE(pic.BadLine, 0) = 0
> Your workaround works. But, I am still stuck with the fact that IsNull loo
ks
> like it has a bug. I am worried about other parts of our database where we
> also use IsNull.
> I would think both of these values would return 0. Does it make any sense
> why the IsNull and COALEASCE funtions do not return the same value when th
e
> first argument is NULL?
> If someone replies to this thread and says, "yup, that looks like a bug to
> me", then I'll contact Microsoft to have it investigated. I just didn't wa
nt
> to be presumptious in my hypothesis before I threw this out into the
> community discussion.
> Jeremy
> "Michael C#" wrote:
>|||AMB,
Here is the DDL and statements you will need. I can reproduce it on any
database.
CREATE TABLE [dbo].[Packages] (
[StyleNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ComponentStyle] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Quantity] [int] NOT NULL ,
[Updated] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[QuoteLines] (
[QuoteId] [int] NOT NULL ,
[QuoteLineId] [int] NOT NULL ,
[ParentId] [int] NOT NULL ,
[LineType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [int] NOT NULL ,
[StyleNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
INSERT INTO QuoteLines
(QuoteID, QuoteLineID, ParentID, LineType, Quantity, StyleNumber)
VALUES
(1234, 1, 0, 'S', 1, 'item-x')
GO
CREATE VIEW dbo.pkgComponents
AS
SELECT l1.QuoteID,
l1.QuoteLineID,
l1.StyleNumber,
l2.StyleNumber ComponentStyle,
l2.Quantity / l1.Quantity Quantity
FROM QuoteLines l1
JOIN QuoteLines l2
ON l1.QuoteID = l2.QuoteID
AND l1.QuoteLineID = l2.ParentID
GO
CREATE VIEW dbo.pkgMissingComponents
AS
--Find Quote Lines with Invalid Components
SELECT
1 BadLine,
l.QuoteID,
l.QuoteLineID
FROM QuoteLines l
JOIN Packages p
ON l.StyleNumber = p.StyleNumber
LEFT JOIN
pkgComponents lpack
ON l.QuoteID = lpack.QuoteID
AND l.QuoteLineID = lpack.QuoteLineID
AND p.StyleNumber = lpack.StyleNumber
AND p.ComponentStyle = lpack.ComponentStyle
--Look for conditions that can make a package in config invalid:
--1. A Component has been added to the package definition, but not in Quote
Lines
WHERE lpack.ComponentStyle IS NULL
GROUP BY
l.QuoteID,
l.QuoteLineID
GO
CREATE VIEW dbo.pkgInvalidComponents
AS
--Find Quote Lines with Wrong Package Definitions
SELECT
1 BadLine,
lpack.QuoteID,
lpack.QuoteLineID
FROM QuoteLines l
JOIN Packages p
ON l.StyleNumber = p.StyleNumber
RIGHT JOIN pkgComponents lpack
ON l.QuoteID = lpack.QuoteID
AND l.QuoteLineID = lpack.QuoteLineID
AND p.StyleNumber = lpack.StyleNumber
AND p.ComponentStyle = lpack.ComponentStyle
--Look for two conditions that can make a package in QuoteLines invalid:
--1. Quantities on a component don't match package definition
--2. A Component remianing in the QuoteLines has been removed from the
master package definition
WHERE
p.Quantity <> lpack.Quantity
OR p.ComponentStyle IS NULL
GROUP BY
lpack.QuoteID,
lpack.QuoteLineID
GO
SELECT TOP 1
ql.QuoteID, --Fixed Values, Not Derivable
ql.QuoteLineID, --Fixed Values, Not Derivable
---
--Invalid Packages
--4/14/2005 Jeremy - There seems to be a bug in IsNull. Work around code is
to use a case statement.
--Old Code
--IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage
--New Code
(CASE WHEN pmc.BadLine IS NULL THEN IsNull(pmc.BadLine, 0) ELSE pmc.BadLine
END)
+ (CASE WHEN pic.BadLine IS NULL THEN IsNull(pic.BadLine, 0) ELSE
pic.BadLine END) InvalidPackage,
--Testing of the old way and another possible workaround
IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage2,
COALESCE(pmc.BadLine, 0) + COALESCE(pic.BadLine, 0) InvalidPackage3
----
FROM dbo.QuoteLines ql
LEFT JOIN dbo.pkgMissingComponents pmc
ON ql.QuoteID = pmc.QuoteID
AND ql.QuoteLineID = pmc.QuoteLineID
LEFT JOIN dbo.pkgInvalidComponents pic
ON ql.QuoteID = pic.QuoteID
AND ql.QuoteLineID = pic.QuoteLineID
Jeremy
"Alejandro Mesa" wrote:
> Unless pic.BadLine = 1 then not possible. How can we reproduce this in our
> computer?
> What data type is pic.BadLine?
>
> AMB
> "Jeremy Lubich" wrote:
>|||Jeremy,
I do not know what it is wrong, but the problem is not ISNULL. If you cast
the values of pic.BadLine and pmc.BadLine then you will see 1 also.
SELECT TOP 1
ql.QuoteID, --Fixed Values, Not Derivable
ql.QuoteLineID, --Fixed Values, Not Derivable
---
--Invalid Packages
--4/14/2005 Jeremy - There seems to be a bug in IsNull. Work around code is
to use a case statement.
--Old Code
--IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage
--New Code
(CASE WHEN pmc.BadLine IS NULL THEN IsNull(pmc.BadLine, 0) ELSE pmc.BadLine
END)
+ (CASE WHEN pic.BadLine IS NULL THEN IsNull(pic.BadLine, 0) ELSE
pic.BadLine END) InvalidPackage,
--Testing of the old way and another possible workaround
IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage2,
COALESCE(pmc.BadLine, 0) + COALESCE(pic.BadLine, 0) InvalidPackage3,
IsNull(pmc.BadLine, 0) as colA,
pmc.BadLine,
cast(pmc.BadLine as sql_variant) as [pmc_BadLine_sql_variant],
cast(pmc.BadLine as int) as [pmc_BadLine_int],
IsNull(pic.BadLine, 0),
pic.BadLine,
cast(pic.BadLine as sql_variant) as [pic.BadLine_sql_variant],
cast(pic.BadLine as int) as [pic.BadLine_int]
----
FROM dbo.QuoteLines ql
LEFT JOIN dbo.pkgMissingComponents pmc
ON ql.QuoteID = pmc.QuoteID
AND ql.QuoteLineID = pmc.QuoteLineID
LEFT JOIN dbo.pkgInvalidComponents pic
ON ql.QuoteID = pic.QuoteID
AND ql.QuoteLineID = pic.QuoteLineID
AMB
"Jeremy Lubich" wrote:
> AMB,
> Here is the DDL and statements you will need. I can reproduce it on any
> database.
> CREATE TABLE [dbo].[Packages] (
> [StyleNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ComponentStyle] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Quantity] [int] NOT NULL ,
> [Updated] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[QuoteLines] (
> [QuoteId] [int] NOT NULL ,
> [QuoteLineId] [int] NOT NULL ,
> [ParentId] [int] NOT NULL ,
> [LineType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Quantity] [int] NOT NULL ,
> [StyleNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO QuoteLines
> (QuoteID, QuoteLineID, ParentID, LineType, Quantity, StyleNumber)
> VALUES
> (1234, 1, 0, 'S', 1, 'item-x')
> GO
> CREATE VIEW dbo.pkgComponents
> AS
> SELECT l1.QuoteID,
> l1.QuoteLineID,
> l1.StyleNumber,
> l2.StyleNumber ComponentStyle,
> l2.Quantity / l1.Quantity Quantity
> FROM QuoteLines l1
> JOIN QuoteLines l2
> ON l1.QuoteID = l2.QuoteID
> AND l1.QuoteLineID = l2.ParentID
> GO
> CREATE VIEW dbo.pkgMissingComponents
> AS
> --Find Quote Lines with Invalid Components
> SELECT
> 1 BadLine,
> l.QuoteID,
> l.QuoteLineID
> FROM QuoteLines l
> JOIN Packages p
> ON l.StyleNumber = p.StyleNumber
> LEFT JOIN
> pkgComponents lpack
> ON l.QuoteID = lpack.QuoteID
> AND l.QuoteLineID = lpack.QuoteLineID
> AND p.StyleNumber = lpack.StyleNumber
> AND p.ComponentStyle = lpack.ComponentStyle
> --Look for conditions that can make a package in config invalid:
> --1. A Component has been added to the package definition, but not in Quot
e
> Lines
> WHERE lpack.ComponentStyle IS NULL
> GROUP BY
> l.QuoteID,
> l.QuoteLineID
> GO
> CREATE VIEW dbo.pkgInvalidComponents
> AS
> --Find Quote Lines with Wrong Package Definitions
> SELECT
> 1 BadLine,
> lpack.QuoteID,
> lpack.QuoteLineID
> FROM QuoteLines l
> JOIN Packages p
> ON l.StyleNumber = p.StyleNumber
> RIGHT JOIN pkgComponents lpack
> ON l.QuoteID = lpack.QuoteID
> AND l.QuoteLineID = lpack.QuoteLineID
> AND p.StyleNumber = lpack.StyleNumber
> AND p.ComponentStyle = lpack.ComponentStyle
> --Look for two conditions that can make a package in QuoteLines invalid:
> --1. Quantities on a component don't match package definition
> --2. A Component remianing in the QuoteLines has been removed from the
> master package definition
> WHERE
> p.Quantity <> lpack.Quantity
> OR p.ComponentStyle IS NULL
> GROUP BY
> lpack.QuoteID,
> lpack.QuoteLineID
> GO
> SELECT TOP 1
> ql.QuoteID, --Fixed Values, Not Derivable
> ql.QuoteLineID, --Fixed Values, Not Derivable
> ---
> --Invalid Packages
> --4/14/2005 Jeremy - There seems to be a bug in IsNull. Work around code
is
> to use a case statement.
> --Old Code
> --IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage
> --New Code
> (CASE WHEN pmc.BadLine IS NULL THEN IsNull(pmc.BadLine, 0) ELSE pmc.BadLi
ne
> END)
> + (CASE WHEN pic.BadLine IS NULL THEN IsNull(pic.BadLine, 0) ELSE
> pic.BadLine END) InvalidPackage,
> --Testing of the old way and another possible workaround
> IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage2,
> COALESCE(pmc.BadLine, 0) + COALESCE(pic.BadLine, 0) InvalidPackage3
> ----
> FROM dbo.QuoteLines ql
> LEFT JOIN dbo.pkgMissingComponents pmc
> ON ql.QuoteID = pmc.QuoteID
> AND ql.QuoteLineID = pmc.QuoteLineID
> LEFT JOIN dbo.pkgInvalidComponents pic
> ON ql.QuoteID = pic.QuoteID
> AND ql.QuoteLineID = pic.QuoteLineID
>
> Jeremy
> "Alejandro Mesa" wrote:
>|||AMB,
I have no experience with reporting bugs to Microsoft. It looks to me if I
try and email them this problem it will cost me $99. Do you know a way of
getting Microsofts attention without it costing $. How would you have this
investigated?
Jeremy
"Alejandro Mesa" wrote:
> Jeremy,
> I do not know what it is wrong, but the problem is not ISNULL. If you cast
> the values of pic.BadLine and pmc.BadLine then you will see 1 also.
> SELECT TOP 1
> ql.QuoteID, --Fixed Values, Not Derivable
> ql.QuoteLineID, --Fixed Values, Not Derivable
> ---
> --Invalid Packages
> --4/14/2005 Jeremy - There seems to be a bug in IsNull. Work around code
is
> to use a case statement.
> --Old Code
> --IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage
> --New Code
> (CASE WHEN pmc.BadLine IS NULL THEN IsNull(pmc.BadLine, 0) ELSE pmc.BadLi
ne
> END)
> + (CASE WHEN pic.BadLine IS NULL THEN IsNull(pic.BadLine, 0) ELSE
> pic.BadLine END) InvalidPackage,
> --Testing of the old way and another possible workaround
> IsNull(pmc.BadLine, 0) + IsNull(pic.BadLine, 0) InvalidPackage2,
> COALESCE(pmc.BadLine, 0) + COALESCE(pic.BadLine, 0) InvalidPackage3,
> IsNull(pmc.BadLine, 0) as colA,
> pmc.BadLine,
> cast(pmc.BadLine as sql_variant) as [pmc_BadLine_sql_variant],
> cast(pmc.BadLine as int) as [pmc_BadLine_int],
> IsNull(pic.BadLine, 0),
> pic.BadLine,
> cast(pic.BadLine as sql_variant) as [pic.BadLine_sql_variant],
> cast(pic.BadLine as int) as [pic.BadLine_int]
> ----
> FROM dbo.QuoteLines ql
> LEFT JOIN dbo.pkgMissingComponents pmc
> ON ql.QuoteID = pmc.QuoteID
> AND ql.QuoteLineID = pmc.QuoteLineID
> LEFT JOIN dbo.pkgInvalidComponents pic
> ON ql.QuoteID = pic.QuoteID
> AND ql.QuoteLineID = pic.QuoteLineID
>
> AMB
> "Jeremy Lubich" wrote:
>

Is there other control like ReportViewer control to avoid security problems

Hello Friends
i am using ReportViewer Control to view the SQL REPORT in the aspx page
its working fine and i am able to view the reports..
Now The problem is that
ReportViewer needs direct access to the Database Server
(i mean to say, we need to give the serverURL(database) to the
reportviewer )
but at my client place the web application is installed on one
server(which is configured to public IP) and the database is on other
server(which is not publicIP)
So when i try to view the report from my web application i am getting
error "page not Found"
can any one please tell me the way to view the reports in my web page
with other than reportviewer.. which gives the same functionality and
which can overcome these sort of security issues
thanx
anil...Not sure if it's a bad practice but before I knew about the ReportViewer I
used an <iframe> and displayed it in that...
It worked fine and should help you with your problem.
Regards
--
http://dotnet.org.za/stanley
"anil" wrote:
> Hello Friends
> i am using ReportViewer Control to view the SQL REPORT in the aspx page
> its working fine and i am able to view the reports..
> Now The problem is that
> ReportViewer needs direct access to the Database Server
> (i mean to say, we need to give the serverURL(database) to the
> reportviewer )
> but at my client place the web application is installed on one
> server(which is configured to public IP) and the database is on other
> server(which is not publicIP)
> So when i try to view the report from my web application i am getting
> error "page not Found"
> can any one please tell me the way to view the reports in my web page
> with other than reportviewer.. which gives the same functionality and
> which can overcome these sort of security issues
> thanx
> anil...
>|||Hello Stan, thanx for the info
i would like to know some more info reg.
by using <iframe> how can i redirect the request to the database
server...
do u have any idea.. or examples pls let me know ..
thanx & regards
Stan wrote:
> Not sure if it's a bad practice but before I knew about the ReportViewer I
> used an <iframe> and displayed it in that...
> It worked fine and should help you with your problem.
> Regards
> --
> http://dotnet.org.za/stanley
>
> "anil" wrote:
> > Hello Friends
> >
> > i am using ReportViewer Control to view the SQL REPORT in the aspx page
> > its working fine and i am able to view the reports..
> >
> > Now The problem is that
> >
> > ReportViewer needs direct access to the Database Server
> > (i mean to say, we need to give the serverURL(database) to the
> > reportviewer )
> > but at my client place the web application is installed on one
> > server(which is configured to public IP) and the database is on other
> > server(which is not publicIP)
> > So when i try to view the report from my web application i am getting
> > error "page not Found"
> >
> > can any one please tell me the way to view the reports in my web page
> > with other than reportviewer.. which gives the same functionality and
> > which can overcome these sort of security issues
> >
> > thanx
> > anil...
> >
> >|||IFrame will have the same problem. You need to use webservices in your web
app. Or, using VS 2005 web control (which itself uses web services) supports
this configuration.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"anil" <kumar.vanaparthy@.gmail.com> wrote in message
news:1130364685.976063.324410@.g43g2000cwa.googlegroups.com...
> Hello Stan, thanx for the info
> i would like to know some more info reg.
> by using <iframe> how can i redirect the request to the database
> server...
> do u have any idea.. or examples pls let me know ..
> thanx & regards
>
> Stan wrote:
>> Not sure if it's a bad practice but before I knew about the ReportViewer
>> I
>> used an <iframe> and displayed it in that...
>> It worked fine and should help you with your problem.
>> Regards
>> --
>> http://dotnet.org.za/stanley
>>
>> "anil" wrote:
>> > Hello Friends
>> >
>> > i am using ReportViewer Control to view the SQL REPORT in the aspx page
>> > its working fine and i am able to view the reports..
>> >
>> > Now The problem is that
>> >
>> > ReportViewer needs direct access to the Database Server
>> > (i mean to say, we need to give the serverURL(database) to the
>> > reportviewer )
>> > but at my client place the web application is installed on one
>> > server(which is configured to public IP) and the database is on other
>> > server(which is not publicIP)
>> > So when i try to view the report from my web application i am getting
>> > error "page not Found"
>> >
>> > can any one please tell me the way to view the reports in my web page
>> > with other than reportviewer.. which gives the same functionality and
>> > which can overcome these sort of security issues
>> >
>> > thanx
>> > anil...
>> >
>> >
>

Is there cascading permission?

Let's say that I have a view that has no explicit permissions defined for
users. Let's also say that I write a stored procedure which uses this view.
Now, if I give explicit permissions to a user to EXEC the stored procedure,
will the procedure execute correctly even if I don't explicitily give
permissions (such as SELECT) for the user to the view?
Michael HocksteinYes, it is.
"michael" <howlinghound@.nospam.nospam> wrote in message
news:CAB858A0-22F5-410C-842E-A0FE19C84343@.microsoft.com...
> Let's say that I have a view that has no explicit permissions defined for
> users. Let's also say that I write a stored procedure which uses this
> view.
> Now, if I give explicit permissions to a user to EXEC the stored
> procedure,
> will the procedure execute correctly even if I don't explicitily give
> permissions (such as SELECT) for the user to the view?
>
> --
> Michael Hockstein|||So, as long as a user has permissions to execute a stored procedure, the
stored procedure will execute correctly even if the user does not have
explicit permissions defined for the objects consumed within the stored
procedure?
If the user has permissions to execute a stored procedure but that the user
is explicitly denied permissions to objects consumed by the stored procedure
will the stored procedure still execute correctly?
--
Michael Hockstein
"Uri Dimant" wrote:

> Yes, it is.
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:CAB858A0-22F5-410C-842E-A0FE19C84343@.microsoft.com...
>
>|||So, if a user has permission to execute a stored procedure which in turn
consumes objects that the same user does not have explicit permissions
defined, the stored procedure will execute without security issues?
And, if a user has permission to execute a stored procedure which in turn
consumes objects that the same user is explicitly denied permisions, will th
e
stored procedure still execute without security issues?
Where can I find documentation on how this security cascades?
Michael Hockstein
"Uri Dimant" wrote:

> Yes, it is.
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:CAB858A0-22F5-410C-842E-A0FE19C84343@.microsoft.com...
>
>|||> So, as long as a user has permissions to execute a stored procedure, the
> stored procedure will execute correctly even if the user does not have
> explicit permissions defined for the objects consumed within the stored
> procedure?
yes, it will , unless you have dynamic sql within a stored procedure , then
you'll have to grant permision on undelaying tables

> If the user has permissions to execute a stored procedure but that the
> user
> is explicitly denied permissions to objects consumed by the stored
> procedure
> will the stored procedure still execute correctly?
Yes , it will
"michael" <howlinghound@.nospam.nospam> wrote in message
news:2DF71CC1-0FAF-4D82-A396-782AC53757C9@.microsoft.com...[vbcol=seagreen]
> So, as long as a user has permissions to execute a stored procedure, the
> stored procedure will execute correctly even if the user does not have
> explicit permissions defined for the objects consumed within the stored
> procedure?
> If the user has permissions to execute a stored procedure but that the
> user
> is explicitly denied permissions to objects consumed by the stored
> procedure
> will the stored procedure still execute correctly?
> --
> Michael Hockstein
>
> "Uri Dimant" wrote:
>|||See:
Security -Giving Permissions through Stored Procedures
http://www.sommarskog.se/grantperm.html
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"michael" <howlinghound@.nospam.nospam> wrote in message
news:F84CE2FB-8709-4B68-BB3C-9F86722B9A8E@.microsoft.com...[vbcol=seagreen]
> So, if a user has permission to execute a stored procedure which in turn
> consumes objects that the same user does not have explicit permissions
> defined, the stored procedure will execute without security issues?
> And, if a user has permission to execute a stored procedure which in turn
> consumes objects that the same user is explicitly denied permisions, will
> the
> stored procedure still execute without security issues?
> Where can I find documentation on how this security cascades?
>
> --
> Michael Hockstein
>
> "Uri Dimant" wrote:
>|||Thanks. I'll look at the reference. BTW, your tag line is one of my favorite
all time sayings.
Michael Hockstein
"Arnie Rowland" wrote:

> See:
> Security -Giving Permissions through Stored Procedures
> http://www.sommarskog.se/grantperm.html
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:F84CE2FB-8709-4B68-BB3C-9F86722B9A8E@.microsoft.com...
>
>|||Check out 'ownership chains' in the SQL Server Books Online. The principal
is basically that permissions on indirectly referenced objects are not
needed as long as the objects (or schema on 2005) involved have the same
owner.
Hope this helps.
Dan Guzman
SQL Server MVP
"michael" <howlinghound@.nospam.nospam> wrote in message
news:0EED2BFD-1EA5-4DA8-A512-C4D8E91CEFC0@.microsoft.com...[vbcol=seagreen]
> Thanks. I'll look at the reference. BTW, your tag line is one of my
> favorite
> all time sayings.
> --
> Michael Hockstein
>
> "Arnie Rowland" wrote:
>

Is there any way to make a VIEW automatically reflect changes to underlying tables?

(SQL Server 2000, SP3a)
Hello all!
I think I asked this question in a different way a long time ago, and wasn't sure if there
was any resolution.
Consider the following DDL/DML:
create table tTest (Id int)
go
create view vTest
as
select * from tTest
go
select * from vTest
go
alter table tTest add Name varchar(255)
go
select * from vTest
go
Is there any easy way, short of DROPping/CREATEing or ALTERing the VIEW, to have the VIEW
automatically reflect the changes to the underlying table? I had hoped a sp_recompile
might do the trick, but alas. :-(
Thanks for any help you can provide!
John PetersonThis is a multi-part message in MIME format.
--=_NextPart_000_005E_01C36B1C.27177190
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Check out sp_refreshview in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:OmfK0zzaDHA.880@.TK2MSFTNGP09.phx.gbl...
(SQL Server 2000, SP3a)
Hello all!
I think I asked this question in a different way a long time ago, and =wasn't sure if there
was any resolution.
Consider the following DDL/DML:
create table tTest (Id int)
go
create view vTest
as
select * from tTest
go
select * from vTest
go
alter table tTest add Name varchar(255)
go
select * from vTest
go
Is there any easy way, short of DROPping/CREATEing or ALTERing the VIEW, =to have the VIEW
automatically reflect the changes to the underlying table? I had hoped =a sp_recompile
might do the trick, but alas. :-(
Thanks for any help you can provide!
John Peterson
--=_NextPart_000_005E_01C36B1C.27177190
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Check out sp_refreshview in the =BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:OmfK0zzaDHA.880@.T=K2MSFTNGP09.phx.gbl...(SQL Server 2000, SP3a)Hello all!I think I asked this =question in a different way a long time ago, and wasn't sure if therewas any resolution.Consider the following DDL/DML:create =table tTest (Id int)gocreate view vTestas select * =from tTestgoselect * from vTestgoalter table tTest =add Name varchar(255)goselect * from vTestgoIs there =any easy way, short of DROPping/CREATEing or ALTERing the VIEW, to have the VIEWautomatically reflect the changes to the underlying table? =I had hoped a sp_recompilemight do the trick, but alas. =:-(Thanks for any help you can provide!John Peterson

---

--=_NextPart_000_005E_01C36B1C.27177190--|||John,
I think you are searching fot sp_refreshview system SP. Check it in Books
OnLine.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OmfK0zzaDHA.880@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I think I asked this question in a different way a long time ago, and
wasn't sure if there
> was any resolution.
> Consider the following DDL/DML:
>
> create table tTest (Id int)
> go
> create view vTest
> as
> select * from tTest
> go
> select * from vTest
> go
> alter table tTest add Name varchar(255)
> go
> select * from vTest
> go
>
> Is there any easy way, short of DROPping/CREATEing or ALTERing the VIEW,
to have the VIEW
> automatically reflect the changes to the underlying table? I had hoped a
sp_recompile
> might do the trick, but alas. :-(
> Thanks for any help you can provide!
> John Peterson
>|||John,
Try this:
create table tTest (Id int)
go
create view vTest
as
select * from tTest
go
select * from vTest
go
alter table tTest add Name varchar(255)
go
sp_refreshview vTest
go
select * from vTest
go
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OmfK0zzaDHA.880@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I think I asked this question in a different way a long time ago, and
wasn't sure if there
> was any resolution.
> Consider the following DDL/DML:
>
> create table tTest (Id int)
> go
> create view vTest
> as
> select * from tTest
> go
> select * from vTest
> go
> alter table tTest add Name varchar(255)
> go
> select * from vTest
> go
>
> Is there any easy way, short of DROPping/CREATEing or ALTERing the VIEW,
to have the VIEW
> automatically reflect the changes to the underlying table? I had hoped a
sp_recompile
> might do the trick, but alas. :-(
> Thanks for any help you can provide!
> John Peterson
>|||Thanks, all! That was a speedy and accurate response! :-)
The sp_refreshview is exactly the type of thing I was looking for.
I think my other (somewhat related) thread was in reference to changing the
ansi_null/quoted_identifier settings of a VIEW/SP without having to DROP/CREATE or ALTER
that VIEW. It sounded as if there wasn't a convenient way to do that (though, someone had
come up with a clever solution to "dinking" the sysobjects table).
In any event, thanks again! :-)
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OmfK0zzaDHA.880@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I think I asked this question in a different way a long time ago, and wasn't sure if
there
> was any resolution.
> Consider the following DDL/DML:
>
> create table tTest (Id int)
> go
> create view vTest
> as
> select * from tTest
> go
> select * from vTest
> go
> alter table tTest add Name varchar(255)
> go
> select * from vTest
> go
>
> Is there any easy way, short of DROPping/CREATEing or ALTERing the VIEW, to have the
VIEW
> automatically reflect the changes to the underlying table? I had hoped a sp_recompile
> might do the trick, but alas. :-(
> Thanks for any help you can provide!
> John Peterson
>