Ok, really Dumb question but still new

I've got a user that needs to be able to get into the attach to the
SQL database server (SQL 2000) from desktop engines I guess it would
be to manage some databases. He was a user that used to have domain
admin priveledges but I took those away so he wouldn't screw other
things up. How can I give him permissions to get what he needs
without giving him Domain Admin permissions? Is there a SQL admin
group or an area within SQL Enterprise manager to delegate SQL Admin
rights?
Thanks.
On Jan 31, 11:20 am, "newsqlman" <ndit...@.yahoo.com> wrote:
> I've got a user that needs to be able to get into the attach to the
> SQL database server (SQL 2000) from desktop engines I guess it would
> be to manage some databases. He was a user that used to have domain
> admin priveledges but I took those away so he wouldn't screw other
> things up. How can I give him permissions to get what he needs
> without giving him Domain Admin permissions? Is there a SQL admin
> group or an area within SQL Enterprise manager to delegate SQL Admin
> rights?
> Thanks.
If you go to Security/logins in Enterprise manager you can add a login
and assign the system administrator server role. This will give them
full admin privileges on the sql server instance.
|||So under Enterprise Manager, the sql group and local area which lists
all the folders.
There is a security folder and underneath it there is a logins
option. I see you can add logins there.
So what I need to do if I understand correctly is add the login from
the domain list domain\user and then
under the properties of that login I go to the server roles tab and
check server administrators box. Is that all
I need to do or do I need to specify all the databases under the
database access tab as well?
Thanks for the help.
|||If you're a server administrator, you're "god" on all of the databases, yes.
newsqlman wrote:
> So under Enterprise Manager, the sql group and local area which lists
> all the folders.
> There is a security folder and underneath it there is a logins
> option. I see you can add logins there.
> So what I need to do if I understand correctly is add the login from
> the domain list domain\user and then
> under the properties of that login I go to the server roles tab and
> check server administrators box. Is that all
> I need to do or do I need to specify all the databases under the
> database access tab as well?
> Thanks for the help.
>
-Dave Markle
http://www.markleconsulting.com/blog
sql

Ok, really Dumb question but still new

I've got a user that needs to be able to get into the attach to the
SQL database server (SQL 2000) from desktop engines I guess it would
be to manage some databases. He was a user that used to have domain
admin priveledges but I took those away so he wouldn't screw other
things up. How can I give him permissions to get what he needs
without giving him Domain Admin permissions? Is there a SQL admin
group or an area within SQL Enterprise manager to delegate SQL Admin
rights?
Thanks.On Jan 31, 11:20 am, "newsqlman" <ndit...@.yahoo.com> wrote:
> I've got a user that needs to be able to get into the attach to the
> SQL database server (SQL 2000) from desktop engines I guess it would
> be to manage some databases. He was a user that used to have domain
> admin priveledges but I took those away so he wouldn't screw other
> things up. How can I give him permissions to get what he needs
> without giving him Domain Admin permissions? Is there a SQL admin
> group or an area within SQL Enterprise manager to delegate SQL Admin
> rights?
> Thanks.
If you go to Security/logins in Enterprise manager you can add a login
and assign the system administrator server role. This will give them
full admin privileges on the sql server instance.|||So under Enterprise Manager, the sql group and local area which lists
all the folders.
There is a security folder and underneath it there is a logins
option. I see you can add logins there.
So what I need to do if I understand correctly is add the login from
the domain list domain\user and then
under the properties of that login I go to the server roles tab and
check server administrators box. Is that all
I need to do or do I need to specify all the databases under the
database access tab as well?
Thanks for the help.|||If you're a server administrator, you're "god" on all of the databases, yes.
newsqlman wrote:
> So under Enterprise Manager, the sql group and local area which lists
> all the folders.
> There is a security folder and underneath it there is a logins
> option. I see you can add logins there.
> So what I need to do if I understand correctly is add the login from
> the domain list domain\user and then
> under the properties of that login I go to the server roles tab and
> check server administrators box. Is that all
> I need to do or do I need to specify all the databases under the
> database access tab as well?
> Thanks for the help.
>
-Dave Markle
http://www.markleconsulting.com/blog

Ok, really Dumb question but still new

