_WA_ indexes in sysindexes

Hi
I was writing script to defrag my indexes on a regular basis.
I was checking the sysindexes table and found
_WA_ indexes and I do no see a corresponding object for the sysobjects
WHat are these indexes?
Mangesh
These are statistics rather than indexes. You can exclude statistics and
hyhothetical indexes (created by the Index Tuning Wizard) rows in sysindexes
using INDEXPROPERTY:
SELECT OBJECT_NAME(id), name
FROM sysindexes
WHERE
INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id, name, 'IsHypothetical') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:0C87800A-4467-4AD0-BCCD-DD822FFF0649@.microsoft.com...
> Hi
> I was writing script to defrag my indexes on a regular basis.
> I was checking the sysindexes table and found
> _WA_ indexes and I do no see a corresponding object for the sysobjects
> WHat are these indexes?
> Mangesh

_WA_ indexes in sysindexes

Hi
I was writing script to defrag my indexes on a regular basis.
I was checking the sysindexes table and found
_WA_ indexes and I do no see a corresponding object for the sysobjects
WHat are these indexes?
MangeshThese are statistics rather than indexes. You can exclude statistics and
hyhothetical indexes (created by the Index Tuning Wizard) rows in sysindexes
using INDEXPROPERTY:
SELECT OBJECT_NAME(id), name
FROM sysindexes
WHERE
INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND
INDEXPROPERTY(id, name, 'IsHypothetical') = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:0C87800A-4467-4AD0-BCCD-DD822FFF0649@.microsoft.com...
> Hi
> I was writing script to defrag my indexes on a regular basis.
> I was checking the sysindexes table and found
> _WA_ indexes and I do no see a corresponding object for the sysobjects
> WHat are these indexes?
> Mangesh

[SQL2000] permissions to use view based on tables from many databases

Hi
I have two databases: Customers and Operations. In Customers database I have
made a view based on a few tables from both Customers and Operations (left
join - customers without any operations). In the same database (Customers) I
have created a stored procedure based on the view. Finally I'd like to give
to some users permission only to exec the stored procedure.
Have I to add the users to Customers? If yes, please describe me how to
limit the users privileges only to execution the stored procedure (no rights
to open tables or view from Customers).
Regards,
Grzegorz
Ps. I had sent the post on microsoft.public.sqlserver.security, but I had no
answer.By default when you add a user they do not have any permissions to do
anything. So just make sure you don't add them to any of the server or
database roles. Then simply GRANT them execute permission on that sp.
Andrew J. Kelly SQL MVP
"Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
news:dteimk$1b7$1@.inews.gazeta.pl...
> Hi
> I have two databases: Customers and Operations. In Customers database I
> have made a view based on a few tables from both Customers and Operations
> (left join - customers without any operations). In the same database
> (Customers) I have created a stored procedure based on the view. Finally
> I'd like to give to some users permission only to exec the stored
> procedure.
> Have I to add the users to Customers? If yes, please describe me how to
> limit the users privileges only to execution the stored procedure (no
> rights to open tables or view from Customers).
> Regards,
> Grzegorz
> Ps. I had sent the post on microsoft.public.sqlserver.security, but I had
> no answer.|||Well, it works fine in situations when all tables are in the same database,
but it doesn't work when tables are in two databases. If user have no rights
to read source table from other database SQL Server shows error:
"SELECT permission denied on object 'CustomersData', database 'Customers',
owner 'dbo'."
Grzegorz
Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
wiadomoci news:%23Iia6cuNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> By default when you add a user they do not have any permissions to do
> anything. So just make sure you don't add them to any of the server or
> database roles. Then simply GRANT them execute permission on that sp.
> --
> Andrew J. Kelly SQL MVP|||Are the objects owned by the same owner in both db's? If so you may have to
specify rights on the other tables. Is Cross database Ownership chaining
turned on?
http://support.microsoft.com/?kbid=810474
Andrew J. Kelly SQL MVP
"Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
news:dtf589$5h3$1@.inews.gazeta.pl...
> Well, it works fine in situations when all tables are in the same
> database, but it doesn't work when tables are in two databases. If user
> have no rights to read source table from other database SQL Server shows
> error:
> "SELECT permission denied on object 'CustomersData', database 'Customers',
> owner 'dbo'."
> Grzegorz
>
> Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
> wiadomoci news:%23Iia6cuNGHA.2336@.TK2MSFTNGP12.phx.gbl...
>|||Thanks, it works.
Grzegorz
Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
wiadomoci news:O07NN2uNGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Are the objects owned by the same owner in both db's? If so you may have
> to specify rights on the other tables. Is Cross database Ownership
> chaining turned on?
> http://support.microsoft.com/?kbid=810474
> --
> Andrew J. Kelly SQL MVP
>
> "Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
> news:dtf589$5h3$1@.inews.gazeta.pl...
>|||It is a in SQL that permission is check on the first called element i.e.
in case of stored procedure the there's a check made if the user has
the right to execute a procedure. Thus it's possiblie to allow a user to
execure a procedure and e.g. display a subset of data without allowing
to read tables content. Up to sp3 it was valid for the whole server.
From sp3 you've got to set cross-databse chaining explicitly.
Grzegorz Danowski wrote:
> Hi
> I have two databases: Customers and Operations. In Customers database I
> have made a view based on a few tables from both Customers and
> Operations (left join - customers without any operations). In the same
> database (Customers) I have created a stored procedure based on the
> view. Finally I'd like to give to some users permission only to exec the
> stored procedure.
> Have I to add the users to Customers? If yes, please describe me how to
> limit the users privileges only to execution the stored procedure (no
> rights to open tables or view from Customers).
> Regards,
> Grzegorz
> Ps. I had sent the post on microsoft.public.sqlserver.security, but I
> had no answer.

