How to improve the performance of a query?

I have a table called DMPD_Product_Lookup_Dom. It is a lookup table which contains values for certain fields of other tables in the database.
This takes long time to run.
Is there any way to improve performance of this query ??

SELECT
BNAD.Benefit_Admin_Cat_CD AS 'AdminCategory',
DOM1.Value_Description AS 'AdminCategoryDesc',
BNAD.Benefit_Component_Name_CD AS 'BenefitAdmin',
DOM2.Value_Description AS 'BenefitAdminDesc',
BNDT.Benefit_Rider_CD AS 'BenefitRider',
DOM3.Value_Description AS 'BenefitRiderDesc',
BNDT.Benefit_Exception_Ind AS 'Exception',
BNDT.Benefit_Detail_Desc_CD AS 'BenefitDetail',
DOM4.Value_Description AS 'BenefitDetailDesc',
DMCS.Cost_Share_Value_Type_CD AS 'CSValueType',
DOM5.Value_Description AS 'CSValueTypeDesc',
DMCS.Cost_Share_Value AS 'CS_Value',
DMCS.Cost_Share_Rule_Cat_CD AS 'CS_Rule_Cat_CD',
DOM6.Value_Description AS 'CS_Rule_Cat_Value',
BNCS.Cost_Share_Rule_Type_CD AS 'CS_Rule_Type_CD',
DOM7.Value_Description AS 'CSRuleTypeDesc',
BNCS.Cost_Share_Mbr_Family_Ind AS 'MemberOrFamily',
DOM8.Value_Description AS 'MemberOrFamilyDesc',
BNCS.Network_Ind AS 'NetworkInd'
FROM
prdtrk01..DMPD_Product_Lookup_Dom DOM1,
prdtrk01..DMPD_Product_Lookup_Dom DOM2,
prdtrk01..DMPD_Product_Lookup_Dom DOM3,
prdtrk01..DMPD_Product_Lookup_Dom DOM4,
prdtrk01..DMPD_Product_Lookup_Dom DOM5,
prdtrk01..DMPD_Product_Lookup_Dom DOM6,
prdtrk01..DMPD_Product_Lookup_Dom DOM7,
prdtrk01..DMPD_Product_Lookup_Dom DOM8,
prdtrk01..BNAD_Benefit_Admin BNAD,
prdtrk01..BNDT_Benefit_Detail BNDT,
prdtrk01..BNCS_Cost_Share_Rule BNCS,
prdtrk01..DMCS_Cost_Share_Dom DMCS
WHERE
BNAD.Benefit_Admin_ID = BNCS.Benefit_Admin_ID
AND BNDT.Benefit_Detail_ID = BNCS.Benefit_Detail_ID
AND DMCS.Cost_Share_Rule_ID = BNCS.Cost_Share_Rule_ID
AND DOM1.Product_Domain_Entity = "BNAD"
AND DOM1.Product_Attribute_Type = "Benefit_Admin_Cat_CD"
AND DOM1.Domain_Value = BNAD.Benefit_Admin_Cat_CD
AND DOM2.Product_Domain_Entity = "BNAD"
AND DOM2.Product_Attribute_Type = "Benefit_Component_Name_CD"
AND DOM2.Domain_Value = BNAD.Benefit_Component_Name_CD
AND DOM3.Product_Domain_Entity = "BNDT"
AND DOM3.Product_Attribute_Type = "Benefit_Rider_CD"
AND BNDT.Benefit_Rider_CD *= DOM3.Domain_Value
AND DOM4.Product_Domain_Entity = "BNDT"
AND DOM4.Product_Attribute_Type = "Benefit_Detail_Desc_CD"
AND DOM4.Domain_Value = BNDT.Benefit_Detail_Desc_CD
AND DOM5.Product_Domain_Entity = "DMCS"
AND DOM5.Product_Attribute_Type = "Cost_Share_Value_Type_CD"
AND DOM5.Domain_Value = DMCS.Cost_Share_Value_Type_CD
AND DOM6.Product_Domain_Entity = "DMCS"
AND DOM6.Product_Attribute_Type = "Cost_Share_Rule_Cat_CD"
AND DOM6.Domain_Value = DMCS.Cost_Share_Rule_Cat_CD
AND DOM7.Product_Domain_Entity = "BNCS"
AND DOM7.Product_Attribute_Type = "Cost_Share_Rule_Type_CD"
AND DOM7.Domain_Value = BNCS.Cost_Share_Rule_Type_CD
AND DOM8.Product_Domain_Entity = "BNCS"
AND DOM8.Product_Attribute_Type = "Cost_Share_Mbr_Family_Ind"
AND DOM8.Domain_Value = BNCS.Cost_Share_Mbr_Family_Ind
AND BNCS.Product_ID = @.Product_ID
ORDER BY
DOM1.Sort_Seq_No,
DOM1.Value_Description,
DOM2.Sort_Seq_No,
DOM2.Value_Description,
DOM3.Sort_Seq_No,
DOM3.Value_Description,
DOM4.Sort_Seq_No,
DOM4.Value_Description,
DOM5.Sort_Seq_No,
DOM5.Value_Description,
DOM6.Sort_Seq_No,
DOM6.Value_Description,
DOM7.Sort_Seq_No,
DOM7.Value_Description,
DOM8.Sort_Seq_No,
DOM8.Value_DescriptionDo you have indexes on these tables?|||