I've got a user that needs to be able to get into the attach to the
SQL database server (SQL 2000) from desktop engines I guess it would
be to manage some databases. He was a user that used to have domain
admin priveledges but I took those away so he wouldn't screw other
things up. How can I give him permissions to get what he needs
without giving him Domain Admin permissions? Is there a SQL admin
group or an area within SQL Enterprise manager to delegate SQL Admin
rights?
Thanks.On Jan 31, 11:20 am, "newsqlman" <ndit...@.yahoo.com> wrote:
> I've got a user that needs to be able to get into the attach to the
> SQL database server (SQL 2000) from desktop engines I guess it would
> be to manage some databases. He was a user that used to have domain
> admin priveledges but I took those away so he wouldn't screw other
> things up. How can I give him permissions to get what he needs
> without giving him Domain Admin permissions? Is there a SQL admin
> group or an area within SQL Enterprise manager to delegate SQL Admin
> rights?
> Thanks.
If you go to Security/logins in Enterprise manager you can add a login
and assign the system administrator server role. This will give them
full admin privileges on the sql server instance.|||So under Enterprise Manager, the sql group and local area which lists
all the folders.
There is a security folder and underneath it there is a logins
option. I see you can add logins there.
So what I need to do if I understand correctly is add the login from
the domain list domain\user and then
under the properties of that login I go to the server roles tab and
check server administrators box. Is that all
I need to do or do I need to specify all the databases under the
database access tab as well?
Thanks for the help.|||If you're a server administrator, you're "god" on all of the databases, yes.
newsqlman wrote:
> So under Enterprise Manager, the sql group and local area which lists
> all the folders.
> There is a security folder and underneath it there is a logins
> option. I see you can add logins there.
> So what I need to do if I understand correctly is add the login from
> the domain list domain\user and then
> under the properties of that login I go to the server roles tab and
> check server administrators box. Is that all
> I need to do or do I need to specify all the databases under the
> database access tab as well?
> Thanks for the help.
>
-Dave Markle
http://www.markleconsulting.com/blog

ok to remove guest from msdb?

BOL says "The guest user can be deleted and added to all databases
except master and tempdb, where it must always exist."
any reason for guest to be in msdb?
I usually remove guest from msdb, but to be more certain before posting this I asked around my MVP
and MCT colleagues. No one has experienced any bad effect from removing guest from msdb. Of course,
for a non-sysadmin to create jobs etc, you need to add that login to msdb, but the fact that you now
can control who can do what is a good thing, IMO (and might just be why you posted this in the first
place :-) ).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ch" <ch@.dontemailme.com> wrote in message news:41862F84.647BCB04@.dontemailme.com...
> BOL says "The guest user can be deleted and added to all databases
> except master and tempdb, where it must always exist."
> any reason for guest to be in msdb?

ok to remove guest from msdb?

BOL says "The guest user can be deleted and added to all databases
except master and tempdb, where it must always exist."
any reason for guest to be in msdb?I usually remove guest from msdb, but to be more certain before posting this
I asked around my MVP
and MCT colleagues. No one has experienced any bad effect from removing gues
t from msdb. Of course,
for a non-sysadmin to create jobs etc, you need to add that login to msdb, b
ut the fact that you now
can control who can do what is a good thing, IMO (and might just be why you
posted this in the first
place :-) ).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ch" <ch@.dontemailme.com> wrote in message news:41862F84.647BCB04@.dontemailme.com...eagreen">
> BOL says "The guest user can be deleted and added to all databases
> except master and tempdb, where it must always exist."
> any reason for guest to be in msdb?

ok to remove guest from msdb?

BOL says "The guest user can be deleted and added to all databases
except master and tempdb, where it must always exist."
any reason for guest to be in msdb?I usually remove guest from msdb, but to be more certain before posting this I asked around my MVP
and MCT colleagues. No one has experienced any bad effect from removing guest from msdb. Of course,
for a non-sysadmin to create jobs etc, you need to add that login to msdb, but the fact that you now
can control who can do what is a good thing, IMO (and might just be why you posted this in the first
place :-) ).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ch" <ch@.dontemailme.com> wrote in message news:41862F84.647BCB04@.dontemailme.com...
> BOL says "The guest user can be deleted and added to all databases
> except master and tempdb, where it must always exist."
> any reason for guest to be in msdb?

OK - I'm stupid....How do you create a user?