[SQL2000] permissions to use view based on tables from many databases

Hi

I have two databases: Customers and Operations. In Customers database I have
made a view based on a few tables from both Customers and Operations (left
join - customers without any operations). In the same database (Customers) I
have created a stored procedure based on the view. Finally I'd like to give
to some users permission only to exec the stored procedure.

Have I to add the users to Customers? If yes, please describe me how to
limit the users privileges only to execution the stored procedure (no rights
to open tables or view from Customers).

Regards,
Grzegorz

Ps. I had sent the post on microsoft.public.sqlserver.security, but I had no
answer.By default when you add a user they do not have any permissions to do
anything. So just make sure you don't add them to any of the server or
database roles. Then simply GRANT them execute permission on that sp.

--
Andrew J. Kelly SQL MVP

"Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
news:dteimk$1b7$1@.inews.gazeta.pl...
> Hi
> I have two databases: Customers and Operations. In Customers database I
> have made a view based on a few tables from both Customers and Operations
> (left join - customers without any operations). In the same database
> (Customers) I have created a stored procedure based on the view. Finally
> I'd like to give to some users permission only to exec the stored
> procedure.
> Have I to add the users to Customers? If yes, please describe me how to
> limit the users privileges only to execution the stored procedure (no
> rights to open tables or view from Customers).
> Regards,
> Grzegorz
> Ps. I had sent the post on microsoft.public.sqlserver.security, but I had
> no answer.|||Well, it works fine in situations when all tables are in the same database,
but it doesn't work when tables are in two databases. If user have no rights
to read source table from other database SQL Server shows error:
"SELECT permission denied on object 'CustomersData', database 'Customers',
owner 'dbo'."

Grzegorz

Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
wiadomoci news:%23Iia6cuNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> By default when you add a user they do not have any permissions to do
> anything. So just make sure you don't add them to any of the server or
> database roles. Then simply GRANT them execute permission on that sp.
> --
> Andrew J. Kelly SQL MVP|||Are the objects owned by the same owner in both db's? If so you may have to
specify rights on the other tables. Is Cross database Ownership chaining
turned on?

http://support.microsoft.com/?kbid=810474

--
Andrew J. Kelly SQL MVP

"Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
news:dtf589$5h3$1@.inews.gazeta.pl...
> Well, it works fine in situations when all tables are in the same
> database, but it doesn't work when tables are in two databases. If user
> have no rights to read source table from other database SQL Server shows
> error:
> "SELECT permission denied on object 'CustomersData', database 'Customers',
> owner 'dbo'."
> Grzegorz
>
> Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
> wiadomoci news:%23Iia6cuNGHA.2336@.TK2MSFTNGP12.phx.gbl...
>> By default when you add a user they do not have any permissions to do
>> anything. So just make sure you don't add them to any of the server or
>> database roles. Then simply GRANT them execute permission on that sp.
>>
>> --
>> Andrew J. Kelly SQL MVP|||Thanks, it works.
Grzegorz

Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
wiadomoci news:O07NN2uNGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Are the objects owned by the same owner in both db's? If so you may have
> to specify rights on the other tables. Is Cross database Ownership
> chaining turned on?
> http://support.microsoft.com/?kbid=810474
> --
> Andrew J. Kelly SQL MVP
>
> "Grzegorz Danowski" <gdn__na@.serwerze__poczta.onet.pl> wrote in message
> news:dtf589$5h3$1@.inews.gazeta.pl...
>> Well, it works fine in situations when all tables are in the same
>> database, but it doesn't work when tables are in two databases. If user
>> have no rights to read source table from other database SQL Server shows
>> error:
>> "SELECT permission denied on object 'CustomersData', database
>> 'Customers', owner 'dbo'."
>>
>> Grzegorz
>>
>>
>> Uytkownik "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> napisa w
>> wiadomoci news:%23Iia6cuNGHA.2336@.TK2MSFTNGP12.phx.gbl...
>>> By default when you add a user they do not have any permissions to do
>>> anything. So just make sure you don't add them to any of the server or
>>> database roles. Then simply GRANT them execute permission on that sp.
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>|||It is a in SQL that permission is check on the first called element i.e.
in case of stored procedure the there's a check made if the user has
the right to execute a procedure. Thus it's possiblie to allow a user to
execure a procedure and e.g. display a subset of data without allowing
to read tables content. Up to sp3 it was valid for the whole server.
From sp3 you've got to set cross-databse chaining explicitly.

Grzegorz Danowski wrote:
> Hi
> I have two databases: Customers and Operations. In Customers database I
> have made a view based on a few tables from both Customers and
> Operations (left join - customers without any operations). In the same
> database (Customers) I have created a stored procedure based on the
> view. Finally I'd like to give to some users permission only to exec the
> stored procedure.
> Have I to add the users to Customers? If yes, please describe me how to
> limit the users privileges only to execution the stored procedure (no
> rights to open tables or view from Customers).
> Regards,
> Grzegorz
> Ps. I had sent the post on microsoft.public.sqlserver.security, but I
> had no answer.

[newbie] Execute SQL task bypassed, why ?

Hi!

I meet a strange behaviour which is probably caused by my SSIS newbie nature.

I have a Execute SQL Task, used to drop and create some temporary tables. It works when invoked manually.

I have chained a Data Flow Task behind, but when I launch the whole process, although the Execute SQL tasks goes green, it is not executed (then the Data Flow fails because the required tables are missing).

Would anyone have any hint on why the execute sql task seems to be bypassed ?

kind regards

Thibaut

hi Thibaut,

how odd! Have you defined a log file for your package? If so, what kind of info is providing to you?

|||

Is that a package that you strated from scratch? If not; make sure there is not an expression or package configuration that changes the SQL Statement or the connection strings. If you are getting green on the SQL task I bet the object is actually being created, perhaps in the wrong side (server, schema, DB, etc)

Rafael Salas

|||I am now restarting the package from scratch, and making it configurable ("keeping it in the dark"). I'll report back if I meet the same issue again.

thanks!|||Hi!

I finally found out what is happening. The Data flow task is using a XML source to load the data into tables which are created by the Execute SQL task.

The XML source was trying to validate the schema provided at run time against tables which are not created yet... I just disabled the validation on the XML source and everything went fine.

thanks for all the replies.

cheers!

Thibaut

[Microsoft][ODBC SQL Server Driver]Timeout expired

