How do I make a view read-only

I want to create a view that can not be used for record inserts, updates or
deletes - by anyone, including administrators. Is there an option that can
be used in creating the view so I don't have to go through all the users
setting up deny permissions?
http://developer.mimer.com/documentation/html_92/Mimer_SQL_Engine_DocSet/Data_manipulation6.html
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:40734E47-0774-4810-959C-5212C74089A7@.microsoft.com...
>I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that
> can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?
|||There is nothing you can do to deny administrators rights to your view.
Your best bet is to create an INSTEAD OF TRIGGER that just generates an
error message whenever someone tries to modify the view.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:40734E47-0774-4810-959C-5212C74089A7@.microsoft.com...
>I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that
> can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?
|||Thank you for all your suggestions. I came up with this solution: I added
DISTINCT to the syntax. Since the select list included the unique
identifier, I get the same view results, but now the view is not editable.
"Bev Kaufman" wrote:

> I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?
|||Keep in mind that this solution might not always possible. The INSTEAD OF
trigger solution is much more general purpose. Also in many cases, adding a
DISTINCT can have a negative impact on query performance if there is no
guaranteed uniqueness. Does your uniqueifier have a unique index? That is
the only way that SQL Server knows the values are already unique and doesn't
add extra processing for the DISTINCT.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:89E10E73-53AD-466B-93F1-7C8B9E6C3E0F@.microsoft.com...[vbcol=seagreen]
> Thank you for all your suggestions. I came up with this solution: I added
> DISTINCT to the syntax. Since the select list included the unique
> identifier, I get the same view results, but now the view is not editable.
> "Bev Kaufman" wrote:

How do I make a view read-only

I want to create a view that can not be used for record inserts, updates or
deletes - by anyone, including administrators. Is there an option that can
be used in creating the view so I don't have to go through all the users
setting up deny permissions?http://developer.mimer.com/documentation/html_92/Mimer_SQL_Engine_DocSet/Data_manipulation6.html
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:40734E47-0774-4810-959C-5212C74089A7@.microsoft.com...
>I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that
> can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?|||If you don't want to change the view definition you can add an INSTEAD_OF
trigger for INSERT, DELETE and UPDATE that do nothing.
--
Rubén Garrigós
Solid Quality Mentors
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:40734E47-0774-4810-959C-5212C74089A7@.microsoft.com...
>I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that
> can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?|||Hi Bev
I really think this should be covered by the permissions you grant to the
view, trying to stop administrators updating the view would just mean only
mean they would need to hit the base tables instead.
John
"Bev Kaufman" wrote:
> I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?|||There is nothing you can do to deny administrators rights to your view.
Your best bet is to create an INSTEAD OF TRIGGER that just generates an
error message whenever someone tries to modify the view.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:40734E47-0774-4810-959C-5212C74089A7@.microsoft.com...
>I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that
> can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?|||Thank you for all your suggestions. I came up with this solution: I added
DISTINCT to the syntax. Since the select list included the unique
identifier, I get the same view results, but now the view is not editable.
"Bev Kaufman" wrote:
> I want to create a view that can not be used for record inserts, updates or
> deletes - by anyone, including administrators. Is there an option that can
> be used in creating the view so I don't have to go through all the users
> setting up deny permissions?|||Keep in mind that this solution might not always possible. The INSTEAD OF
trigger solution is much more general purpose. Also in many cases, adding a
DISTINCT can have a negative impact on query performance if there is no
guaranteed uniqueness. Does your uniqueifier have a unique index? That is
the only way that SQL Server knows the values are already unique and doesn't
add extra processing for the DISTINCT.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:89E10E73-53AD-466B-93F1-7C8B9E6C3E0F@.microsoft.com...
> Thank you for all your suggestions. I came up with this solution: I added
> DISTINCT to the syntax. Since the select list included the unique
> identifier, I get the same view results, but now the view is not editable.
> "Bev Kaufman" wrote:
>> I want to create a view that can not be used for record inserts, updates
>> or
>> deletes - by anyone, including administrators. Is there an option that
>> can
>> be used in creating the view so I don't have to go through all the users
>> setting up deny permissions?sql

How do I handle time-zones?

