view schemabinding  
Author Message
jeffro2588





PostPosted: Wed Jul 04 03:55:34 CDT 2007 Top

SQL Server Developer >> view schemabinding

Is it possible to create one view with schemabinding which has the select
query on several tables?
Or do I have to have one view with schemabinding per table?
Thanks

i.e.

alter view vwTables with schemabinding
as
SELECT ActionID, [Action] FROM dbo.tblActions
SELECT AuditID,TableID,ActionID,UserID,ActionDate FROM dbo.tblAudits

SQL Server40  
 
 
Uri





PostPosted: Wed Jul 04 03:55:34 CDT 2007 Top

SQL Server Developer >> view schemabinding Hi
Perhaps if you explain what are you trying to achive we would bring you
suggestion. Why two SELECTs?








> Is it possible to create one view with schemabinding which has the select
> query on several tables?
> Or do I have to have one view with schemabinding per table?
> Thanks
>
> i.e.
>
> alter view vwTables with schemabinding
> as
> SELECT ActionID, [Action] FROM dbo.tblActions
> SELECT AuditID,TableID,ActionID,UserID,ActionDate FROM dbo.tblAudits


 
 
farshad





PostPosted: Wed Jul 04 04:16:01 CDT 2007 Top

SQL Server Developer >> view schemabinding I once read somewhere that by creating a view with schemabinding which has
select on the table then you will not be able to delete the table
accidentally.

I can create one view with schemabindiong for a select query on a table but
I am not sure if I need to create a view for each table or one view for all
the tables.
Thanks



> Hi
> Perhaps if you explain what are you trying to achive we would bring you
> suggestion. Why two SELECTs?
>
>
>
>
>
>


> > Is it possible to create one view with schemabinding which has the select
> > query on several tables?
> > Or do I have to have one view with schemabinding per table?
> > Thanks
> >
> > i.e.
> >
> > alter view vwTables with schemabinding
> > as
> > SELECT ActionID, [Action] FROM dbo.tblActions
> > SELECT AuditID,TableID,ActionID,UserID,ActionDate FROM dbo.tblAudits
>
>
>
 
 
Uri





PostPosted: Wed Jul 04 04:33:40 CDT 2007 Top

SQL Server Developer >> view schemabinding Hi
>I once read somewhere that by creating a view with schemabinding which has
> select on the table then you will not be able to delete the table
> accidentally.

Ok, make sense

> I can create one view with schemabindiong for a select query on a table
> but
> I am not sure if I need to create a view for each table or one view for
> all
> the tables.

Ok what version are you using? I assume you are still on SQL Server 2000
,right?
I'd suggest you GRANT SELECT permission on view/s to users and DENY
permission on underlyaing tables.
Well , the question is if the user is an owner of table he/she has DROP it
accidentally, right? So in this case you need to manage security in
another way .






>I once read somewhere that by creating a view with schemabinding which has
> select on the table then you will not be able to delete the table
> accidentally.
>
> I can create one view with schemabindiong for a select query on a table
> but
> I am not sure if I need to create a view for each table or one view for
> all
> the tables.
> Thanks
>

>
>> Hi
>> Perhaps if you explain what are you trying to achive we would bring you
>> suggestion. Why two SELECTs?
>>
>>
>>
>>
>>
>>


>> > Is it possible to create one view with schemabinding which has the
>> > select
>> > query on several tables?
>> > Or do I have to have one view with schemabinding per table?
>> > Thanks
>> >
>> > i.e.
>> >
>> > alter view vwTables with schemabinding
>> > as
>> > SELECT ActionID, [Action] FROM dbo.tblActions
>> > SELECT AuditID,TableID,ActionID,UserID,ActionDate FROM dbo.tblAudits
>>
>>
>>


 
 
farshad





PostPosted: Wed Jul 04 05:16:00 CDT 2007 Top

SQL Server Developer >> view schemabinding Sorry, don't follow you...
I am using sql 2005 and would like to ceate one view with one select per
table.
Is this possible?



> Hi
> >I once read somewhere that by creating a view with schemabinding which has
> > select on the table then you will not be able to delete the table
> > accidentally.
>
> Ok, make sense
>
> > I can create one view with schemabindiong for a select query on a table
> > but
> > I am not sure if I need to create a view for each table or one view for
> > all
> > the tables.
>
> Ok what version are you using? I assume you are still on SQL Server 2000
> ,right?
> I'd suggest you GRANT SELECT permission on view/s to users and DENY
> permission on underlyaing tables.
> Well , the question is if the user is an owner of table he/she has DROP it
> accidentally, right? So in this case you need to manage security in
> another way .
>
>
>
>


> >I once read somewhere that by creating a view with schemabinding which has
> > select on the table then you will not be able to delete the table
> > accidentally.
> >
> > I can create one view with schemabindiong for a select query on a table
> > but
> > I am not sure if I need to create a view for each table or one view for
> > all
> > the tables.
> > Thanks
> >

> >
> >> Hi
> >> Perhaps if you explain what are you trying to achive we would bring you
> >> suggestion. Why two SELECTs?
> >>
> >>
> >>
> >>
> >>
> >>