Hi:
I am getting this error when the .asp page tries to connect to the DB server.
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver]Timeout expired
The web server is Windows 2000 Advanced Server, Dual CPU with
Hyperthreading, P4 with 2 GB RAM. The database is SQL 2000 Standard Edition
with SP3 and Cumulative Patch for Microsoft SQL Server (815495)MS03-031
installed on a remote server with the same config. I have also tried all the
possible variations like increasing the VBscript timeout in IIS from 90 to
300 and Global transaction timeout in COM+ applications from 60 to 300. This
however is not helping either. When I check the anonymous concurrent sessions
I find atleast 950 connections at any given time. Also installed MDAC 2.7
Refresh on both the servers.
Can any one help me on this please.
Mitul Z.
Hi Mitul,
This error message is somewhat a generic error message for ODBC. Here are
some ideas for troubleshooting the issue.
1. From the server hosting your web server, can you connect to the SQL
Server using Query Analyzer? If you do not have Query Analyzer available,
you can test connectivity by creating a test ODBC DSN and using the test
facility. Can you connect to the SQL Server with these techniques?
2. If you can connect using Query Analyzer or an ODBC test DSN, then the
issue is with the asp page itself. If you cannot connect to the SQL Server
using these tests, the problem is with ODBC configuration or a network
level problem is at play.
If you suspect a network problem is at play, Network Monitor traces will be
very helpful for your troubleshooting. That will at least allow you to
know if your connection request is reaching the SQL Server.
Please let me know if this information helps.
Thank you.
Gary
This posting is provided "AS IS" with no warranties, and confers no rights.

[Microsoft][ODBC SQL Server Driver]Timeout expired

Hi:
I am getting this error when the .asp page tries to connect to the DB server
.
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver]Timeout expired
The web server is Windows 2000 Advanced Server, Dual CPU with
Hyperthreading, P4 with 2 GB RAM. The database is SQL 2000 Standard Edition
with SP3 and Cumulative Patch for Microsoft SQL Server (815495)MS03-031
installed on a remote server with the same config. I have also tried all the
possible variations like increasing the VBscript timeout in IIS from 90 to
300 and Global transaction timeout in COM+ applications from 60 to 300. This
however is not helping either. When I check the anonymous concurrent session
s
I find atleast 950 connections at any given time. Also installed MDAC 2.7
Refresh on both the servers.
Can any one help me on this please.
Mitul Z.Hi Mitul,
This error message is somewhat a generic error message for ODBC. Here are
some ideas for troubleshooting the issue.
1. From the server hosting your web server, can you connect to the SQL
Server using Query Analyzer? If you do not have Query Analyzer available,
you can test connectivity by creating a test ODBC DSN and using the test
facility. Can you connect to the SQL Server with these techniques?
2. If you can connect using Query Analyzer or an ODBC test DSN, then the
issue is with the asp page itself. If you cannot connect to the SQL Server
using these tests, the problem is with ODBC configuration or a network
level problem is at play.
If you suspect a network problem is at play, Network Monitor traces will be
very helpful for your troubleshooting. That will at least allow you to
know if your connection request is reaching the SQL Server.
Please let me know if this information helps.
Thank you.
Gary
This posting is provided "AS IS" with no warranties, and confers no rights.

[Help]SQLServer Logon Failure ....

Hi!
I have a SQL server which version is SQL Server 8.0 and I want to change
this server's windows logon password. But, when I did then restart sql
service, I get this error message :"SQL Server Logon Failure". Is there any
way to change this password?
Registration properties : Windows authentication
Authentication mode : SQL Server and Windows
Best Regards,
AresChange the service login info - check
http://www.techgroup21.com/subpage.asp?subnavID=114.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"ares" <aresATasia-minor.info> wrote in message
news:uRCSkJkxEHA.2580@.TK2MSFTNGP10.phx.gbl...
> Hi!
> I have a SQL server which version is SQL Server 8.0 and I want to change
> this server's windows logon password. But, when I did then restart sql
> service, I get this error message :"SQL Server Logon Failure". Is there
any
> way to change this password?
> Registration properties : Windows authentication
> Authentication mode : SQL Server and Windows
> Best Regards,
> Ares
>
>|||Thanks for your help.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OiePlJmxEHA.1300@.TK2MSFTNGP14.phx.gbl...
> Change the service login info - check
> http://www.techgroup21.com/subpage.asp?subnavID=114.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
> "ares" <aresATasia-minor.info> wrote in message
> news:uRCSkJkxEHA.2580@.TK2MSFTNGP10.phx.gbl...
> any
>

[Help] Snapshot replication & Full Text Indexing