OK - I'm stupid. How do you create a user? I've gone through the steps of
creating the user for the server, then create the user for the db but the
user cannot login.
Can somebody point me towards a web page/article that will tell me how to do
this?
TIA - Jeff.You might find the topics under this tutorial useful.
http://msdn2.microsoft.com/en-us/library/ms365345.aspx
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"UJ" <fred@.nowhere.com> wrote in message
news:OsHzQQPCHHA.3380@.TK2MSFTNGP04.phx.gbl...
> OK - I'm stupid. How do you create a user? I've gone through the steps of
> creating the user for the server, then create the user for the db but the
> user cannot login.
> Can somebody point me towards a web page/article that will tell me how to
> do this?
> TIA - Jeff.
>|||OK. I'm doing something wrong. I execute the following statements:
use mktg
go
create login stoelle with password = 'sharon'
go
create user stoelle for login stoelle
go
grant select on referrals to stoelle
go
grant delete on referrals to stoelle
go
grant update on referrals to stoelle
go
grant insert on referrals to stoelle
go
The command completes successfully. I can see the user and the login. But I
can't login as the user. What else do I need to do?
TIA - Jeff.
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:OslCL5RCHHA.144@.TK2MSFTNGP02.phx.gbl...
> You might find the topics under this tutorial useful.
> http://msdn2.microsoft.com/en-us/library/ms365345.aspx
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "UJ" <fred@.nowhere.com> wrote in message
> news:OsHzQQPCHHA.3380@.TK2MSFTNGP04.phx.gbl...
>|||> The command completes successfully. I can see the user and the login. But
> I can't login as the user. What else do I need to do?
What error message do you get?
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"UJ" <fred@.nowhere.com> wrote in message
news:%23N7xadZCHHA.1012@.TK2MSFTNGP04.phx.gbl...
> OK. I'm doing something wrong. I execute the following statements:
>
> use mktg
> go
> create login stoelle with password = 'sharon'
> go
> create user stoelle for login stoelle
> go
> grant select on referrals to stoelle
> go
> grant delete on referrals to stoelle
> go
> grant update on referrals to stoelle
> go
> grant insert on referrals to stoelle
> go
>
> The command completes successfully. I can see the user and the login. But
> I can't login as the user. What else do I need to do?
> TIA - Jeff.
>
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:OslCL5RCHHA.144@.TK2MSFTNGP02.phx.gbl...
>|||Login failed for user stoelle
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:%23ptAjBaCHHA.4992@.TK2MSFTNGP03.phx.gbl...
> What error message do you get?
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "UJ" <fred@.nowhere.com> wrote in message
> news:%23N7xadZCHHA.1012@.TK2MSFTNGP04.phx.gbl...
>|||> Login failed for user stoelle
You're almost there. Because the login you created is a local account and
not a Windows login, you'll need to change the authentication mode SQL
Server uses from "Windows Authentication" to "SQL Server and Windows
Authentication". This topic describes how to do this:
http://msdn2.microsoft.com/en-us/library/ms188670.aspx.
By default SQL Server uses Windows Authentication and it is considered a
best practice recommendation because it is more secure. In addition, you
should always use strong passwords for your logins. For more information
about authentication modes and strong passwords, see this topic:
http://msdn2.microsoft.com/en-us/library/ms144284.aspx.
I'm assuming you're learning/experimenting, which is great. When you're
ready to set up a production system with real users, you should use only
Windows authentication mode if at all possible.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"UJ" <fred@.nowhere.com> wrote in message
news:uS9g7bbCHHA.468@.TK2MSFTNGP06.phx.gbl...
> Login failed for user stoelle
> "Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
> news:%23ptAjBaCHHA.4992@.TK2MSFTNGP03.phx.gbl...
>sql

Offline Report Generation

Hi,

I have a requirement of generating very long reports (1-2hrs approx). Obviously the user would not like to wait for the report to come up to view.

Can I generate reports offline using SSRS 2005? If yes, then how can i submit the report for generation and re-direct the user to any other page, where he/she can see the status of the report (or continue to work on other pages).
If yes, then can I run an exe in the background to check the status of the report at regular intervals and notify the user once the report is completed? I checked the status of the report in the ExecutionLog table should be 'rsSuccessful', indicating the successful generation of the report.

If yes, then where (path) the reports generated offline are stored?

Thanks in advance.One option would be to schedule the reports and then deliver them to an email address or fileshare.
Another option would be to use the snapshot feature.
From BOL:

Report history is a collection of report snapshots. You can maintain report history by adding and deleting snapshots, or by modifying properties that affect report history storage. You can create report history manually or on a schedule.

To create report history, your role assignment must include the "Manage report history" task. To view report history, your role assignment must include the "View reports" task. Report history is available to all users who have access to the report. You cannot selectively enable or disable report history for a subset of users.

Snapshots in report history are identified by the date and time they were created. The date and time is based on when the query executed.

Offline command hangs when I try to take database offline