I need to be able to consider time-zones, including daylight savings, within
stored procedures. Can anyone point me to a resource/code base which will
either allow me to procure or build my own code? I need to do things like
determine local time based on a customer's state, zip code, city, or whateve
r
is required to truly determine the customer's local time.
Thanks,
Michael"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:20421547-C406-4AA1-8E4A-FEF5012FCE5E@.microsoft.com...
>I need to be able to consider time-zones, including daylight savings,
>within
> stored procedures. Can anyone point me to a resource/code base which will
> either allow me to procure or build my own code? I need to do things like
> determine local time based on a customer's state, zip code, city, or
> whatever
> is required to truly determine the customer's local time.
> Thanks,
> Michael
You are in for a heap of work. Especially if you need to go international.
Just a couple of notes. Daylight savings time is done differently in
different countries. IIRC, the US currently switches on the last Sunday in
October and the first Sunday in April. England switches on the last Sunday
in October and the last Sunday in March.
Some countries require a minium of 150 days of daylight savings time, so
they have different schedules every year.
If you only need to do the US, then you will need to create a stack of
lookup tables for state, zip codes and cities. You will need logic to
default if the zip code or city doesn't exist. You will need to keep in
mind also that some states like Tennesse and Texas have multiple time zones.
You will need to keep in mind that Arizona and Hawaii don't do daylight
savings time and part of Indiana doesn't either.
Google for daylight savings time and you will find a number of articles
regarding this issue.
Good luck!
Rick Sawtell|||> Just a couple of notes. Daylight savings time is done differently in
> different countries. IIRC, the US currently switches on the last Sunday
> in October and the first Sunday in April.
And to add to the fun, that changes next year!
A|||I just posted this the other day for similar question here:
http://channel9.msdn.com/ShowPost.aspx?PostID=142586
Client would still need to pass it's standard time zone name to the proc or
you could have that data stored in a table by user.
William Stacey [MVP]
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:20421547-C406-4AA1-8E4A-FEF5012FCE5E@.microsoft.com...
>I need to be able to consider time-zones, including daylight savings,
>within
> stored procedures. Can anyone point me to a resource/code base which will
> either allow me to procure or build my own code? I need to do things like
> determine local time based on a customer's state, zip code, city, or
> whatever
> is required to truly determine the customer's local time.
> Thanks,
> Michael

How do i find all subscriptions

Hi There

I need to get rid of all subscriptions (including cleaning up all associated sql jobs) for RS 2000 and 2005.

We have hundreds of reports , going through them 1 by 1 is not an option.

When i look at the system tables, i join Subscription to Catalog, but i find reports with subscriptions in the system tables but when i go to Report Manger there is no subscription for the report.

In a nutshell , how can i see all valid subscription for all reports in RS, how can i delete all of them and make sur eall sql jobs associated etc are deleted.?

Thanx

Hi Dietz, try it with the object-model of RS:

rs = new rs2005.ReportingService2005();

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

rs.Url = "http://<Server>/ReportServer/ReportService2005.asmx";

Get all Subscriptions from a related Report

string RelatedReport = ConfigurationManager.AppSettings["RelatedReport"];

rs2005.Subscription[] subscr = rs.ListSubscriptions(RelatedReport, "");

string[] SubscID = new string[subscr.Length];

int icount = 0;

foreach (rs2005.Subscription s in subscr)

{

SubscID[icount] = s.SubscriptionID;

icount++;

}

Delete all Subscriptions from this Report.

for (int j = 0; j <= SubscID.Length-1; j++)

{

rs.DeleteSubscription(SubscID[j]);

}

If you want to delete just all Subscriptions, please read following Remarks from Microsoft:

You can supply a null (Nothing in Visual Basic) value for the Owner and Report parameters. The information that the ListSubscriptions method returns varies depending on the parameters that are submitted:

If the values of both Owner and Report are null, the method returns all subscriptions for all reports that the current user has permission to view.

If only the Owner parameter is submitted, the method returns all subscriptions for all reports that the specified user has created and has permission to view.

If only the Report parameter is submitted, the method returns all subscriptions for all users of the specified report that the current user has permission to view.

If valid values are supplied for both the Owner parameter and Report parameter, the method returns all subscriptions for the specified report that the specified user created and has permission to view.

|||

Hi Ronny

Thank You for the feedback, unfortunately i am tsql guy. Does anyone know of system stored procs that can give me this info, or delete subscriptions for RS?

Or a tsql equivelant of the code above?

Thanx

|||

Build a cursor to iterate over all Reports (Catalog)

Then set up a delete statement in interation

Delete from Subscriptions where Report_OID=Catalog.ItemID;

All Subscriptions will be deleted and also the jobs, beacause the triggers are doing their job.

Or use sp DeleteActiveSubscriptions, DeleteSubscription

Ronny

|||Awesome, thanx Ronny i will try this.|||

Sorry - we don't support folks directly accessing the report server database. If you build a solution on top of database calls, you will find that with service packs/hotfixes, or major versions, your solution will eventually break.

I have a blog post for how to monitor subscriptions here:

http://blogs.msdn.com/lukaszp/archive/2005/12/30/508328.aspx

Thanks,

-Lukasz

|||

Hi Lukasz, thank you for not supporting me!

i just want to let you know that accessing the Report Server Database directly is a job on admins. As a admin i am responsible for service packs / hofixes or major versions and i am able to read the release-notes. I also remember that i wrote a little tool which supports me in deleteing subscriptions. I can have a short look to see if the schema changes.