> >> > Is it possible to create one view with schemabinding which has the
> >> > select
> >> > query on several tables?
> >> > Or do I have to have one view with schemabinding per table?
> >> > Thanks
> >> >
> >> > i.e.
> >> >
> >> > alter view vwTables with schemabinding
> >> > as
> >> > SELECT ActionID, [Action] FROM dbo.tblActions
> >> > SELECT AuditID,TableID,ActionID,UserID,ActionDate FROM dbo.tblAudits
> >>
> >>
> >>
>
>
>
 
 
Damien





PostPosted: Wed Jul 04 05:22:47 CDT 2007 Top

SQL Server Developer >> view schemabinding
> I once read somewhere that by creating a view with schemabinding which has
> select on the table then you will not be able to delete the table
> accidentally.
>
You can do this, but it's more a matter of a happy accident of the way
things work rather than a "this is why schemabinding exists" kind of
thing. I'd suggest just going for 1 per table - that way when you add
a table, you don't have to alter the view, and if you want to drop,
say 2 tables, you just drop their associated views rather than having
to drop/modify the ONE BIG view.

That being said, if you're determined to try to do it as ONE BIG view,
I *think* the following would work:

create view BigDaddyView with schemabinding as
select COUNT(*) from table1
union all
select COUNT(*) from table2
.
.
.
select COUNT(*) from tableN

Damien

 
 
Uri





PostPosted: Wed Jul 04 05:32:31 CDT 2007 Top

SQL Server Developer >> view schemabinding Ok if you use SQL Server 2005 take a look at DATABASE's triggers to prevent
DROP TABLE event






> Sorry, don't follow you...
> I am using sql 2005 and would like to ceate one view with one select per
> table.
> Is this possible?
>

>
>> Hi
>> >I once read somewhere that by creating a view with schemabinding which
>> >has
>> > select on the table then you will not be able to delete the table
>> > accidentally.
>>
>> Ok, make sense
>>
>> > I can create one view with schemabindiong for a select query on a table
>> > but
>> > I am not sure if I need to create a view for each table or one view for
>> > all
>> > the tables.
>>
>> Ok what version are you using? I assume you are still on SQL Server 2000
>> ,right?
>> I'd suggest you GRANT SELECT permission on view/s to users and DENY
>> permission on underlyaing tables.
>> Well , the question is if the user is an owner of table he/she has DROP
>> it
>> accidentally, right? So in this case you need to manage security in
>> another way .
>>
>>
>>
>>


>> >I once read somewhere that by creating a view with schemabinding which
>> >has
>> > select on the table then you will not be able to delete the table
>> > accidentally.
>> >
>> > I can create one view with schemabindiong for a select query on a table
>> > but
>> > I am not sure if I need to create a view for each table or one view for
>> > all
>> > the tables.
>> > Thanks
>> >

>> >
>> >> Hi
>> >> Perhaps if you explain what are you trying to achive we would bring
>> >> you
>> >> suggestion. Why two SELECTs?
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>


>> >> > Is it possible to create one view with schemabinding which has the
>> >> > select
>> >> > query on several tables?
>> >> > Or do I have to have one view with schemabinding per table?
>> >> > Thanks
>> >> >
>> >> > i.e.
>> >> >
>> >> > alter view vwTables with schemabinding
>> >> > as
>> >> > SELECT ActionID, [Action] FROM dbo.tblActions
>> >> > SELECT AuditID,TableID,ActionID,UserID,ActionDate FROM dbo.tblAudits
>> >>
>> >>
>> >>
>>
>>
>>


 
 
farshad





PostPosted: Wed Jul 04 05:34:01 CDT 2007 Top

SQL Server Developer >> view schemabinding Thanks




> > I once read somewhere that by creating a view with schemabinding which has
> > select on the table then you will not be able to delete the table
> > accidentally.
> >
> You can do this, but it's more a matter of a happy accident of the way
> things work rather than a "this is why schemabinding exists" kind of
> thing. I'd suggest just going for 1 per table - that way when you add
> a table, you don't have to alter the view, and if you want to drop,
> say 2 tables, you just drop their associated views rather than having
> to drop/modify the ONE BIG view.
>
> That being said, if you're determined to try to do it as ONE BIG view,
> I *think* the following would work:
>
> create view BigDaddyView with schemabinding as
> select COUNT(*) from table1
> union all
> select COUNT(*) from table2
> ..
> ..
> ..
> select COUNT(*) from tableN
>
> Damien
>
>
 
 
Damien





PostPosted: Thu Jul 05 01:26:17 CDT 2007 Top

SQL Server Developer >> view schemabinding

> > select on the table then you will not be able to delete the table
> > accidentally.
>
> You can do this, but it's more a matter of a happy accident of the way
> things work rather than a "this is why schemabinding exists" kind of
> thing. I'd suggest just going for 1 per table - that way when you add
> a table, you don't have to alter the view, and if you want to drop,
> say 2 tables, you just drop their associated views rather than having
> to drop/modify the ONE BIG view.
>
> That being said, if you're determined to try to do it as ONE BIG view,
> I *think* the following would work:
>
> create view BigDaddyView with schemabinding as
> select COUNT(*) from table1
> union all
> select COUNT(*) from table2
> .
> .
> .
> select COUNT(*) from tableN
>
> Damien

Of course, I should have mentioned the obvious bigger problem with the
"use one view for all tables" approach - it fails once you hit 256
tables.

Damien