Hi, I am relative newbie to SQLServer. When I try to take a user database offline, the query "hangs," with the query processing circle spinning. The Sharepoint 7 application is running on top of the Enterprise SQL Server 2005 db with several logins sleeping and awaiting commands. No errors are generated until I kill the offline command. Anyone have any ideas? Do I need to kill all the connections?Are there any users / connections to the database that you want to take offline? If so, you should ensure that these are disconnected first, as you cannot take a database offline until there are no outstanding connections.

Thanks,|||

Try "alter database <db> set restricted_user with rollback immediate" before setting it offline. This will terminiate user connections and rollback their transactions and only allow priviledged users to connect.

Offline Capabilities

Hello All,

I have developed a solution where the user can generate pivot based reports using excel with Analysis Services in the background.

Now the requirement is that the user should also be able to go offline.

I want to understand how can this be done. Given the fact that the data in the cube is of financial nature so we need to have very secure solution.

(I read about local cubes, but how will I secure them? today the customer is generating excel sheets and protects it with windows DRM.... the problem with that solution is that we have too much data to put in excel).

Securing local cubes:

http://sqljunkies.com/WebLog/mosha/archive/2006/03/08/local_cube_password.aspx
|||

Passwords don't provide a good security solution because they can be shared, they are known to the person.

Let's say an employee who had the excel sheet is leaving the company, he takes it along to his new employer and uses it there because he has the password.

Is it possible to DRM (digital rights management) protect a local cube. So that everytime someone opens a cube it makes contact to the DRM server to decide what can be done and read/write to the cube?

As I said that our security requrirement is very critical because the cube contains a lot of financial data.

regards,

Abhishek.

|||There is no DRM integration with local cube files.

Odd Rights Issue

SQL Server 2000 Server at SP3a
WinXP SQL Server 2000 Client at SP4
Win2K SQL Server 2000 Client at SP3a
Identical local user account setup on both clients using peer-to-peer
windows authentication to server.
The account on Win2K client at SP3a can successfully open pre-existing
transformation tasks in DTS.
The account on WinXP client at SP4 cannot open pre-existing transforamtion
tasks in DTS (but can create new ones okay).
What gives? Thanks for any help anyone can offer!
ChuckI forgot to include the actual error message in WinXP client:
Error Source : Microsoft OLE DB Provider for SQL Server
Error Description : [DNETLIB][ConnectionOpen (Connect()).]SQL Server
does
not exist or access denied.
WinXP client has no difficulty registering the server and doing other
functions. Since exact same account works on another client, leads me to
believe something about either XP or SP4 that's causing the problem. Can
SP4 cleint connect to SP3a server?
"CR" <chuck.rich__ardson@.sfcc.edu> wrote in message
news:eZItbHLrFHA.2896@.TK2MSFTNGP10.phx.gbl...
> SQL Server 2000 Server at SP3a
> WinXP SQL Server 2000 Client at SP4
> Win2K SQL Server 2000 Client at SP3a
> Identical local user account setup on both clients using peer-to-peer
> windows authentication to server.
> The account on Win2K client at SP3a can successfully open pre-existing
> transformation tasks in DTS.
> The account on WinXP client at SP4 cannot open pre-existing transforamtion
> tasks in DTS (but can create new ones okay).
> What gives? Thanks for any help anyone can offer!
> Chuck
>|||I figured it out -- the problem client was missing a cleint network alias
for the server. Problem had nothing to do with XP or SP4.
"CR" <chuck.rich__ardson@.sfcc.edu> wrote in message
news:OxYvzKLrFHA.716@.TK2MSFTNGP10.phx.gbl...
> I forgot to include the actual error message in WinXP client:
> Error Source : Microsoft OLE DB Provider for SQL Server
> Error Description : [DNETLIB][ConnectionOpen (Connect()).]SQL Serv
er does
> not exist or access denied.
> WinXP client has no difficulty registering the server and doing other
> functions. Since exact same account works on another client, leads me to
> believe something about either XP or SP4 that's causing the problem. Can
> SP4 cleint connect to SP3a server?
>
> "CR" <chuck.rich__ardson@.sfcc.edu> wrote in message
> news:eZItbHLrFHA.2896@.TK2MSFTNGP10.phx.gbl...
transforamtion[vbcol=seagreen]
>

Odd behavior when calling a user function within an aggregation function