btw: arent the SOAP-API calls using the same stored procedures to acces the database?

All the Best

Ronny

|||

The SOAP APIs to use various stored procs. However, the definition of stored procs can change over time and unexpectedly. As such, I have to empahsize that you shouldn't rely on them.

The difference is that we version the SOAP APIs to ensure backwards compatibility. For stored procs/tables/views/etc, we just change them as needed to support our development efforts.

-Lukasz

|||

Hi Lukasz,

in general i agree with your statements, but until the Reporting Services are having some gaps in Administration i'm forced to do so.

And it was not so bad, because we earned money to sell those kind of hacks even to global players.

All the Best

Ronny

|||

Ronny,

Thanks for your honest feedback - I will communicate this more broadly in the Reporting Services team.

Thanks,

-Lukasz

How do i find all subscriptions

Hi There

I need to get rid of all subscriptions (including cleaning up all associated sql jobs) for RS 2000 and 2005.

We have hundreds of reports , going through them 1 by 1 is not an option.

When i look at the system tables, i join Subscription to Catalog, but i find reports with subscriptions in the system tables but when i go to Report Manger there is no subscription for the report.

In a nutshell , how can i see all valid subscription for all reports in RS, how can i delete all of them and make sur eall sql jobs associated etc are deleted.?

Thanx

Hi Dietz, try it with the object-model of RS:

rs = new rs2005.ReportingService2005();

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

rs.Url = "http://<Server>/ReportServer/ReportService2005.asmx";

Get all Subscriptions from a related Report

string RelatedReport = ConfigurationManager.AppSettings["RelatedReport"];

rs2005.Subscription[] subscr = rs.ListSubscriptions(RelatedReport, "");

string[] SubscID = newstring[subscr.Length];

int icount = 0;

foreach (rs2005.Subscription s in subscr)

{

SubscID[icount] = s.SubscriptionID;

icount++;

}

Delete all Subscriptions from this Report.

for (int j = 0; j <= SubscID.Length-1; j++)

{

rs.DeleteSubscription(SubscID[j]);

}

If you want to delete just all Subscriptions, please read following Remarks from Microsoft:

You can supply a null (Nothing in Visual Basic) value for the Owner and Report parameters. The information that the ListSubscriptions method returns varies depending on the parameters that are submitted:

If the values of both Owner and Report are null, the method returns all subscriptions for all reports that the current user has permission to view.

If only the Owner parameter is submitted, the method returns all subscriptions for all reports that the specified user has created and has permission to view.

If only the Report parameter is submitted, the method returns all subscriptions for all users of the specified report that the current user has permission to view.

If valid values are supplied for both the Owner parameter and Report parameter, the method returns all subscriptions for the specified report that the specified user created and has permission to view.

|||

Hi Ronny

Thank You for the feedback, unfortunately i am tsql guy. Does anyone know of system stored procs that can give me this info, or delete subscriptions for RS?

Or a tsql equivelant of the code above?

Thanx

|||

Build a cursor to iterate over all Reports (Catalog)

Then set up a delete statement in interation

Delete from Subscriptions where Report_OID=Catalog.ItemID;

All Subscriptions will be deleted and also the jobs, beacause the triggers are doing their job.

Or use sp DeleteActiveSubscriptions, DeleteSubscription

Ronny

|||Awesome, thanx Ronny i will try this.|||

Sorry - we don't support folks directly accessing the report server database. If you build a solution on top of database calls, you will find that with service packs/hotfixes, or major versions, your solution will eventually break.

I have a blog post for how to monitor subscriptions here:

http://blogs.msdn.com/lukaszp/archive/2005/12/30/508328.aspx

Thanks,

-Lukasz

|||

Hi Lukasz, thank you for not supporting me!

i just want to let you know that accessing the Report Server Database directly is a job on admins. As a admin i am responsible for service packs / hofixes or major versions and i am able to read the release-notes. I also remember that i wrote a little tool which supports me in deleteing subscriptions. I can have a short look to see if the schema changes.

btw: arent the SOAP-API calls using the same stored procedures to acces the database?

All the Best

Ronny

|||

The SOAP APIs to use various stored procs. However, the definition of stored procs can change over time and unexpectedly. As such, I have to empahsize that you shouldn't rely on them.

The difference is that we version the SOAP APIs to ensure backwards compatibility. For stored procs/tables/views/etc, we just change them as needed to support our development efforts.

-Lukasz

|||

Hi Lukasz,

in general i agree with your statements, but until the Reporting Services are having some gaps in Administration i'm forced to do so.

And it was not so bad, because we earned money to sell those kind of hacks even to global players.

All the Best

Ronny

|||

Ronny,

Thanks for your honest feedback - I will communicate this more broadly in the Reporting Services team.

Thanks,

-Lukasz