Quote:

Originally Posted by iburyak

Do you have indexes on these tables?

Yes, the tables mentioned have indexes. But i want to know is there any other way to improve the performance of this query.|||Unfortunately you didn't provide specific answer.
To help you I need to see all index compositions.
Some people think they have indexes but it could be that they are not the once that such query will use.

To view if your query uses indexes in SQL Query Analyzer go to Query Show Execution Plan.
Execute your query and point to each item and see if your indexes are actually used.|||well the performance can be boosted using the appropriate filters in the FROM clause itself. Try to use INNER and the other different appropriate joins in the clause.The default join is the CROSS join which is the worst join. So try to filter out the results as much as possible in the FROM clause. Definitely the performance will be boosted up to some instant.|||

Quote:

Originally Posted by abhishek8236

well the performance can be boosted using the appropriate filters in the FROM clause itself. Try to use INNER and the other different appropriate joins in the clause.The default join is the CROSS join which is the worst join. So try to filter out the results as much as possible in the FROM clause. Definitely the performance will be boosted up to some instant.

Thanks Everyone! The Problem has been resolved.

How to improve speed of query MSDE 2000

Hi All
I am having trouble with query timeouts in a VB6 program using MSDE 2000 (on
local PC)
It only happens when querying between certain dates ( surprisingly it occurs
when the dates are closer together and hence less records exists)
e.g if date range is 1/8/05 to 1/9/05 query doesn't timeout
if date range 25/8/05 to 1/9/05 it does?
code.....
sql = "select [prod_code], sum(qty) as amount, sum(qty * price) as myprice
from [idetail] where [inv_num] in (Select inv_num from Invoice where
inv_date between '" & Format(DT1.Value, "YYYY-MM-DD") & "' and '" &
Format(DT2.Value, "YYYY-MM-DD") & "') group by prod_code order by prod_code"
rs.Open sql, cn, adOpenKeyset, adLockReadOnly
I have no primary keys or indexes on the idetail table
Any ideas
Regards
Steve
hi Steve,
steve wrote:
> Hi All
> I am having trouble with query timeouts in a VB6 program using MSDE
> 2000 (on local PC)
> It only happens when querying between certain dates ( surprisingly it
> occurs when the dates are closer together and hence less records
> exists)
> e.g if date range is 1/8/05 to 1/9/05 query doesn't timeout
> if date range 25/8/05 to 1/9/05 it does?
> code.....
> sql = "select [prod_code], sum(qty) as amount, sum(qty * price) as
> myprice from [idetail] where [inv_num] in (Select inv_num from
> Invoice where inv_date between '" & Format(DT1.Value, "YYYY-MM-DD") &
> "' and '" & Format(DT2.Value, "YYYY-MM-DD") & "') group by prod_code
> order by prod_code"
> rs.Open sql, cn, adOpenKeyset, adLockReadOnly
> I have no primary keys or indexes on the idetail table
>
a proper indexing schema (as long as a proper primary key, which is always
usefull :D) should always help... but this should not be a related
problem... your actual plan should always be a table scan, followed by a
sort operation, but again, should not be related...
do you have actual lock pending on the table during the failing projections?
try executing
EXEC sp_lock
http://msdn.microsoft.com/library/de...la-lz_6cdn.asp
and
EXEC sp_who
http://msdn.microsoft.com/library/de...wa-wz_3v8v.asp
to determin eventual lock conditions..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

how to implement locking

Take for example ticketmaster where they lock a certain section, row and
even seat numbers for a duration..
I am assuming all this info may be in one table . How can one ensure row
level locking without SQL Server escalating it to some high level lock.
And even if a particular row is locked, does that mean that one can update
rows that are not locked
Trying to find a solution to do locking at a row level while still leaving
other rows for DML i.e select, updates,inserts,deletes
Thanks
On Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
wrote:
>Take for example ticketmaster where they lock a certain section, row and
>even seat numbers for a duration..
>I am assuming all this info may be in one table . How can one ensure row
>level locking without SQL Server escalating it to some high level lock.
>And even if a particular row is locked, does that mean that one can update
>rows that are not locked
>Trying to find a solution to do locking at a row level while still leaving
>other rows for DML i.e select, updates,inserts,deletes
You can always add an IsLocked field to the table, SQLServer will
never escalate that, and it won't block other operations, and will
even still be there if you reboot the server!
J.
|||I think we may get into design here. How will I model the data ? Take for
example Ticketmaster.
And consider high concurrency needed and not being able to block one
another.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:me34h1lcijtg0h301o85u0cj84ds2dbrfa@.4ax.com...
> On Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
> wrote:
> You can always add an IsLocked field to the table, SQLServer will
> never escalate that, and it won't block other operations, and will
> even still be there if you reboot the server!
> J.
>
|||If they have the proper indexes and WHERE clauses they will have to lock
lots of rows in the single transaction before it will escalate to table.
But if you want to ensure it never escalates you can add a dummy row and
have a connection lock it all the time. As long as there is another lock in
the table at any level you can not escalate to a table lock. Of coarse that
means if you do scans you might have an issue but if you have the right
indexes and such it shouldn't be an issue.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Take for example ticketmaster where they lock a certain section, row and
> even seat numbers for a duration..
> I am assuming all this info may be in one table . How can one ensure row
> level locking without SQL Server escalating it to some high level lock.
> And even if a particular row is locked, does that mean that one can update
> rows that are not locked
> Trying to find a solution to do locking at a row level while still leaving
> other rows for DML i.e select, updates,inserts,deletes
> Thanks
>
|||Locking a dummy row is a clever way of avoiding lock escalation on a
particular table. However, please do not overuse this. Having long running
open transaction is not desirable. It should only be used as a workaround
for heavy contention issues as a result of lock escalation. Disabling lock
escalation in general may result in slower performance.
By default, SQL Server does not escalate unless more than 5000 locks are
obtained in the current statement for a particular table. If the query plan
does not contain a range scan, then most likely you will not encounter
escalation. If it does have a range scan, then you could analyze if that
range scan has a chance of encountering a lot of rows.
If you do need to implement the dummy row locking idea, be sure not to do
anything else in the transaction which holds the long term lock. So it
should be something like this:
-- if you do not want escalated X table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table where PrimyarKeyColumn = dummy_row_key_value
wait for delay ...
commit
-- if you do not want escalated S table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table with (UPDLOCK) where PrimyarKeyColumn =
dummy_row_key_value
wait for delay ...
commit
If you are planning to use SQL Server 2005, then the new
Read-Committed-Snapshot-Isolation feature guarantees that there is no S
table lock for reads.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ON%23waYCrFHA.3640@.tk2msftngp13.phx.gbl...
> If they have the proper indexes and WHERE clauses they will have to lock
> lots of rows in the single transaction before it will escalate to table.
> But if you want to ensure it never escalates you can add a dummy row and
> have a connection lock it all the time. As long as there is another lock
> in the table at any level you can not escalate to a table lock. Of coarse
> that means if you do scans you might have an issue but if you have the
> right indexes and such it shouldn't be an issue.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
>

how to implement locking

Take for example ticketmaster where they lock a certain section, row and
even seat numbers for a duration..
I am assuming all this info may be in one table . How can one ensure row
level locking without SQL Server escalating it to some high level lock.
And even if a particular row is locked, does that mean that one can update
rows that are not locked
Trying to find a solution to do locking at a row level while still leaving
other rows for DML i.e select, updates,inserts,deletes
ThanksOn Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
wrote:
>Take for example ticketmaster where they lock a certain section, row and
>even seat numbers for a duration..
>I am assuming all this info may be in one table . How can one ensure row
>level locking without SQL Server escalating it to some high level lock.
>And even if a particular row is locked, does that mean that one can update
>rows that are not locked
>Trying to find a solution to do locking at a row level while still leaving
>other rows for DML i.e select, updates,inserts,deletes
You can always add an IsLocked field to the table, SQLServer will
never escalate that, and it won't block other operations, and will
even still be there if you reboot the server!
J.|||I think we may get into design here. How will I model the data ? Take for
example Ticketmaster.
And consider high concurrency needed and not being able to block one
another.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:me34h1lcijtg0h301o85u0cj84ds2dbrfa@.
4ax.com...
> On Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
> wrote:
> You can always add an IsLocked field to the table, SQLServer will
> never escalate that, and it won't block other operations, and will
> even still be there if you reboot the server!
> J.
>|||If they have the proper indexes and WHERE clauses they will have to lock
lots of rows in the single transaction before it will escalate to table.
But if you want to ensure it never escalates you can add a dummy row and
have a connection lock it all the time. As long as there is another lock in
the table at any level you can not escalate to a table lock. Of coarse that
means if you do scans you might have an issue but if you have the right
indexes and such it shouldn't be an issue.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Take for example ticketmaster where they lock a certain section, row and
> even seat numbers for a duration..
> I am assuming all this info may be in one table . How can one ensure row
> level locking without SQL Server escalating it to some high level lock.
> And even if a particular row is locked, does that mean that one can update
> rows that are not locked
> Trying to find a solution to do locking at a row level while still leaving
> other rows for DML i.e select, updates,inserts,deletes
> Thanks
>|||Locking a dummy row is a clever way of avoiding lock escalation on a
particular table. However, please do not overuse this. Having long running
open transaction is not desirable. It should only be used as a workaround
for heavy contention issues as a result of lock escalation. Disabling lock
escalation in general may result in slower performance.
By default, SQL Server does not escalate unless more than 5000 locks are
obtained in the current statement for a particular table. If the query plan
does not contain a range scan, then most likely you will not encounter
escalation. If it does have a range scan, then you could analyze if that
range scan has a chance of encountering a lot of rows.
If you do need to implement the dummy row locking idea, be sure not to do
anything else in the transaction which holds the long term lock. So it
should be something like this:
-- if you do not want escalated X table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table where PrimyarKeyColumn = dummy_row_key_value
wait for delay ...
commit
-- if you do not want escalated S table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table with (UPDLOCK) where PrimyarKeyColumn =
dummy_row_key_value
wait for delay ...
commit
If you are planning to use SQL Server 2005, then the new
Read-Committed-Snapshot-Isolation feature guarantees that there is no S
table lock for reads.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ON%23waYCrFHA.3640@.tk2msftngp13.phx.gbl...
> If they have the proper indexes and WHERE clauses they will have to lock
> lots of rows in the single transaction before it will escalate to table.
> But if you want to ensure it never escalates you can add a dummy row and
> have a connection lock it all the time. As long as there is another lock
> in the table at any level you can not escalate to a table lock. Of coarse
> that means if you do scans you might have an issue but if you have the
> right indexes and such it shouldn't be an issue.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
>

how to implement locking

Take for example ticketmaster where they lock a certain section, row and
even seat numbers for a duration..
I am assuming all this info may be in one table . How can one ensure row
level locking without SQL Server escalating it to some high level lock.
And even if a particular row is locked, does that mean that one can update
rows that are not locked
Trying to find a solution to do locking at a row level while still leaving
other rows for DML i.e select, updates,inserts,deletes
ThanksOn Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
wrote:
>Take for example ticketmaster where they lock a certain section, row and
>even seat numbers for a duration..
>I am assuming all this info may be in one table . How can one ensure row
>level locking without SQL Server escalating it to some high level lock.
>And even if a particular row is locked, does that mean that one can update
>rows that are not locked
>Trying to find a solution to do locking at a row level while still leaving
>other rows for DML i.e select, updates,inserts,deletes
You can always add an IsLocked field to the table, SQLServer will
never escalate that, and it won't block other operations, and will
even still be there if you reboot the server!
J.|||I think we may get into design here. How will I model the data ? Take for
example Ticketmaster.
And consider high concurrency needed and not being able to block one
another.
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:me34h1lcijtg0h301o85u0cj84ds2dbrfa@.4ax.com...
> On Sun, 28 Aug 2005 11:11:59 -0700, "Hassan" <hassanboy@.hotmail.com>
> wrote:
>>Take for example ticketmaster where they lock a certain section, row and
>>even seat numbers for a duration..
>>I am assuming all this info may be in one table . How can one ensure row
>>level locking without SQL Server escalating it to some high level lock.
>>And even if a particular row is locked, does that mean that one can update
>>rows that are not locked
>>Trying to find a solution to do locking at a row level while still leaving
>>other rows for DML i.e select, updates,inserts,deletes
> You can always add an IsLocked field to the table, SQLServer will
> never escalate that, and it won't block other operations, and will
> even still be there if you reboot the server!
> J.
>|||If they have the proper indexes and WHERE clauses they will have to lock
lots of rows in the single transaction before it will escalate to table.
But if you want to ensure it never escalates you can add a dummy row and
have a connection lock it all the time. As long as there is another lock in
the table at any level you can not escalate to a table lock. Of coarse that
means if you do scans you might have an issue but if you have the right
indexes and such it shouldn't be an issue.
Andrew J. Kelly SQL MVP
"Hassan" <hassanboy@.hotmail.com> wrote in message
news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Take for example ticketmaster where they lock a certain section, row and
> even seat numbers for a duration..
> I am assuming all this info may be in one table . How can one ensure row
> level locking without SQL Server escalating it to some high level lock.
> And even if a particular row is locked, does that mean that one can update
> rows that are not locked
> Trying to find a solution to do locking at a row level while still leaving
> other rows for DML i.e select, updates,inserts,deletes
> Thanks
>|||Locking a dummy row is a clever way of avoiding lock escalation on a
particular table. However, please do not overuse this. Having long running
open transaction is not desirable. It should only be used as a workaround
for heavy contention issues as a result of lock escalation. Disabling lock
escalation in general may result in slower performance.
By default, SQL Server does not escalate unless more than 5000 locks are
obtained in the current statement for a particular table. If the query plan
does not contain a range scan, then most likely you will not encounter
escalation. If it does have a range scan, then you could analyze if that
range scan has a chance of encountering a lot of rows.
If you do need to implement the dummy row locking idea, be sure not to do
anything else in the transaction which holds the long term lock. So it
should be something like this:
-- if you do not want escalated X table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table where PrimyarKeyColumn = dummy_row_key_value
wait for delay ...
commit
-- if you do not want escalated S table lock:
set transaction isolation level repeatable read
begin tran
select ... from my_table with (UPDLOCK) where PrimyarKeyColumn =dummy_row_key_value
wait for delay ...
commit
If you are planning to use SQL Server 2005, then the new
Read-Committed-Snapshot-Isolation feature guarantees that there is no S
table lock for reads.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ON%23waYCrFHA.3640@.tk2msftngp13.phx.gbl...
> If they have the proper indexes and WHERE clauses they will have to lock
> lots of rows in the single transaction before it will escalate to table.
> But if you want to ensure it never escalates you can add a dummy row and
> have a connection lock it all the time. As long as there is another lock
> in the table at any level you can not escalate to a table lock. Of coarse
> that means if you do scans you might have an issue but if you have the
> right indexes and such it shouldn't be an issue.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Hassan" <hassanboy@.hotmail.com> wrote in message
> news:%23RDk8v$qFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> Take for example ticketmaster where they lock a certain section, row and
>> even seat numbers for a duration..
>> I am assuming all this info may be in one table . How can one ensure row
>> level locking without SQL Server escalating it to some high level lock.
>> And even if a particular row is locked, does that mean that one can
>> update rows that are not locked
>> Trying to find a solution to do locking at a row level while still
>> leaving other rows for DML i.e select, updates,inserts,deletes
>> Thanks
>

How to hide table columns for users without permission in SQL Tool

Hi,
I already set select permission to certain columns in a table for a user.
BUt the user still can view all the columns in the Enterprise Manager/ SQL
Mgt Studio. HOw to hide those columns without permission ?
Hi
If restrict access to the table by use stored procedures then the user does
not need permissions on the table directly. You can also be granular to
column level what permissions are granted.
Not giving them access to Enterprise Manager may also be an idea.
John
"Wan" wrote:

> Hi,
> I already set select permission to certain columns in a table for a user.
> BUt the user still can view all the columns in the Enterprise Manager/ SQL
> Mgt Studio. HOw to hide those columns without permission ?
|||Hi
Just to add, you may not exclude the user seeing the existance of the column
even if you have denied them permissions to see the data.
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> If restrict access to the table by use stored procedures then the user does
> not need permissions on the table directly. You can also be granular to
> column level what permissions are granted.
> Not giving them access to Enterprise Manager may also be an idea.
> John
> "Wan" wrote:

How to hide table columns for users without permission in SQL Tool

Hi,
I already set select permission to certain columns in a table for a user.
BUt the user still can view all the columns in the Enterprise Manager/ SQL
Mgt Studio. HOw to hide those columns without permission ?Hi
If restrict access to the table by use stored procedures then the user does
not need permissions on the table directly. You can also be granular to
column level what permissions are granted.
Not giving them access to Enterprise Manager may also be an idea.
John
"Wan" wrote:
> Hi,
> I already set select permission to certain columns in a table for a user.
> BUt the user still can view all the columns in the Enterprise Manager/ SQL
> Mgt Studio. HOw to hide those columns without permission ?|||Hi
Just to add, you may not exclude the user seeing the existance of the column
even if you have denied them permissions to see the data.
John
"John Bell" wrote:
> Hi
> If restrict access to the table by use stored procedures then the user does
> not need permissions on the table directly. You can also be granular to
> column level what permissions are granted.
> Not giving them access to Enterprise Manager may also be an idea.
> John
> "Wan" wrote:
> > Hi,
> > I already set select permission to certain columns in a table for a user.
> > BUt the user still can view all the columns in the Enterprise Manager/ SQL
> > Mgt Studio. HOw to hide those columns without permission ?

How to hide table columns for users without permission in SQL Tool

Hi,
I already set select permission to certain columns in a table for a user.
BUt the user still can view all the columns in the Enterprise Manager/ SQL
Mgt Studio. HOw to hide those columns without permission ?Hi
If restrict access to the table by use stored procedures then the user does
not need permissions on the table directly. You can also be granular to
column level what permissions are granted.
Not giving them access to Enterprise Manager may also be an idea.
John
"Wan" wrote:

> Hi,
> I already set select permission to certain columns in a table for a user.
> BUt the user still can view all the columns in the Enterprise Manager/ SQ
L
> Mgt Studio. HOw to hide those columns without permission ?