While debugging a code on the sql server 2000 debugger, I have encountered a
strange behavior while calling a user function from whitin the sum
aggregation function.
I have reproduced this behavior with the following code:
CREATE FUNCTION Test1 (@.prm decimal(18,2))
RETURNS decimal(18,2)
AS
BEGIN
return @.prm
END
---
CREATE PROCEDURE Test2
AS
declare @.var decimal(18,2)
set @.var=0
select @.var=sum(dbo.test1(Col1))
from Tbl1
print @.var
---
Col1 is a decimal(18,2) column on table Tbl1.
Now, when debuggin the sp 'Test2', it enters the function 'Test1' *twice*
for each row of Tbl1, but the sum result is ok.
For example if I had 2 rows on Tbl1 with values 1,2 on Col1, then i see it
enters twice to function 'Test2' with prm=1 and then twice with prm=2, but
the result will be 3 as it should be.
On my original code I am using a recursive function that is running on a
tree and not a simple one like in this example, so in my case it harms the
performance quite a lot (actually any node on the tree will be executed 2^h
when h is the height of the node).
I replaced the sum function with the variable that sums itself the values as
followed:
select @.var = @.var + dbo.test1(Col1)
But still i'm not sure if there's no a better idea to solve it.
What is the cause of this behavior? Is it only while debugging?
Any ideas and thoughts will be appreciated.
Thanks, apscalar function gets invoked once per row. this is by design, i'm afraid.
-oj
<ap> wrote in message news:%23blucZYRFHA.2736@.TK2MSFTNGP09.phx.gbl...
> While debugging a code on the sql server 2000 debugger, I have encountered
> a strange behavior while calling a user function from whitin the sum
> aggregation function.
> I have reproduced this behavior with the following code:
> CREATE FUNCTION Test1 (@.prm decimal(18,2))
> RETURNS decimal(18,2)
> AS
> BEGIN
> return @.prm
> END
> ---
> CREATE PROCEDURE Test2
> AS
> declare @.var decimal(18,2)
> set @.var=0
> select @.var=sum(dbo.test1(Col1))
> from Tbl1
> print @.var
> ---
> Col1 is a decimal(18,2) column on table Tbl1.
> Now, when debuggin the sp 'Test2', it enters the function 'Test1' *twice*
> for each row of Tbl1, but the sum result is ok.
> For example if I had 2 rows on Tbl1 with values 1,2 on Col1, then i see it
> enters twice to function 'Test2' with prm=1 and then twice with prm=2, but
> the result will be 3 as it should be.
> On my original code I am using a recursive function that is running on a
> tree and not a simple one like in this example, so in my case it harms the
> performance quite a lot (actually any node on the tree will be executed
> 2^h when h is the height of the node).
> I replaced the sum function with the variable that sums itself the values
> as followed:
> select @.var = @.var + dbo.test1(Col1)
> But still i'm not sure if there's no a better idea to solve it.
> What is the cause of this behavior? Is it only while debugging?
> Any ideas and thoughts will be appreciated.
> Thanks, ap
>
>|||>> What is the cause of this behavior?
In the topic
http://msdn.microsoft.com/library/e...des_08_460j.asp
they say:
"The number of times that a function specified in a query is actually
executed can vary between execution plans built by the optimizer."
This behaviour can also be revealed by using the "Show Server Trace"
option in Query Analyzer or using Profiler, so it seems that it does
not happen only while debugging.
You can use this statement, instead:
SELECT @.var=SUM(X) FROM (
select top 100 percent dbo.test1(Col1) X
from Tbl1 order by X
) A
It seems that this way the function is called only once for each row.
Razvan

ODBC, Excel, SQL Server

I have a user accessing SQL Server 2000 data through Excel
and MSQuery...we have used ODBC DSN with no probs. It is
only this user with an issue, so very specific to their
setup somehow.
Example databases called A and B.
User had ODBC DSN set up to default database A.
We want it to be B.
Edited DSN to have default database of B.
No prob. Saved, verified, successful. Just like in past.
HOWEVER, when creating a NEW Excel spreadsheet and NEW DB
Query using this DSN, it STILL points to database A for
some reason. There are no other DSNs with this name
(system, file, or user).
Any idea why it is pointing to the old db, and WHERE it is
getting this from? I have tried removing the DSN and
recreating it. I have tried using other people's logins
instead of his. This works fine on any other computer we
try it on.
Perplexed,
Sharon
I think this might be caused by using an Excel data source that points to
the old ODBC DSN. Excel builds its own wrapper for ODBC data sources and
typically stores them as .odc files. These are separate entries from the
ODBC data source that you build using the ODBC Administrator. Try opening
Excel, using the Data | Import External Data | Import Data option to display
the Select Data Source dialog. Then delete the old data source by right
clicking on it and selecting Delete from the pop-up menu. Once the old Excel
data source file is deleted build a new one by clicking the New Source
button and following the wizard dialogs.
Mike O.
"Sharon" <beall2@.llnl.gov> wrote in message
news:12e3201c411fc$714a9300$a301280a@.phx.gbl...
> I have a user accessing SQL Server 2000 data through Excel
> and MSQuery...we have used ODBC DSN with no probs. It is
> only this user with an issue, so very specific to their
> setup somehow.
> Example databases called A and B.
> User had ODBC DSN set up to default database A.
> We want it to be B.
> Edited DSN to have default database of B.
> No prob. Saved, verified, successful. Just like in past.
> HOWEVER, when creating a NEW Excel spreadsheet and NEW DB
> Query using this DSN, it STILL points to database A for
> some reason. There are no other DSNs with this name
> (system, file, or user).
> Any idea why it is pointing to the old db, and WHERE it is
> getting this from? I have tried removing the DSN and
> recreating it. I have tried using other people's logins
> instead of his. This works fine on any other computer we
> try it on.
> Perplexed,
> Sharon
>

