Hi,
1. I have an Access ADP on SQL2000, uaing Windows users and groups..
2. Individual users with granted permissions work OK.
3. However, if I drop the individual users and try to uses the Windows
groups which contain them with same permissions granted, the app cannot make
connection.
4. Have revoked logins, re-added logins, database grants, and permissions,
but to no avail.
Any suggestions on what to look for?
Thanks.
AlanCan you make successfull connections from Query Analyser or OSQL.exe based
upon the group ?
Is the group a local group or domain based group?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin,
Thanks for your reply.
1. I can't connect with query analyzer, logging on to users's machine with
user's login but only his group login and group database access existing in
SQLServer.
2. User's group is a Universal domain group.
Using SQL@.000.
Useer: WinXP
OS: Win2000Server
DB Machine: Win2000 Server.
Regards,
Alan
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:0WvCdgfxEHA.3640@.cpmsftngxa10.phx.gbl...
> Can you make successfull connections from Query Analyser or OSQL.exe based
> upon the group ?
> Is the group a local group or domain based group?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Ah. The problem may be the Universal Group.. There was a fix for this. Try
this update.
825042 FIX: SQL Server Jobs That Are Owned by Non-sysadmin Users May Not
Start
http://support.microsoft.com/?id=825042
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin,
Thanks for your reply.
This sounds like it applies to jobs only, not regular database accesss. Do
you know if it applies to database access as well.
I'd like to avoid hotfixes on client's computers as well, plus I don't know
if they have installed required Win2000 SP4.
Thanks.
Alan
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:7AkfzfpxEHA.768@.cpmsftngxa10.phx.gbl...
> Ah. The problem may be the Universal Group.. There was a fix for this.
Try
> this update.
> 825042 FIX: SQL Server Jobs That Are Owned by Non-sysadmin Users May Not
> Start
> http://support.microsoft.com/?id=825042
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Its' been some time since I did testing, but I don't think we enumerate
Universal Groups only Domain Global Groups.
Try;
xp_logininf groupname
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:0mIdcZrxEHA.3984@.cpmsftngxa10.phx.gbl...
> Its' been some time since I did testing, but I don't think we enumerate
> Universal Groups only Domain Global Groups.
> Try;
> xp_logininf groupname
Kevin,
Thanks again for your reply.
I've asked net admin to recreate groups as global, not universal.
In meantime, I've put results of xp_logininfo below.
1. xp_login 'GroupAccountName', 'members': properly displays list of members
2. xp_login 'GroupMemberAccountName', 'all': gives NULL RECORD!!
3. If, as a test, I add the GroupMemberAccountName to SQL logins as new
separate login IndividualUserName and do
xp_login 'IndividualUserName', 'all' :
then displays individual user's record, but no additional record for group
path.
CONCLUSION?
--
Does above mean that somewhere no connection is being made in SQLServer
between the group and the individual member?
BOL on xp_logininfo: If account_name is a valid Windows NT account but that
account does not have permission to access SQL Server, an empty result set
is returned.
Thanks very much.
Alan|||I know that Domain Local Groups won't work correctly for jobs and I
couldn't remember the results with Universal Groups.
Another question is , Is SQL Server in the same Domain that the users
groups are defined in?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin,
It's in a separate domain.
Alan
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:0Jw0SF1xEHA.3984@.cpmsftngxa10.phx.gbl...
> I know that Domain Local Groups won't work correctly for jobs and I
> couldn't remember the results with Universal Groups.
> Another question is , Is SQL Server in the same Domain that the users
> groups are defined in?
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
Showing posts with label users. Show all posts
Showing posts with label users. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
can't force index
Hi,
I have a table called "users"
basically, it has a column called "user_id",
and there is a clustered index called "user_id_index",
so everytimes I try to execute
select * from users
I look at the execution plan, it's always used user_id_index, which is
fine.
however, I added another index called "user_name_index"
then I execute:
select * from users (index=user_name_index)
now I look at the execution plan, it is still using "user_id_index" ,
obviously,
it didn't force the index. what happen? why sql server ignore my index
hint?
how do you solve this problem?Recall that the clustered index holds the data as well as the key
columns. Unless the index "user_name_index" contains all the columns of
the table the server still has to read the clustered index to retrieve
the data. You should see a bookmark lookup on the cluster key.
Why do you see this as a problem? Why are you attempting to force an
index hint? Why are you using SELECT *, which potentially hinders index
optimization and shouldn't be used at all in production code.
David Portas
SQL Server MVP
--|||Hi
An not supplying a WHERE clause results in SQL server doing a table scan so
indexes may not be used (why use an index when you are returning all the
data?).
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108762629.179991.114690@.o13g2000cwo.googlegroups.com...
> Recall that the clustered index holds the data as well as the key
> columns. Unless the index "user_name_index" contains all the columns of
> the table the server still has to read the clustered index to retrieve
> the data. You should see a bookmark lookup on the cluster key.
> Why do you see this as a problem? Why are you attempting to force an
> index hint? Why are you using SELECT *, which potentially hinders index
> optimization and shouldn't be used at all in production code.
> --
> David Portas
> SQL Server MVP
> --
>|||user_name_index is used on "user_name" column,
but even when I execute
select user_name from users (index=user_name_index)
where user_name='Joe'
I still see the execution plan is using clustered index "user_id_index".
don't you think it's weird?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108762629.179991.114690@.o13g2000cwo.googlegroups.com...
> Recall that the clustered index holds the data as well as the key
> columns. Unless the index "user_name_index" contains all the columns of
> the table the server still has to read the clustered index to retrieve
> the data. You should see a bookmark lookup on the cluster key.
> Why do you see this as a problem? Why are you attempting to force an
> index hint? Why are you using SELECT *, which potentially hinders index
> optimization and shouldn't be used at all in production code.
> --
> David Portas
> SQL Server MVP
> --
>|||> don't you think it's weird?
No. Did you read the replies from Mike and myself?
If you explain what you want to achieve maybe we can help you better.
If you just want to understand indexes and hints then I recommend Kalen
Delaney's book "Inside SQL Server". Hints are an advanced feature and
should be used only when essential and with an understanding of their
effects on the query plan.
David Portas
SQL Server MVP
--|||> select user_name from users (index=user_name_index)
> where user_name='Joe'
> I still see the execution plan is using clustered index
"user_id_index".
Maybe you could post some runnable code to reproduce that behaviour
(CREATE..., INSERT..., SELECT...). I don't see that myself. I get
user_name_index used with or without the hint. Also tell us your
edition, version and SP level.
David Portas
SQL Server MVP
--|||1) How big is the table? How many pages (blocks of 8 Kilobyte) does the
table use? You can check this with "sp_spaceused".
2) What version of SQL-Server are you using
3) Please post (simplified) DDL and some sample rows, and preferably a
script to reproduce the behavior.
Gert-Jan
Britney wrote:
> user_name_index is used on "user_name" column,
> but even when I execute
> select user_name from users (index=user_name_index)
> where user_name='Joe'
> I still see the execution plan is using clustered index "user_id_index".
> don't you think it's weird?|||Oh my god, sorry guys.
I was wrong about it, "users" is not a table, but it's a view.
I just found out.
In case you ask me why i'm doing this stupid view:
the reason we create a view for this is because I want to do snapshot
isolation for read and write.
if there are data coming in to [2users] table, then I alter view to use
[1users] table. So users table have 2 tables:
Read and write. This way I don't worry about locking.
CREATE VIEW users
AS
select * from [2users]
--
Now We know what is happening...
IF I select from actual table ,
select user_name from [2users] (index=user_name_index)
where user_name='Joe'
I see that it 's using forced index.
I guess view doesn't work correctly for some reason.
---
sp_spaceused [2users]
result:
name rows reserved data index_size unused
[2users] 41892 13952 KB 6616 KB 7144 KB 192 KB
---
select @.@.version
result:
Microsoft SQL Server 2000 - 8.00.780 (Intel X86) Mar 3 2003 10:28:28
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
----
> 1) How big is the table? How many pages (blocks of 8 Kilobyte) does the
> table use? You can check this with "sp_spaceused".
>
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42166EA6.EA6900C3@.toomuchspamalready.nl...
> 1) How big is the table? How many pages (blocks of 8 Kilobyte) does the
> table use? You can check this with "sp_spaceused".
> 2) What version of SQL-Server are you using
> 3) Please post (simplified) DDL and some sample rows, and preferably a
> script to reproduce the behavior.
> Gert-Jan
>
> Britney wrote:
I have a table called "users"
basically, it has a column called "user_id",
and there is a clustered index called "user_id_index",
so everytimes I try to execute
select * from users
I look at the execution plan, it's always used user_id_index, which is
fine.
however, I added another index called "user_name_index"
then I execute:
select * from users (index=user_name_index)
now I look at the execution plan, it is still using "user_id_index" ,
obviously,
it didn't force the index. what happen? why sql server ignore my index
hint?
how do you solve this problem?Recall that the clustered index holds the data as well as the key
columns. Unless the index "user_name_index" contains all the columns of
the table the server still has to read the clustered index to retrieve
the data. You should see a bookmark lookup on the cluster key.
Why do you see this as a problem? Why are you attempting to force an
index hint? Why are you using SELECT *, which potentially hinders index
optimization and shouldn't be used at all in production code.
David Portas
SQL Server MVP
--|||Hi
An not supplying a WHERE clause results in SQL server doing a table scan so
indexes may not be used (why use an index when you are returning all the
data?).
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108762629.179991.114690@.o13g2000cwo.googlegroups.com...
> Recall that the clustered index holds the data as well as the key
> columns. Unless the index "user_name_index" contains all the columns of
> the table the server still has to read the clustered index to retrieve
> the data. You should see a bookmark lookup on the cluster key.
> Why do you see this as a problem? Why are you attempting to force an
> index hint? Why are you using SELECT *, which potentially hinders index
> optimization and shouldn't be used at all in production code.
> --
> David Portas
> SQL Server MVP
> --
>|||user_name_index is used on "user_name" column,
but even when I execute
select user_name from users (index=user_name_index)
where user_name='Joe'
I still see the execution plan is using clustered index "user_id_index".
don't you think it's weird?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108762629.179991.114690@.o13g2000cwo.googlegroups.com...
> Recall that the clustered index holds the data as well as the key
> columns. Unless the index "user_name_index" contains all the columns of
> the table the server still has to read the clustered index to retrieve
> the data. You should see a bookmark lookup on the cluster key.
> Why do you see this as a problem? Why are you attempting to force an
> index hint? Why are you using SELECT *, which potentially hinders index
> optimization and shouldn't be used at all in production code.
> --
> David Portas
> SQL Server MVP
> --
>|||> don't you think it's weird?
No. Did you read the replies from Mike and myself?
If you explain what you want to achieve maybe we can help you better.
If you just want to understand indexes and hints then I recommend Kalen
Delaney's book "Inside SQL Server". Hints are an advanced feature and
should be used only when essential and with an understanding of their
effects on the query plan.
David Portas
SQL Server MVP
--|||> select user_name from users (index=user_name_index)
> where user_name='Joe'
> I still see the execution plan is using clustered index
"user_id_index".
Maybe you could post some runnable code to reproduce that behaviour
(CREATE..., INSERT..., SELECT...). I don't see that myself. I get
user_name_index used with or without the hint. Also tell us your
edition, version and SP level.
David Portas
SQL Server MVP
--|||1) How big is the table? How many pages (blocks of 8 Kilobyte) does the
table use? You can check this with "sp_spaceused".
2) What version of SQL-Server are you using
3) Please post (simplified) DDL and some sample rows, and preferably a
script to reproduce the behavior.
Gert-Jan
Britney wrote:
> user_name_index is used on "user_name" column,
> but even when I execute
> select user_name from users (index=user_name_index)
> where user_name='Joe'
> I still see the execution plan is using clustered index "user_id_index".
> don't you think it's weird?|||Oh my god, sorry guys.
I was wrong about it, "users" is not a table, but it's a view.
I just found out.
In case you ask me why i'm doing this stupid view:
the reason we create a view for this is because I want to do snapshot
isolation for read and write.
if there are data coming in to [2users] table, then I alter view to use
[1users] table. So users table have 2 tables:
Read and write. This way I don't worry about locking.
CREATE VIEW users
AS
select * from [2users]
--
Now We know what is happening...
IF I select from actual table ,
select user_name from [2users] (index=user_name_index)
where user_name='Joe'
I see that it 's using forced index.
I guess view doesn't work correctly for some reason.
---
sp_spaceused [2users]
result:
name rows reserved data index_size unused
[2users] 41892 13952 KB 6616 KB 7144 KB 192 KB
---
select @.@.version
result:
Microsoft SQL Server 2000 - 8.00.780 (Intel X86) Mar 3 2003 10:28:28
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
----
> 1) How big is the table? How many pages (blocks of 8 Kilobyte) does the
> table use? You can check this with "sp_spaceused".
>
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42166EA6.EA6900C3@.toomuchspamalready.nl...
> 1) How big is the table? How many pages (blocks of 8 Kilobyte) does the
> table use? You can check this with "sp_spaceused".
> 2) What version of SQL-Server are you using
> 3) Please post (simplified) DDL and some sample rows, and preferably a
> script to reproduce the behavior.
> Gert-Jan
>
> Britney wrote:
Sunday, February 19, 2012
Can't connect to SQl Server 2005 with sa Login
Hello,
I recently installed SQL2005 and SQL2000. I only created SA login permissions with Admin access to the database (There are no Windows Users that have access).
I then installed SQL2000 and it overwrote the "mixed connection mode" . whenever I try to connect with the sa login I get the following error
Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
The problem is explained in a fix http://support.microsoft.com/kb/269587
The only problem is that the fix requires access rights that I don't have. My only logins are "BuiltIn\Users" that have public access rights and then a "sa" login (that doesn't work) that has Admin rights within the db engine.
So my question is... Can I create an sysadmin account with windows authentication (for a normal Windows Admin Account) without having the proper rights within the database engine?
-Tom
I would recommend taking a look to the followign post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1353448&SiteID=1
If the information there doesn't help you, please let us know, we will be glad to help.
Thanks,
-Raul Garcia
SDE/T
SQL Server Engine
Can't connect to SQL Server 2005 Express
PLEASE HELP ME
I ran the upsize wizard on one of my databases to SQL Server 2005 Express. I can connect to it just fine. I have 3 test users and they cannot. They get the following:
Connection failed:
SQL State: '01000'
SQL Server Error: 11004
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connt()).
Connection failed:
SQL State: '08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.
I tried adding the SQL Server to 1 users System DSN, but I got this message:
Connection failed:
SQL State: '01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connt()).
Connection failed:
SQL State: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
What am I doing wrong?
See my whitepaper on Connecting... http://betav.com/blog/billva/2006/06/getting_and_staying_connected_1.html
I expect that you you have not properly configure the SQL Server instances to be visible on the network--they are hidden by default.
|||Please follow steps to configure your Server and be aware of firewall issues.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
Basically, you need make sure remote connection is enabled and it's not firewalled.
Subscribe to:
Posts (Atom)