Hi
I have setup snapshot replication in one of my sql database. The article
which i publish, has full text index enabled in the subscription db. So when
the initial snap shot is applied, i get a error message saying "cannot drop
index PK_<<table name>> because it enforces full text key. It really drives
me crazy.
Here are my questions
1. In the Artcile properties, page i specify, under the name conflict
section, leave the table un changed. Then why is it that sql server tries
to drop the table and recreate it.(i checked in the sch table, what ever
option i specify, it is adament, it wants to recreate the table).
2. Is there a way, to publish full text indexed tables, without the initial
snapshot failing ?
I am in "Developer in Distress" mode, can anyone help ?
Thank You
Shrinivas
1 & 2) this should work, could you post the schema of this problem table
here?
"shrini" <senor20@.hotmail.com> wrote in message
news:%23R4xv8vJEHA.620@.tk2msftngp13.phx.gbl...
> Hi
> I have setup snapshot replication in one of my sql database. The article
> which i publish, has full text index enabled in the subscription db. So
when
> the initial snap shot is applied, i get a error message saying "cannot
drop
> index PK_<<table name>> because it enforces full text key. It really
drives
> me crazy.
> Here are my questions
> 1. In the Artcile properties, page i specify, under the name conflict
> section, leave the table un changed. Then why is it that sql server tries
> to drop the table and recreate it.(i checked in the sch table, what ever
> option i specify, it is adament, it wants to recreate the table).
> 2. Is there a way, to publish full text indexed tables, without the
initial
> snapshot failing ?
> I am in "Developer in Distress" mode, can anyone help ?
> Thank You
> Shrinivas
>
|||Hi Hilary
I managed to work around this, by dropping the Full Text index before
applying the snap (by specifying sql in the snap shot tab of properties) and
recreating the index after the snap shot is created. I think this is the
possible way for doing this. Thanks for your reply.
Shrini
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:e3KduPFKEHA.2808@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> 1 & 2) this should work, could you post the schema of this problem table
> here?
> "shrini" <senor20@.hotmail.com> wrote in message
> news:%23R4xv8vJEHA.620@.tk2msftngp13.phx.gbl...
> when
> drop
> drives
tries
> initial
>

@@servername gives old name

Hi
I have changed mycomputername from test1 to test2
I can connect to sql server 2000 in query analyzer using test2 name , but
when I give query
select @.@.servername is gives old name test1
why this happens?
ThanksChanging the servername is not only a thing on windows, there are entries
you have to make via stored procedure on the systables, what does the table
sysserver tell (located in master)
HTH, Jens Suessmeyer.
"AM" <anonymous@.examnotes.net> schrieb im Newsbeitrag
news:%23$T53ICTFHA.3696@.TK2MSFTNGP15.phx.gbl...
> Hi
> I have changed mycomputername from test1 to test2
> I can connect to sql server 2000 in query analyzer using test2 name , but
> when I give query
> select @.@.servername is gives old name test1
> why this happens?
> Thanks
>|||Changing the name of your server does not automatically change the name of t
he
SQL Server instance. Changing the name of a server running SQL Server is a
significant undertaking and should not be done without considerable forethou
ght.
Here's a link on the Microsoft KB that might help:
http://support.microsoft.com/defaul...kb;en-us;303774
Of course, this doesn't account for all applications that will have to chang
e to
point to the new server.
Thomas
"AM" <anonymous@.examnotes.net> wrote in message
news:%23$T53ICTFHA.3696@.TK2MSFTNGP15.phx.gbl...
> Hi
> I have changed mycomputername from test1 to test2
> I can connect to sql server 2000 in query analyzer using test2 name , but
> when I give query
> select @.@.servername is gives old name test1
> why this happens?
> Thanks
>|||Execute the following on your server:
sp_dropserver <old_name>
go
sp_addserver <new_name>
go
When you rename a server, it doesn't update the master..sysservers table,
which is where @.@.servername gets its value. In SQL Server Books Online go
to index and type "Renaming servers" and you'll get a good topic discussing
it.
Mike
"AM" <anonymous@.examnotes.net> wrote in message
news:%23$T53ICTFHA.3696@.TK2MSFTNGP15.phx.gbl...
> Hi
> I have changed mycomputername from test1 to test2
> I can connect to sql server 2000 in query analyzer using test2 name , but
> when I give query
> select @.@.servername is gives old name test1
> why this happens?
> Thanks
>|||http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AM" <anonymous@.examnotes.net> wrote in message news:%23$T53ICTFHA.3696@.TK2MSFTNGP15.ph
x.gbl...
> Hi
> I have changed mycomputername from test1 to test2
> I can connect to sql server 2000 in query analyzer using test2 name , but
> when I give query
> select @.@.servername is gives old name test1
> why this happens?
> Thanks
>