ODBC, Excel, SQL Server

I have a user accessing SQL Server 2000 data through Excel
and MSQuery...we have used ODBC DSN with no probs. It is
only this user with an issue, so very specific to their
setup somehow.
Example databases called A and B.
User had ODBC DSN set up to default database A.
We want it to be B.
Edited DSN to have default database of B.
No prob. Saved, verified, successful. Just like in past.
HOWEVER, when creating a NEW Excel spreadsheet and NEW DB
Query using this DSN, it STILL points to database A for
some reason. There are no other DSNs with this name
(system, file, or user).
Any idea why it is pointing to the old db, and WHERE it is
getting this from? I have tried removing the DSN and
recreating it. I have tried using other people's logins
instead of his. This works fine on any other computer we
try it on.
Perplexed,
SharonI think this might be caused by using an Excel data source that points to
the old ODBC DSN. Excel builds its own wrapper for ODBC data sources and
typically stores them as .odc files. These are separate entries from the
ODBC data source that you build using the ODBC Administrator. Try opening
Excel, using the Data | Import External Data | Import Data option to display
the Select Data Source dialog. Then delete the old data source by right
clicking on it and selecting Delete from the pop-up menu. Once the old Excel
data source file is deleted build a new one by clicking the New Source
button and following the wizard dialogs.
Mike O.
"Sharon" <beall2@.llnl.gov> wrote in message
news:12e3201c411fc$714a9300$a301280a@.phx
.gbl...
> I have a user accessing SQL Server 2000 data through Excel
> and MSQuery...we have used ODBC DSN with no probs. It is
> only this user with an issue, so very specific to their
> setup somehow.
> Example databases called A and B.
> User had ODBC DSN set up to default database A.
> We want it to be B.
> Edited DSN to have default database of B.
> No prob. Saved, verified, successful. Just like in past.
> HOWEVER, when creating a NEW Excel spreadsheet and NEW DB
> Query using this DSN, it STILL points to database A for
> some reason. There are no other DSNs with this name
> (system, file, or user).
> Any idea why it is pointing to the old db, and WHERE it is
> getting this from? I have tried removing the DSN and
> recreating it. I have tried using other people's logins
> instead of his. This works fine on any other computer we
> try it on.
> Perplexed,
> Sharon
>

ODBC via Windows authentication

Hello,

We have an internal application that accesses SQL Server 2000 via ODBC.
The connecton is established with user id and password. We recently
received a business request to allow the software to work with Windows
authentication scheme going forward. This implies that users will no
longer have passwords to login to the database. The Windows PC would
take care of the authentication.

Is there a way (perhaps a connection string) to allow ODBC to connect
to the database in this new environment? I would appreciate any tips
or pointers.<tunity5@.yahoo.com> wrote in message
news:1105712559.798067.299450@.c13g2000cwb.googlegr oups.com...
> Hello,
> We have an internal application that accesses SQL Server 2000 via ODBC.
> The connecton is established with user id and password. We recently
> received a business request to allow the software to work with Windows
> authentication scheme going forward. This implies that users will no
> longer have passwords to login to the database. The Windows PC would
> take care of the authentication.
> Is there a way (perhaps a connection string) to allow ODBC to connect
> to the database in this new environment? I would appreciate any tips
> or pointers.

There are some sample connection strings for trusted connections here:

http://www.able-consulting.com/MDAC...verForSQLServer

Simon|||I think you can call SQLConnect with an empty user name and password
information.
Also, for using SQLDriverConnect, try to add "Trusted_Connection = yes" into
your connect string.

t> The connecton is established with user id and password. We recently
t> received a business request to allow the software to work with
t> Windows authentication scheme going forward. This implies that users
t> will no longer have passwords to login to the database. The Windows
t> PC would take care of the authentication.

Igor Shekalev, http://www.sqledit.com, SQL editor with ODBC support

ODBC Trusted_Connection call to SQLGetInfo returns DBO as user name, not NT User

Hello, all:
I am connecting to SQL Server 2000 with a trusted connection, and that is working fine. I then am issuing a SQLGetInfo call to find out the SQL_USER_NAME, which is always returning dbo instead of (my) Windows NT login name, which I expect to see. THe authentication is (apparently) confirmed by the SQL Server session monitor where it shows the right Windows NT user name logged in, over ODBC. With a standard SQL Login, untrusted, I definitely get the right user name back, not dbo.

So:

Login Type / user id /returned value from SQLGetInfo

Windows Authentication / Windows login / dbo
SQL Authentication / Windows login (UID) / UID

The problem is that I am trying to confirm that the userid entered in a dialog or passed on the command line to my application matches the actual connected user name inside SQL Server...

My questions:
Is there some pathology in Trusted_Connections that masks the Windows/NT login name and always returns UID 'dbo' from a call to SQLGetInfo? Is there a MSS (public) stored procedure that can robustly give back the logged in user name so I can bypass the SQLGetInfo call?I think I have finally wrestled this beast to the ground. Here's the story, in case anybody is interested:

1) When making an NTLM trusted connection to MSS, logging in using my NT login name e.g. as amarshall, the system was always saying the connected user is dbo, no matter what. This can be verified by using a SQL tool like SQL Query Analyzer, connecting over a trusted connection to MSS, and entering SELECT USER; or SELECT USER_NAME() or any variants. These will always return dbo, which is not the real 'user' of interest, but the schema owner. What we want is to confirm 'amarshall' is a (legitimate) user, meaning, SQL Server knows who this is..

2) So, use the SYSTEM_USER call, and the result is AMARSHALL-CAM\amarshall (Host\user).

3) Then, just perform substring match to see if what the user has typed into the login dialog (amarshall) matches what is in the call result. If so, good user, otherwise, reject (program decides).

4) I do NOT strip off the host name or anything - just substring match result of SYSTEM_USER call to logins userid.

5) Database connectstring for Powerbuilder must include SECURE=1. For ADO/VB/.NET, has to have something like TRUSTED_CONNECTION=yes (PB does this for me)

6) Have not tested this against the SQL Server Desktop Edition. Should be identical, but who knows?

7) If anybody has a superior resolution, or security concerns, please advise, but this suffices for now.|||dbo is a database user. You seem to want the server login information, which is quite different. I'd use suser_sname (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setu-sus_86xx.asp).

-PatP

ODBC system DSN Issue

Hi,
I have a problem with SQL 2005, ODBC Connection,
I did create a ODBC conection with user DSN to a SQL 2005 database, and
there is a 3rd party aplication that we use to access that SQL database ever
y
thing went fine.
I did create ODBC connection with System DSN to the same Database but when
I try to open the database with our 3rd party application I get this error:
The datasource you specified does not exist.
Any idea why I can access the same database on the same SQL server with user
DSN and not with System DSN?
We are useing Windows 2003R2 (64 bit) and SQL 2005 (64 bit).
Thanks,
Shahin.ODBC on an x64 based OS has 2 sets of system DSN settings stored in the
registry.
If you run: %SystemRoot%\SysWOW64\odbcad32.exe you modify the 32bit
settings.
If you run: %SystemRoot%\system32\odbcad32.exe you modify the 64bit
settings.
32bit client applications use the 32bit settings, 64bit apps use the 64bit
settings.
So, first check which system DSN settings you set (32 or 64bit), then check
the 3rd party app to see if it's 32 or 64bit.
cmk|||Hi Chris,
Thanks for info it was very helpful and it did solve my problem
"Chris Kushnir" wrote:

> ODBC on an x64 based OS has 2 sets of system DSN settings stored in the
> registry.
> If you run: %SystemRoot%\SysWOW64\odbcad32.exe you modify the 32bit
> settings.
> If you run: %SystemRoot%\system32\odbcad32.exe you modify the 64bit
> settings.
> 32bit client applications use the 32bit settings, 64bit apps use the 64bit
> settings.
> So, first check which system DSN settings you set (32 or 64bit), then chec
k
> the 3rd party app to see if it's 32 or 64bit.
>
> cmk
>
>

ODBC SQLFetch problem

Hi,

I have ODBC code reading data from a XLS file.

It opens the file, reads the sheets and columns available, then presents a dialog to allow the user to select which columns are to be used to load the data.

All of this works, almost.

It is a simple "Select A,B,C FROM Sheet1" using SQLExecDirect

When using SQLFetch it always skips the first row of data.

Thanks, any ideas would be great.

Keith

Keith,

If you are using Fast Forward Only AutoFetch cursors, then as an optimization the driver might fill the SQL_ATTR_ROW_ARRAY_SIZE number of rows to your application buffers as part of SQLExecDirect execution. I do know that this is indeed the case with SQL Native Client (SQLNCLI) ODBC driver, not sure about the excel driver though. Can you check to see if that is the case in your scenario. Hope this helps.

Thanks

Waseem

|||

Thanks for the response,

I have played with different row sizes does not change the results.

I have also tried different files with different row counts, It always miss only the first row.

I have also added blank rows above the first and it still misses the first row of data.

Keith

|||

Does SQLExecDirect fetch the 1st row for you? Can you check the bind buffers to see they are filled with column data from the 1st row after SQLExecDirect before calling SQLFetch?

One more thing you might try is to move SQLBindCol after SQLExecDirect to see if that changes anything.

|||

Hi,

SQLExecDirect does not change the bind buffers.

Have moved the SQLBindCol calls before and and after the SQLExecDirect call both have the same effect. I also used SQLGetData instead of SQLBindCol and they also always miss the first row of data.

Keith

|||

Turns out that the Excel driver expects the first row to be the column names. There is supposed to be an option to turn this behavior off, but it has not worked in the past. I am not sure if it has been fixed. Here is the link to more information. http://support.microsoft.com/kb/288343/

If you have a row with column names in it, you can just include that in your range and you will be all set, otherwise, you should try the option, if that doesn't work, use the Jet engine as suggested in the above link.

Thanks

|||Thank you, adding headers does make it read all the lines. I had tried blank rows that has no effect. This is a reader for a user supplied file I have no control over.

ODBC SQLFetch problem

Hi,

I have ODBC code reading data from a XLS file.

It opens the file, reads the sheets and columns available, then presents a dialog to allow the user to select which columns are to be used to load the data.

All of this works, almost.

It is a simple "Select A,B,C FROM Sheet1" using SQLExecDirect

When using SQLFetch it always skips the first row of data.

Thanks, any ideas would be great.

Keith

Keith,

If you are using Fast Forward Only AutoFetch cursors, then as an optimization the driver might fill the SQL_ATTR_ROW_ARRAY_SIZE number of rows to your application buffers as part of SQLExecDirect execution. I do know that this is indeed the case with SQL Native Client (SQLNCLI) ODBC driver, not sure about the excel driver though. Can you check to see if that is the case in your scenario. Hope this helps.

Thanks

Waseem

|||

Thanks for the response,

I have played with different row sizes does not change the results.

I have also tried different files with different row counts, It always miss only the first row.

I have also added blank rows above the first and it still misses the first row of data.

Keith

|||

Does SQLExecDirect fetch the 1st row for you? Can you check the bind buffers to see they are filled with column data from the 1st row after SQLExecDirect before calling SQLFetch?

One more thing you might try is to move SQLBindCol after SQLExecDirect to see if that changes anything.

|||

Hi,

SQLExecDirect does not change the bind buffers.

Have moved the SQLBindCol calls before and and after the SQLExecDirect call both have the same effect. I also used SQLGetData instead of SQLBindCol and they also always miss the first row of data.

Keith

|||

Turns out that the Excel driver expects the first row to be the column names. There is supposed to be an option to turn this behavior off, but it has not worked in the past. I am not sure if it has been fixed. Here is the link to more information. http://support.microsoft.com/kb/288343/

If you have a row with column names in it, you can just include that in your range and you will be all set, otherwise, you should try the option, if that doesn't work, use the Jet engine as suggested in the above link.

Thanks

|||Thank you, adding headers does make it read all the lines. I had tried blank rows that has no effect. This is a reader for a user supplied file I have no control over.

ODBC on remote computer won't access

I'm attempting to connect to an SQL Server Express database from a remote PC, but it isn't connecting. I have a user account created in the database, and I'm using the SQL authentication with that user account. I have success with this if I'm on the server that the database is on, but not remotely. Both are on the same network, even plugged into the same hub. There doesn't appear to be any network problems.

Any ideas? Thanks!

Please follow this instruction to enable remote connections: http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

If you still see problem, follow the instruction here and give us more details http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1