Showing posts with label user. Show all posts
Showing posts with label user. Show all posts

Sunday, 25 March 2012

Connect to remote server

From Enterprise Manager on my PC, how do I connect to a remote server over
the internet - I have the IP address, User Name and Password.

ThanksWhen creating an SQL connection using SQL Server EM you will need to:

Configure the server alias
Create the SQL connection.

Configure the Server alias

Open SQL Server Client Network Utility.
When Client Network Utility opens, click on the Alias tab.
Click on the Add button.
In the Server Alias text box enter a name for your SQL connection.
Select TCP/IP under Network libraries
Enter the IP address of the Server your SQL database is located on, in
the Server name text box.
Click on the OK button.
Your Server alias will now be configured, click on the Apply button.
Click on the OK button.

Create Connection

Open SQL Server Enterprise Manager
When Enterprise Manager opens, right click SQL Server Group and select
New SQL Server Registration.
The Register SQL Server wizard will now open.
Click on the Next button.
>From the Available Servers drop down menu, select the server alias you
created earlier.
Click on the Add button.
The server should now be listed under Added servers, click on the Next
button.
When the next dialog box appears select 'The SQL Server login
information that was assigned to me by the system administrator' (see
above).
Click on the Next button.
Enter your Login name and Password - this will be used every time you
connect to your SQL server. Click on the Next button.
Ensure that 'Add the SQL Server(s) to an existing SQL Server group' is
selected and 'SQL Server Group' is selected for Group name.

Click on the Next button.
When the next dialog box opens you can click on the Finish button.
Your SQL Connection will now be created.

HTH
Paul|||Paul

That works fine, thanks.

<stpaul_71@.yahoo.com> wrote in message
news:1102537081.548714.60980@.z14g2000cwz.googlegro ups.com...
> When creating an SQL connection using SQL Server EM you will need to:
> Configure the server alias
> Create the SQL connection.
> Configure the Server alias
> Open SQL Server Client Network Utility.
> When Client Network Utility opens, click on the Alias tab.
> Click on the Add button.
> In the Server Alias text box enter a name for your SQL connection.
> Select TCP/IP under Network libraries
> Enter the IP address of the Server your SQL database is located on, in
> the Server name text box.
> Click on the OK button.
> Your Server alias will now be configured, click on the Apply button.
> Click on the OK button.
>
> Create Connection
> Open SQL Server Enterprise Manager
> When Enterprise Manager opens, right click SQL Server Group and select
> New SQL Server Registration.
> The Register SQL Server wizard will now open.
> Click on the Next button.
>>From the Available Servers drop down menu, select the server alias you
> created earlier.
> Click on the Add button.
> The server should now be listed under Added servers, click on the Next
> button.
> When the next dialog box appears select 'The SQL Server login
> information that was assigned to me by the system administrator' (see
> above).
> Click on the Next button.
> Enter your Login name and Password - this will be used every time you
> connect to your SQL server. Click on the Next button.
> Ensure that 'Add the SQL Server(s) to an existing SQL Server group' is
> selected and 'SQL Server Group' is selected for Group name.
> Click on the Next button.
> When the next dialog box opens you can click on the Finish button.
> Your SQL Connection will now be created.
>
> HTH
> Paul

Thursday, 22 March 2012

Connect to MSDE with Limited User (not using sql authentication)

I have an MSDE installation that I need to connect to within a C# .NET
project as a Limited User (from the same machine, not via the network)
The MSDE instance is not in sql authentication mode, only windows
authentication. Is this possible? I've used the
'Trusted_Connection=True;' in the connection string, but a login fails
for the user with an error like:
"Cannot open database in login 'testdatabase'. Login fails. Login
failed for wkstn\limiteduser"
Do I need to grant any (additional) rights to the database itself?
Thanks for your help,
Tom
You can=B4t mix up the keyword "USER ID" and Trusted_connection, if you
choose "User ID" (SQL Server auth.) SQL Server will use SQL Server
authentication regardless of the Trusted_connection settings, see more
details here:
http://msdn.microsoft.com/library/de...n-us/adminsql=
/ad_security_47u6.asp
But you can (as you are working with C#) create a impersonated context
and connect with this...
http://www.codeproject.com/csharp/cpimpersonation1.asp
..=2E.and connect with this.
HTH, jens Suessmeyer.

Tuesday, 20 March 2012

Connect to database takes long for non-admins

Hi,

any idea why it might take longer to connect to a SQL Server for a non-administrative user than for a dbo? Same machine, just different logins...

Thanks,

Hi Thomas :-)

first thing for me would to be to have a look in the event logs, Maybe with full security logging (C2 audit) eventually also enabled you might see some more issues. But I assume that the none dbo logins will be redirected to another database than the one the "normal" users are redirected to. That might cause a delay if the other databases are closed for inactivity and have to be reopened first.

HTH, Jens SUessmeyer.

http:/www.sqlserver2005.de|||

Hi Jens,

well, we'll try full logging later... It has nothing to do with the database being closed for inactivity since if you logon twice it will take the same time...

Thanks,

Thomas

|||

Interesting... I'm trying to troubleshoot what may be a similar issue. Here are some specifics...

+ just set up a brand new 64bit server (Windows 2003 R2 64 bit O/S and SQL 2005 64 bit, Std Edition); SQL is the only thing running on this server.

+ installed this new server in the same AD domain as an existing SQL 2000 db

+ on my workstation using SQL Mgmt Studio, I am unable to connect to the new SQL2005 box (and I'm a member of the domain Administrators group), I get a "Timeout expired" dialog box. However, I *am* able to connect to our SQL 2k box.

+ again, on my workstation, I use RDP (remote desktop) to connect to both boxes just fine. So once I'm RDP'd into the SQL2005 box, I fire up SQL Mgmt Studio there... it takes well over a couple of minutes before it even finishes rendering the first screen to connect to any instances. Once it finally does come up, I can connect to the (local) db using Windows Auth.

I am completely baffled as to why it SQL Mgmt studio would take so long to open up... Any information on what it's trying to do might be helpful... It's almost like it's trying to authenticate or connect to something and timing out, although no errors are displayed and there's nothing in the event logs. It's odd to me though, as at that point (just opening up the application), it doesn't seem like it should be attempting anything like that.

-- Margo Noreen

|||

Interesting... I'm trying to troubleshoot what may be a similar issue. Here are some specifics...

+ just set up a brand new 64bit server (Windows 2003 R2 64 bit O/S and SQL 2005 64 bit, Std Edition); SQL is the only thing running on this server.

+ installed this new server in the same AD domain as an existing SQL 2000 db

+ on my workstation using SQL Mgmt Studio, I am unable to connect to the new SQL2005 box (and I'm a member of the domain Administrators group), I get a "Timeout expired" dialog box. However, I *am* able to connect to our SQL 2k box.

+ again, on my workstation, I use RDP (remote desktop) to connect to both boxes just fine. So once I'm RDP'd into the SQL2005 box, I fire up SQL Mgmt Studio there... it takes well over a couple of minutes before it even finishes rendering the first screen to connect to any instances. Once it finally does come up, I can connect to the (local) db using Windows Auth.

I am completely baffled as to why it SQL Mgmt studio would take so long to open up... Any information on what it's trying to do might be helpful... It's almost like it's trying to authenticate or connect to something and timing out, although no errors are displayed and there's nothing in the event logs. It's odd to me though, as at that point (just opening up the application), it doesn't seem like it should be attempting anything like that.

-- Margo Noreen

Sunday, 11 March 2012

Connect into SSAS from SSMS with diferent user

Hi,

I'm a consultant and I need to connect to differents SSAS Servers using Management Studio.

In each customer I have one different user. I use this user to connect into their network and access servers, drives and SQL Server (using SQL autenticate) from my laptop (without connect into domain from my user).

In some customers, when I've a drive mapped to server I can access SSAS, but I don't know why this happen only in some customers and how do this again.

I'd like if someone can help me.

Thanks.

You can specify User Name and Password connection string properties when connecting to AS, (but it is possible that Management Studio doesn't have UI for them). Mapping drive works sometimes because of how NETBIOS interacts with SSPI, but in general it is not reliable way of piggibacking of establishing credentials.|||

The runas command should do what you want:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1365877&SiteID=17

Connect fails: Error 4064, Can not open user default DB

I just installed SQL Server 2005 CTP (9.00.1116) on Windows Server 2003 with SP1. Then I unzipped DotNetNuke portal site and tried to connect to it with various errors, generally relating to invalid login.

I never figured it out after many hours of exploring every SQL interface I could find, changing web.config's connection string, and editing the DotNetNuke ODBC Data Source. At one point I was able to intermittently sign in to the SQL Server Management Studio (but never to get DotNetNuke site to connect to the DB).

Late in this process I gave up, decided to reinstall DotNetNuke from scratch, and removed that directory with my default database. Maybe one isn't supposed to do this, but I did it and suspect that is the reason for the error message in the title of this post. I don't see a way to login in as another user either at this point.

If it is indeed possible for one to delete one's default database - and if so - is there any solution except a complete (multi-hour on my old system) reinstall of SQL Server?

If I try to connect using SQL user SA (user names are case insensative, right?!), I get error 18452: "The user is not associated with a trusted SQL Server Connection."

You can change default database for a user using ALTER LOGIN. You can check your current settings for all users using sys.server_principals.

As far as being unable to connect using ‘sa’ credentials, verify that you have mixed mode authentication enabled. If you do not that might explain why you are getting trusted connection error for ‘sa’.

This may also be affecting your inability to connect to the portal.

Overall, it is recommended that you upgrade to CTP 15 (build 1187), so reinstall might be the cleanest option. Make sure you select ‘mixed mode auth’ and set ‘sa’ password during setup.

Regards,

Boris.

|||Boris B is right , I had the same problem when i started using it and it turned out to be the default database i hat for the specific login .

Connect fails: Error 4064, Can not open user default DB

I just installed SQL Server 2005 CTP (9.00.1116) on Windows Server 2003 with SP1. Then I unzipped DotNetNuke portal site and tried to connect to it with various errors, generally relating to invalid login.

I never figured it out after many hours of exploring every SQL interface I could find, changing web.config's connection string, and editing the DotNetNuke ODBC Data Source. At one point I was able to intermittently sign in to the SQL Server Management Studio (but never to get DotNetNuke site to connect to the DB).

Late in this process I gave up, decided to reinstall DotNetNuke from scratch, and removed that directory with my default database. Maybe one isn't supposed to do this, but I did it and suspect that is the reason for the error message in the title of this post. I don't see a way to login in as another user either at this point.

If it is indeed possible for one to delete one's default database - and if so - is there any solution except a complete (multi-hour on my old system) reinstall of SQL Server?

If I try to connect using SQL user SA (user names are case insensative, right?!), I get error 18452: "The user is not associated with a trusted SQL Server Connection."

You can change default database for a user using ALTER LOGIN. You can check your current settings for all users using sys.server_principals.

As far as being unable to connect using ‘sa’ credentials, verify that you have mixed mode authentication enabled. If you do not that might explain why you are getting trusted connection error for ‘sa’.

This may also be affecting your inability to connect to the portal.

Overall, it is recommended that you upgrade to CTP 15 (build 1187), so reinstall might be the cleanest option. Make sure you select ‘mixed mode auth’ and set ‘sa’ password during setup.

Regards,

Boris.

|||Boris B is right , I had the same problem when i started using it and it turned out to be the default database i hat for the specific login .

connect database user to security login user

After restore off a database the user under security logins was not created.
The user under Database and users was created, but that user MUST bee the
same user under security logins, if i try to make a new user with same name
under logins, thats ok, but i cant connect to the database becuse the user
allready exists, i cant delete the user under the database. What to do? Is
there som way to connect a database user to a security login user. Its SQL
security and login.
/Per W.sp_change_users_login.
Also, you can transport the logins with the correct sid, search KB for sp_he
lp_revlogin.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Per W." <pwbuf@.tiscali.no> wrote in message news:GdMOd.98909$Vf.3877273@.news000.worldonlin
e.dk...
> After restore off a database the user under security logins was not create
d. The user under
> Database and users was created, but that user MUST bee the same user under
security logins, if i
> try to make a new user with same name under logins, thats ok, but i cant c
onnect to the database
> becuse the user allready exists, i cant delete the user under the database
. What to do? Is there
> som way to connect a database user to a security login user. Its SQL secur
ity and login.
>
> /Per W.
>|||The following will list all users whose SPID is not mapped to a valid login:
sp_change_users_login 'Report'
"Per W." <pwbuf@.tiscali.no> wrote in message
news:GdMOd.98909$Vf.3877273@.news000.worldonline.dk...
> After restore off a database the user under security logins was not
created.
> The user under Database and users was created, but that user MUST bee the
> same user under security logins, if i try to make a new user with same
name
> under logins, thats ok, but i cant connect to the database becuse the user
> allready exists, i cant delete the user under the database. What to do? Is
> there som way to connect a database user to a security login user. Its SQL
> security and login.
>
> /Per W.
>

Wednesday, 7 March 2012

Confusing Cross-Database Permissions Issue

We're trying to follow the principle of least privilege here in setting up a user account for our website to use to access SQL Server 2005, but we're having a nightmarish time getting it to work.

The issue seems to be trying to get a limited access user account the ability to cross databases.

Here's the situation:

We have a User [WebUser] that we want to grant access to the database. This account has a login [WebUser] that has username=WebUser and password=ALongPassword.

This user only calls stored procedures in the database [WebData].

However, some of the stored procedures in [WebData] call stored procedures in the database [dbutil].

One of the stored procedures in [dbutil] inserts records into a table in a third database [dbutil_temp].[DebugLog].

This all works out great from my development account using Windows Authentication.

But as you might guess, if I do something like "EXECUTE AS [WebUser]" and run the same procedure on [WebData] things fall apart quickly. I've looked online regarding cross-database ownership chaining, but quite frankly, the whole users/logins/roles/schemas security model is confusing, and I'm getting nowhere fast on my own.

We really only want [WebUser] to have CONNECT and EXECUTE permissions on the primary [WebData] database, but it seems like we've got to do a lot more than that to get this to work.

I'd appreciate any help...

Yes, you have to do a bit more work, but it isn't a whole lot more work. The most appropriate solution for this is to use signatures for the cross database access. There are detailed demos for creating signatures as well as specifically signatures for cross database queries in the following blogs, which I VERY highly recommend reading.

http://blogs.msdn.com/lcris/

http://blogs.msdn.com/raulga/

I would also suggest listenting to the security presentations at http://cmcgc.com/media/WMP/261115

|||Thanks Mike, I'll take a look at those resources.

Saturday, 25 February 2012

conflicting object names in sql server 2000

I can't seem to solve this problem. I hope someone can help. I have a table
which is owned by dbo called xxx. I also have a user define function also
called xxx but owned by a different user. Everything works fine on the local
database. From query analyzer if I type in select * from xxx everything
works fine on the local database.
Here's the problem. When I use dts to move this over to another server, and
then try select * from xxx it returns the error "Server: Msg 208, Level 16,
State 3, Line 1
Invalid object name 'xxx'. If I try select * from dbo.xxx it works fine.
Unfortunately because of the situation I can't go back in and change the
user function name to something else, or change all the code to prefix the
table with dbo.xxx.
The only clue I've come up is that on the local machine when I do an sp_help
it shows "xxx dbouser table2004-02-16 19:14:17.280"
but when I do an sp_help on the other server it shows "xxxmichaeltable
function2005-01-08 13:21:57.500"
that seems to explain my problem, but my question is how/what can I update
so that the table gets the "higher priority" so when I do the select it looks
at the table and not the function when just using xxx and not dbo.xxx or
michael.xxx?
Hope that makes sense. I'm in a real bind with this and would appreciate
any help. Thanks.
John
Hi
This is a bit confusing. Are you trying to access the table or the function?
What user are YOU when you try to access the object?
Here are a couple of points that may help:
if you say
SELECT * from xxxx
you are selecting from a table. The only question is, which table? SQL
Server will first try to select from a table xxx that YOU own, if there is
none, it will see if there is one owned by dbo.
If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
dbo, or anybody besides Michael, wants to access the table owned by
Michael, she must specify the owner:
SELECT * from michael.xxx
If you are selecting from a table valued function, you MUST specify an
owner, PLUS you must specify a parameter list, even if there are no
parameters.
So,
SELECT * from xxx()
is incorrect syntax because you didn't specify the owner
SELECT * from dbo.xxx()
will access the xxx FUNCTION owned by dbo
and
SELECT * from michael.xxx()
will access the xxx FUNCTION owned by michael
I hope this helps. If not, please clarify
What object you are trying to access, and if it's a function, what are the
parameters
Who owns that object
What user you are
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
>I can't seem to solve this problem. I hope someone can help. I have a
>table
> which is owned by dbo called xxx. I also have a user define function also
> called xxx but owned by a different user. Everything works fine on the
> local
> database. From query analyzer if I type in select * from xxx everything
> works fine on the local database.
> Here's the problem. When I use dts to move this over to another server,
> and
> then try select * from xxx it returns the error "Server: Msg 208, Level
> 16,
> State 3, Line 1
> Invalid object name 'xxx'. If I try select * from dbo.xxx it works fine.
> Unfortunately because of the situation I can't go back in and change the
> user function name to something else, or change all the code to prefix the
> table with dbo.xxx.
> The only clue I've come up is that on the local machine when I do an
> sp_help
> it shows "xxx dbo user table 2004-02-16 19:14:17.280"
> but when I do an sp_help on the other server it shows "xxx michael table
> function 2005-01-08 13:21:57.500"
> that seems to explain my problem, but my question is how/what can I update
> so that the table gets the "higher priority" so when I do the select it
> looks
> at the table and not the function when just using xxx and not dbo.xxx or
> michael.xxx?
> Hope that makes sense. I'm in a real bind with this and would appreciate
> any help. Thanks.
> John
>
>
|||Hi,
Yes, this is very confusing, unfortunately this is what I have to work
with. Thanks for responding though.
the connection string specifies "michael" as the user when connecting to
the database. On the local database server when it does the select * from
xxx it is expecting to see the table which is owned by dbo, and it does
produce these results. When it looks at the function, it does it with
michael.xxx Note that michael is the owner of the database. I have
attempted to detach the database and reattach it on another server and it
works like that as well. However the server I need to get these tables and
functions to I do not have access to reattach the database, only to move the
database over via dts. The only clue I can find is the difference in the
sp_help results which I provided earlier.
Thanks again.
"Kalen Delaney" wrote:

> Hi
> This is a bit confusing. Are you trying to access the table or the function?
> What user are YOU when you try to access the object?
> Here are a couple of points that may help:
> if you say
> SELECT * from xxxx
> you are selecting from a table. The only question is, which table? SQL
> Server will first try to select from a table xxx that YOU own, if there is
> none, it will see if there is one owned by dbo.
> If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
> dbo, or anybody besides Michael, wants to access the table owned by
> Michael, she must specify the owner:
> SELECT * from michael.xxx
> If you are selecting from a table valued function, you MUST specify an
> owner, PLUS you must specify a parameter list, even if there are no
> parameters.
> So,
> SELECT * from xxx()
> is incorrect syntax because you didn't specify the owner
> SELECT * from dbo.xxx()
> will access the xxx FUNCTION owned by dbo
> and
> SELECT * from michael.xxx()
> will access the xxx FUNCTION owned by michael
>
> I hope this helps. If not, please clarify
> What object you are trying to access, and if it's a function, what are the
> parameters
> Who owns that object
> What user you are
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
> news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
>
>
|||A login name of michael in the connection string does not mean the user name
is also michael. A login name maps to a user name when the login is given
access to a particular database. A login michael could be a user named
michael in one database, a user named clerk in another, and a user named
user1 in a third database. If michael is the owner of a database, his user
name will be dbo in that database.
My guess is that the login michael is dbo on one database and not dbo on
another.
How exactly are you 'looking' at the function michael.xxx and how do you
know you are looking at the results of the function and not the table xxx.
Can you post the create table statements, the create function statement, and
the exact commands you are using for access. Also, when you are in a
database, you can execute SELECT USER_NAME() to find out what your user name
is in that database.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...[vbcol=seagreen]
> Hi,
> Yes, this is very confusing, unfortunately this is what I have to work
> with. Thanks for responding though.
> the connection string specifies "michael" as the user when connecting to
> the database. On the local database server when it does the select * from
> xxx it is expecting to see the table which is owned by dbo, and it does
> produce these results. When it looks at the function, it does it with
> michael.xxx Note that michael is the owner of the database. I have
> attempted to detach the database and reattach it on another server and it
> works like that as well. However the server I need to get these tables
> and
> functions to I do not have access to reattach the database, only to move
> the
> database over via dts. The only clue I can find is the difference in the
> sp_help results which I provided earlier.
> Thanks again.
>
>
> "Kalen Delaney" wrote:
|||Oops, I did make one mistake. You do not have to specify an owner name when
selecting from a table valued function, but you DO need to include the param
list, even if there are no params.
When you say SELECT * from xxx you can only be selecting from a table or a
view, NOT from a function. The owner of the object will depend on the user
name your login name is mapped to.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...[vbcol=seagreen]
> Hi,
> Yes, this is very confusing, unfortunately this is what I have to work
> with. Thanks for responding though.
> the connection string specifies "michael" as the user when connecting to
> the database. On the local database server when it does the select * from
> xxx it is expecting to see the table which is owned by dbo, and it does
> produce these results. When it looks at the function, it does it with
> michael.xxx Note that michael is the owner of the database. I have
> attempted to detach the database and reattach it on another server and it
> works like that as well. However the server I need to get these tables
> and
> functions to I do not have access to reattach the database, only to move
> the
> database over via dts. The only clue I can find is the difference in the
> sp_help results which I provided earlier.
> Thanks again.
>
>
> "Kalen Delaney" wrote:
|||Here a suggestion: try to add another user (with the role db_owner set?) and
use this account to connect. As this new account will have a different user
name, SQL-Server shouldn't look anymore for any object with michael as the
owner.
S. L.
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...[vbcol=seagreen]
> Hi,
> Yes, this is very confusing, unfortunately this is what I have to work
> with. Thanks for responding though.
> the connection string specifies "michael" as the user when connecting to
> the database. On the local database server when it does the select * from
> xxx it is expecting to see the table which is owned by dbo, and it does
> produce these results. When it looks at the function, it does it with
> michael.xxx Note that michael is the owner of the database. I have
> attempted to detach the database and reattach it on another server and it
> works like that as well. However the server I need to get these tables
> and
> functions to I do not have access to reattach the database, only to move
> the
> database over via dts. The only clue I can find is the difference in the
> sp_help results which I provided earlier.
> Thanks again.
>
>
> "Kalen Delaney" wrote:
|||Thank you. It appears in the database that it works in the user is the dbo,
and in the other database the user isn't. I believe that will solve my
issue.
"Kalen Delaney" wrote:

> A login name of michael in the connection string does not mean the user name
> is also michael. A login name maps to a user name when the login is given
> access to a particular database. A login michael could be a user named
> michael in one database, a user named clerk in another, and a user named
> user1 in a third database. If michael is the owner of a database, his user
> name will be dbo in that database.
> My guess is that the login michael is dbo on one database and not dbo on
> another.
> How exactly are you 'looking' at the function michael.xxx and how do you
> know you are looking at the results of the function and not the table xxx.
> Can you post the create table statements, the create function statement, and
> the exact commands you are using for access. Also, when you are in a
> database, you can execute SELECT USER_NAME() to find out what your user name
> is in that database.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
> news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...
>
>

conflicting object names in sql server 2000

I can't seem to solve this problem. I hope someone can help. I have a table
which is owned by dbo called xxx. I also have a user define function also
called xxx but owned by a different user. Everything works fine on the local
database. From query analyzer if I type in select * from xxx everything
works fine on the local database.
Here's the problem. When I use dts to move this over to another server, and
then try select * from xxx it returns the error "Server: Msg 208, Level 16,
State 3, Line 1
Invalid object name 'xxx'. If I try select * from dbo.xxx it works fine.
Unfortunately because of the situation I can't go back in and change the
user function name to something else, or change all the code to prefix the
table with dbo.xxx.
The only clue I've come up is that on the local machine when I do an sp_help
it shows "xxx dbo user table 2004-02-16 19:14:17.280"
but when I do an sp_help on the other server it shows "xxx michael table
function 2005-01-08 13:21:57.500"
that seems to explain my problem, but my question is how/what can I update
so that the table gets the "higher priority" so when I do the select it looks
at the table and not the function when just using xxx and not dbo.xxx or
michael.xxx?
Hope that makes sense. I'm in a real bind with this and would appreciate
any help. Thanks.
JohnHi
This is a bit confusing. Are you trying to access the table or the function?
What user are YOU when you try to access the object?
Here are a couple of points that may help:
if you say
SELECT * from xxxx
you are selecting from a table. The only question is, which table? SQL
Server will first try to select from a table xxx that YOU own, if there is
none, it will see if there is one owned by dbo.
If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
dbo, or anybody besides Michael, wants to access the table owned by
Michael, she must specify the owner:
SELECT * from michael.xxx
If you are selecting from a table valued function, you MUST specify an
owner, PLUS you must specify a parameter list, even if there are no
parameters.
So,
SELECT * from xxx()
is incorrect syntax because you didn't specify the owner
SELECT * from dbo.xxx()
will access the xxx FUNCTION owned by dbo
and
SELECT * from michael.xxx()
will access the xxx FUNCTION owned by michael
I hope this helps. If not, please clarify
What object you are trying to access, and if it's a function, what are the
parameters
Who owns that object
What user you are
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
>I can't seem to solve this problem. I hope someone can help. I have a
>table
> which is owned by dbo called xxx. I also have a user define function also
> called xxx but owned by a different user. Everything works fine on the
> local
> database. From query analyzer if I type in select * from xxx everything
> works fine on the local database.
> Here's the problem. When I use dts to move this over to another server,
> and
> then try select * from xxx it returns the error "Server: Msg 208, Level
> 16,
> State 3, Line 1
> Invalid object name 'xxx'. If I try select * from dbo.xxx it works fine.
> Unfortunately because of the situation I can't go back in and change the
> user function name to something else, or change all the code to prefix the
> table with dbo.xxx.
> The only clue I've come up is that on the local machine when I do an
> sp_help
> it shows "xxx dbo user table 2004-02-16 19:14:17.280"
> but when I do an sp_help on the other server it shows "xxx michael table
> function 2005-01-08 13:21:57.500"
> that seems to explain my problem, but my question is how/what can I update
> so that the table gets the "higher priority" so when I do the select it
> looks
> at the table and not the function when just using xxx and not dbo.xxx or
> michael.xxx?
> Hope that makes sense. I'm in a real bind with this and would appreciate
> any help. Thanks.
> John
>
>|||Hi,
Yes, this is very confusing, unfortunately this is what I have to work
with. Thanks for responding though.
the connection string specifies "michael" as the user when connecting to
the database. On the local database server when it does the select * from
xxx it is expecting to see the table which is owned by dbo, and it does
produce these results. When it looks at the function, it does it with
michael.xxx Note that michael is the owner of the database. I have
attempted to detach the database and reattach it on another server and it
works like that as well. However the server I need to get these tables and
functions to I do not have access to reattach the database, only to move the
database over via dts. The only clue I can find is the difference in the
sp_help results which I provided earlier.
Thanks again.
"Kalen Delaney" wrote:
> Hi
> This is a bit confusing. Are you trying to access the table or the function?
> What user are YOU when you try to access the object?
> Here are a couple of points that may help:
> if you say
> SELECT * from xxxx
> you are selecting from a table. The only question is, which table? SQL
> Server will first try to select from a table xxx that YOU own, if there is
> none, it will see if there is one owned by dbo.
> If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
> dbo, or anybody besides Michael, wants to access the table owned by
> Michael, she must specify the owner:
> SELECT * from michael.xxx
> If you are selecting from a table valued function, you MUST specify an
> owner, PLUS you must specify a parameter list, even if there are no
> parameters.
> So,
> SELECT * from xxx()
> is incorrect syntax because you didn't specify the owner
> SELECT * from dbo.xxx()
> will access the xxx FUNCTION owned by dbo
> and
> SELECT * from michael.xxx()
> will access the xxx FUNCTION owned by michael
>
> I hope this helps. If not, please clarify
> What object you are trying to access, and if it's a function, what are the
> parameters
> Who owns that object
> What user you are
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
> news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
> >I can't seem to solve this problem. I hope someone can help. I have a
> >table
> > which is owned by dbo called xxx. I also have a user define function also
> > called xxx but owned by a different user. Everything works fine on the
> > local
> > database. From query analyzer if I type in select * from xxx everything
> > works fine on the local database.
> >
> > Here's the problem. When I use dts to move this over to another server,
> > and
> > then try select * from xxx it returns the error "Server: Msg 208, Level
> > 16,
> > State 3, Line 1
> > Invalid object name 'xxx'. If I try select * from dbo.xxx it works fine.
> >
> > Unfortunately because of the situation I can't go back in and change the
> > user function name to something else, or change all the code to prefix the
> > table with dbo.xxx.
> >
> > The only clue I've come up is that on the local machine when I do an
> > sp_help
> > it shows "xxx dbo user table 2004-02-16 19:14:17.280"
> >
> > but when I do an sp_help on the other server it shows "xxx michael table
> > function 2005-01-08 13:21:57.500"
> >
> > that seems to explain my problem, but my question is how/what can I update
> > so that the table gets the "higher priority" so when I do the select it
> > looks
> > at the table and not the function when just using xxx and not dbo.xxx or
> > michael.xxx?
> >
> > Hope that makes sense. I'm in a real bind with this and would appreciate
> > any help. Thanks.
> >
> > John
> >
> >
> >
>
>|||A login name of michael in the connection string does not mean the user name
is also michael. A login name maps to a user name when the login is given
access to a particular database. A login michael could be a user named
michael in one database, a user named clerk in another, and a user named
user1 in a third database. If michael is the owner of a database, his user
name will be dbo in that database.
My guess is that the login michael is dbo on one database and not dbo on
another.
How exactly are you 'looking' at the function michael.xxx and how do you
know you are looking at the results of the function and not the table xxx.
Can you post the create table statements, the create function statement, and
the exact commands you are using for access. Also, when you are in a
database, you can execute SELECT USER_NAME() to find out what your user name
is in that database.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...
> Hi,
> Yes, this is very confusing, unfortunately this is what I have to work
> with. Thanks for responding though.
> the connection string specifies "michael" as the user when connecting to
> the database. On the local database server when it does the select * from
> xxx it is expecting to see the table which is owned by dbo, and it does
> produce these results. When it looks at the function, it does it with
> michael.xxx Note that michael is the owner of the database. I have
> attempted to detach the database and reattach it on another server and it
> works like that as well. However the server I need to get these tables
> and
> functions to I do not have access to reattach the database, only to move
> the
> database over via dts. The only clue I can find is the difference in the
> sp_help results which I provided earlier.
> Thanks again.
>
>
> "Kalen Delaney" wrote:
>> Hi
>> This is a bit confusing. Are you trying to access the table or the
>> function?
>> What user are YOU when you try to access the object?
>> Here are a couple of points that may help:
>> if you say
>> SELECT * from xxxx
>> you are selecting from a table. The only question is, which table? SQL
>> Server will first try to select from a table xxx that YOU own, if there
>> is
>> none, it will see if there is one owned by dbo.
>> If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
>> dbo, or anybody besides Michael, wants to access the table owned by
>> Michael, she must specify the owner:
>> SELECT * from michael.xxx
>> If you are selecting from a table valued function, you MUST specify an
>> owner, PLUS you must specify a parameter list, even if there are no
>> parameters.
>> So,
>> SELECT * from xxx()
>> is incorrect syntax because you didn't specify the owner
>> SELECT * from dbo.xxx()
>> will access the xxx FUNCTION owned by dbo
>> and
>> SELECT * from michael.xxx()
>> will access the xxx FUNCTION owned by michael
>>
>> I hope this helps. If not, please clarify
>> What object you are trying to access, and if it's a function, what are
>> the
>> parameters
>> Who owns that object
>> What user you are
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
>> news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
>> >I can't seem to solve this problem. I hope someone can help. I have a
>> >table
>> > which is owned by dbo called xxx. I also have a user define function
>> > also
>> > called xxx but owned by a different user. Everything works fine on the
>> > local
>> > database. From query analyzer if I type in select * from xxx
>> > everything
>> > works fine on the local database.
>> >
>> > Here's the problem. When I use dts to move this over to another
>> > server,
>> > and
>> > then try select * from xxx it returns the error "Server: Msg 208, Level
>> > 16,
>> > State 3, Line 1
>> > Invalid object name 'xxx'. If I try select * from dbo.xxx it works
>> > fine.
>> >
>> > Unfortunately because of the situation I can't go back in and change
>> > the
>> > user function name to something else, or change all the code to prefix
>> > the
>> > table with dbo.xxx.
>> >
>> > The only clue I've come up is that on the local machine when I do an
>> > sp_help
>> > it shows "xxx dbo user table 2004-02-16 19:14:17.280"
>> >
>> > but when I do an sp_help on the other server it shows "xxx michael
>> > table
>> > function 2005-01-08 13:21:57.500"
>> >
>> > that seems to explain my problem, but my question is how/what can I
>> > update
>> > so that the table gets the "higher priority" so when I do the select it
>> > looks
>> > at the table and not the function when just using xxx and not dbo.xxx
>> > or
>> > michael.xxx?
>> >
>> > Hope that makes sense. I'm in a real bind with this and would
>> > appreciate
>> > any help. Thanks.
>> >
>> > John
>> >
>> >
>> >
>>|||Oops, I did make one mistake. You do not have to specify an owner name when
selecting from a table valued function, but you DO need to include the param
list, even if there are no params.
When you say SELECT * from xxx you can only be selecting from a table or a
view, NOT from a function. The owner of the object will depend on the user
name your login name is mapped to.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...
> Hi,
> Yes, this is very confusing, unfortunately this is what I have to work
> with. Thanks for responding though.
> the connection string specifies "michael" as the user when connecting to
> the database. On the local database server when it does the select * from
> xxx it is expecting to see the table which is owned by dbo, and it does
> produce these results. When it looks at the function, it does it with
> michael.xxx Note that michael is the owner of the database. I have
> attempted to detach the database and reattach it on another server and it
> works like that as well. However the server I need to get these tables
> and
> functions to I do not have access to reattach the database, only to move
> the
> database over via dts. The only clue I can find is the difference in the
> sp_help results which I provided earlier.
> Thanks again.
>
>
> "Kalen Delaney" wrote:
>> Hi
>> This is a bit confusing. Are you trying to access the table or the
>> function?
>> What user are YOU when you try to access the object?
>> Here are a couple of points that may help:
>> if you say
>> SELECT * from xxxx
>> you are selecting from a table. The only question is, which table? SQL
>> Server will first try to select from a table xxx that YOU own, if there
>> is
>> none, it will see if there is one owned by dbo.
>> If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
>> dbo, or anybody besides Michael, wants to access the table owned by
>> Michael, she must specify the owner:
>> SELECT * from michael.xxx
>> If you are selecting from a table valued function, you MUST specify an
>> owner, PLUS you must specify a parameter list, even if there are no
>> parameters.
>> So,
>> SELECT * from xxx()
>> is incorrect syntax because you didn't specify the owner
>> SELECT * from dbo.xxx()
>> will access the xxx FUNCTION owned by dbo
>> and
>> SELECT * from michael.xxx()
>> will access the xxx FUNCTION owned by michael
>>
>> I hope this helps. If not, please clarify
>> What object you are trying to access, and if it's a function, what are
>> the
>> parameters
>> Who owns that object
>> What user you are
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
>> news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
>> >I can't seem to solve this problem. I hope someone can help. I have a
>> >table
>> > which is owned by dbo called xxx. I also have a user define function
>> > also
>> > called xxx but owned by a different user. Everything works fine on the
>> > local
>> > database. From query analyzer if I type in select * from xxx
>> > everything
>> > works fine on the local database.
>> >
>> > Here's the problem. When I use dts to move this over to another
>> > server,
>> > and
>> > then try select * from xxx it returns the error "Server: Msg 208, Level
>> > 16,
>> > State 3, Line 1
>> > Invalid object name 'xxx'. If I try select * from dbo.xxx it works
>> > fine.
>> >
>> > Unfortunately because of the situation I can't go back in and change
>> > the
>> > user function name to something else, or change all the code to prefix
>> > the
>> > table with dbo.xxx.
>> >
>> > The only clue I've come up is that on the local machine when I do an
>> > sp_help
>> > it shows "xxx dbo user table 2004-02-16 19:14:17.280"
>> >
>> > but when I do an sp_help on the other server it shows "xxx michael
>> > table
>> > function 2005-01-08 13:21:57.500"
>> >
>> > that seems to explain my problem, but my question is how/what can I
>> > update
>> > so that the table gets the "higher priority" so when I do the select it
>> > looks
>> > at the table and not the function when just using xxx and not dbo.xxx
>> > or
>> > michael.xxx?
>> >
>> > Hope that makes sense. I'm in a real bind with this and would
>> > appreciate
>> > any help. Thanks.
>> >
>> > John
>> >
>> >
>> >
>>|||Here a suggestion: try to add another user (with the role db_owner set?) and
use this account to connect. As this new account will have a different user
name, SQL-Server shouldn't look anymore for any object with michael as the
owner.
S. L.
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...
> Hi,
> Yes, this is very confusing, unfortunately this is what I have to work
> with. Thanks for responding though.
> the connection string specifies "michael" as the user when connecting to
> the database. On the local database server when it does the select * from
> xxx it is expecting to see the table which is owned by dbo, and it does
> produce these results. When it looks at the function, it does it with
> michael.xxx Note that michael is the owner of the database. I have
> attempted to detach the database and reattach it on another server and it
> works like that as well. However the server I need to get these tables
> and
> functions to I do not have access to reattach the database, only to move
> the
> database over via dts. The only clue I can find is the difference in the
> sp_help results which I provided earlier.
> Thanks again.
>
>
> "Kalen Delaney" wrote:
>> Hi
>> This is a bit confusing. Are you trying to access the table or the
>> function?
>> What user are YOU when you try to access the object?
>> Here are a couple of points that may help:
>> if you say
>> SELECT * from xxxx
>> you are selecting from a table. The only question is, which table? SQL
>> Server will first try to select from a table xxx that YOU own, if there
>> is
>> none, it will see if there is one owned by dbo.
>> If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
>> dbo, or anybody besides Michael, wants to access the table owned by
>> Michael, she must specify the owner:
>> SELECT * from michael.xxx
>> If you are selecting from a table valued function, you MUST specify an
>> owner, PLUS you must specify a parameter list, even if there are no
>> parameters.
>> So,
>> SELECT * from xxx()
>> is incorrect syntax because you didn't specify the owner
>> SELECT * from dbo.xxx()
>> will access the xxx FUNCTION owned by dbo
>> and
>> SELECT * from michael.xxx()
>> will access the xxx FUNCTION owned by michael
>>
>> I hope this helps. If not, please clarify
>> What object you are trying to access, and if it's a function, what are
>> the
>> parameters
>> Who owns that object
>> What user you are
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
>> news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
>> >I can't seem to solve this problem. I hope someone can help. I have a
>> >table
>> > which is owned by dbo called xxx. I also have a user define function
>> > also
>> > called xxx but owned by a different user. Everything works fine on the
>> > local
>> > database. From query analyzer if I type in select * from xxx
>> > everything
>> > works fine on the local database.
>> >
>> > Here's the problem. When I use dts to move this over to another
>> > server,
>> > and
>> > then try select * from xxx it returns the error "Server: Msg 208, Level
>> > 16,
>> > State 3, Line 1
>> > Invalid object name 'xxx'. If I try select * from dbo.xxx it works
>> > fine.
>> >
>> > Unfortunately because of the situation I can't go back in and change
>> > the
>> > user function name to something else, or change all the code to prefix
>> > the
>> > table with dbo.xxx.
>> >
>> > The only clue I've come up is that on the local machine when I do an
>> > sp_help
>> > it shows "xxx dbo user table 2004-02-16 19:14:17.280"
>> >
>> > but when I do an sp_help on the other server it shows "xxx michael
>> > table
>> > function 2005-01-08 13:21:57.500"
>> >
>> > that seems to explain my problem, but my question is how/what can I
>> > update
>> > so that the table gets the "higher priority" so when I do the select it
>> > looks
>> > at the table and not the function when just using xxx and not dbo.xxx
>> > or
>> > michael.xxx?
>> >
>> > Hope that makes sense. I'm in a real bind with this and would
>> > appreciate
>> > any help. Thanks.
>> >
>> > John
>> >
>> >
>> >
>>|||Thank you. It appears in the database that it works in the user is the dbo,
and in the other database the user isn't. I believe that will solve my
issue.
"Kalen Delaney" wrote:
> A login name of michael in the connection string does not mean the user name
> is also michael. A login name maps to a user name when the login is given
> access to a particular database. A login michael could be a user named
> michael in one database, a user named clerk in another, and a user named
> user1 in a third database. If michael is the owner of a database, his user
> name will be dbo in that database.
> My guess is that the login michael is dbo on one database and not dbo on
> another.
> How exactly are you 'looking' at the function michael.xxx and how do you
> know you are looking at the results of the function and not the table xxx.
> Can you post the create table statements, the create function statement, and
> the exact commands you are using for access. Also, when you are in a
> database, you can execute SELECT USER_NAME() to find out what your user name
> is in that database.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
> news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...
> > Hi,
> >
> > Yes, this is very confusing, unfortunately this is what I have to work
> > with. Thanks for responding though.
> >
> > the connection string specifies "michael" as the user when connecting to
> > the database. On the local database server when it does the select * from
> > xxx it is expecting to see the table which is owned by dbo, and it does
> > produce these results. When it looks at the function, it does it with
> > michael.xxx Note that michael is the owner of the database. I have
> > attempted to detach the database and reattach it on another server and it
> > works like that as well. However the server I need to get these tables
> > and
> > functions to I do not have access to reattach the database, only to move
> > the
> > database over via dts. The only clue I can find is the difference in the
> > sp_help results which I provided earlier.
> >
> > Thanks again.
> >
> >
> >
> >
> >
> > "Kalen Delaney" wrote:
> >
> >> Hi
> >>
> >> This is a bit confusing. Are you trying to access the table or the
> >> function?
> >> What user are YOU when you try to access the object?
> >>
> >> Here are a couple of points that may help:
> >>
> >> if you say
> >>
> >> SELECT * from xxxx
> >>
> >> you are selecting from a table. The only question is, which table? SQL
> >> Server will first try to select from a table xxx that YOU own, if there
> >> is
> >> none, it will see if there is one owned by dbo.
> >> If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
> >> dbo, or anybody besides Michael, wants to access the table owned by
> >> Michael, she must specify the owner:
> >>
> >> SELECT * from michael.xxx
> >>
> >> If you are selecting from a table valued function, you MUST specify an
> >> owner, PLUS you must specify a parameter list, even if there are no
> >> parameters.
> >>
> >> So,
> >> SELECT * from xxx()
> >> is incorrect syntax because you didn't specify the owner
> >>
> >> SELECT * from dbo.xxx()
> >> will access the xxx FUNCTION owned by dbo
> >>
> >> and
> >>
> >> SELECT * from michael.xxx()
> >> will access the xxx FUNCTION owned by michael
> >>
> >>
> >> I hope this helps. If not, please clarify
> >>
> >> What object you are trying to access, and if it's a function, what are
> >> the
> >> parameters
> >> Who owns that object
> >> What user you are
> >>
> >> --
> >> HTH
> >> --
> >> Kalen Delaney
> >> SQL Server MVP
> >> www.SolidQualityLearning.com
> >>
> >>
> >> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
> >> news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
> >> >I can't seem to solve this problem. I hope someone can help. I have a
> >> >table
> >> > which is owned by dbo called xxx. I also have a user define function
> >> > also
> >> > called xxx but owned by a different user. Everything works fine on the
> >> > local
> >> > database. From query analyzer if I type in select * from xxx
> >> > everything
> >> > works fine on the local database.
> >> >
> >> > Here's the problem. When I use dts to move this over to another
> >> > server,
> >> > and
> >> > then try select * from xxx it returns the error "Server: Msg 208, Level
> >> > 16,
> >> > State 3, Line 1
> >> > Invalid object name 'xxx'. If I try select * from dbo.xxx it works
> >> > fine.
> >> >
> >> > Unfortunately because of the situation I can't go back in and change
> >> > the
> >> > user function name to something else, or change all the code to prefix
> >> > the
> >> > table with dbo.xxx.
> >> >
> >> > The only clue I've come up is that on the local machine when I do an
> >> > sp_help
> >> > it shows "xxx dbo user table 2004-02-16 19:14:17.280"
> >> >
> >> > but when I do an sp_help on the other server it shows "xxx michael
> >> > table
> >> > function 2005-01-08 13:21:57.500"
> >> >
> >> > that seems to explain my problem, but my question is how/what can I
> >> > update
> >> > so that the table gets the "higher priority" so when I do the select it
> >> > looks
> >> > at the table and not the function when just using xxx and not dbo.xxx
> >> > or
> >> > michael.xxx?
> >> >
> >> > Hope that makes sense. I'm in a real bind with this and would
> >> > appreciate
> >> > any help. Thanks.
> >> >
> >> > John
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>

conflicting object names in sql server 2000

I can't seem to solve this problem. I hope someone can help. I have a tabl
e
which is owned by dbo called xxx. I also have a user define function also
called xxx but owned by a different user. Everything works fine on the loca
l
database. From query analyzer if I type in select * from xxx everything
works fine on the local database.
Here's the problem. When I use dts to move this over to another server, and
then try select * from xxx it returns the error "Server: Msg 208, Level 16,
State 3, Line 1
Invalid object name 'xxx'. If I try select * from dbo.xxx it works fine.
Unfortunately because of the situation I can't go back in and change the
user function name to something else, or change all the code to prefix the
table with dbo.xxx.
The only clue I've come up is that on the local machine when I do an sp_help
it shows "xxx dbo user table 2004-02-16 19:14:17.280"
but when I do an sp_help on the other server it shows "xxx michael table
function 2005-01-08 13:21:57.500"
that seems to explain my problem, but my question is how/what can I update
so that the table gets the "higher priority" so when I do the select it look
s
at the table and not the function when just using xxx and not dbo.xxx or
michael.xxx?
Hope that makes sense. I'm in a real bind with this and would appreciate
any help. Thanks.
JohnHi
This is a bit confusing. Are you trying to access the table or the function?
What user are YOU when you try to access the object?
Here are a couple of points that may help:
if you say
SELECT * from xxxx
you are selecting from a table. The only question is, which table? SQL
Server will first try to select from a table xxx that YOU own, if there is
none, it will see if there is one owned by dbo.
If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
dbo, or anybody besides Michael, wants to access the table owned by
Michael, she must specify the owner:
SELECT * from michael.xxx
If you are selecting from a table valued function, you MUST specify an
owner, PLUS you must specify a parameter list, even if there are no
parameters.
So,
SELECT * from xxx()
is incorrect syntax because you didn't specify the owner
SELECT * from dbo.xxx()
will access the xxx FUNCTION owned by dbo
and
SELECT * from michael.xxx()
will access the xxx FUNCTION owned by michael
I hope this helps. If not, please clarify
What object you are trying to access, and if it's a function, what are the
parameters
Who owns that object
What user you are
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
>I can't seem to solve this problem. I hope someone can help. I have a
>table
> which is owned by dbo called xxx. I also have a user define function also
> called xxx but owned by a different user. Everything works fine on the
> local
> database. From query analyzer if I type in select * from xxx everything
> works fine on the local database.
> Here's the problem. When I use dts to move this over to another server,
> and
> then try select * from xxx it returns the error "Server: Msg 208, Level
> 16,
> State 3, Line 1
> Invalid object name 'xxx'. If I try select * from dbo.xxx it works fine.
> Unfortunately because of the situation I can't go back in and change the
> user function name to something else, or change all the code to prefix the
> table with dbo.xxx.
> The only clue I've come up is that on the local machine when I do an
> sp_help
> it shows "xxx dbo user table 2004-02-16 19:14:17.280"
> but when I do an sp_help on the other server it shows "xxx michael table
> function 2005-01-08 13:21:57.500"
> that seems to explain my problem, but my question is how/what can I update
> so that the table gets the "higher priority" so when I do the select it
> looks
> at the table and not the function when just using xxx and not dbo.xxx or
> michael.xxx?
> Hope that makes sense. I'm in a real bind with this and would appreciate
> any help. Thanks.
> John
>
>|||Hi,
Yes, this is very confusing, unfortunately this is what I have to work
with. Thanks for responding though.
the connection string specifies "michael" as the user when connecting to
the database. On the local database server when it does the select * from
xxx it is expecting to see the table which is owned by dbo, and it does
produce these results. When it looks at the function, it does it with
michael.xxx Note that michael is the owner of the database. I have
attempted to detach the database and reattach it on another server and it
works like that as well. However the server I need to get these tables and
functions to I do not have access to reattach the database, only to move the
database over via dts. The only clue I can find is the difference in the
sp_help results which I provided earlier.
Thanks again.
"Kalen Delaney" wrote:

> Hi
> This is a bit confusing. Are you trying to access the table or the functio
n?
> What user are YOU when you try to access the object?
> Here are a couple of points that may help:
> if you say
> SELECT * from xxxx
> you are selecting from a table. The only question is, which table? SQL
> Server will first try to select from a table xxx that YOU own, if there is
> none, it will see if there is one owned by dbo.
> If you ARE dbo, it then can only be the table owned by dbo (dbo.xxx). If
> dbo, or anybody besides Michael, wants to access the table owned by
> Michael, she must specify the owner:
> SELECT * from michael.xxx
> If you are selecting from a table valued function, you MUST specify an
> owner, PLUS you must specify a parameter list, even if there are no
> parameters.
> So,
> SELECT * from xxx()
> is incorrect syntax because you didn't specify the owner
> SELECT * from dbo.xxx()
> will access the xxx FUNCTION owned by dbo
> and
> SELECT * from michael.xxx()
> will access the xxx FUNCTION owned by michael
>
> I hope this helps. If not, please clarify
> What object you are trying to access, and if it's a function, what are the
> parameters
> Who owns that object
> What user you are
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
> news:AC3D0C32-7A8F-4FF2-9526-1433646E9872@.microsoft.com...
>
>|||A login name of michael in the connection string does not mean the user name
is also michael. A login name maps to a user name when the login is given
access to a particular database. A login michael could be a user named
michael in one database, a user named clerk in another, and a user named
user1 in a third database. If michael is the owner of a database, his user
name will be dbo in that database.
My guess is that the login michael is dbo on one database and not dbo on
another.
How exactly are you 'looking' at the function michael.xxx and how do you
know you are looking at the results of the function and not the table xxx.
Can you post the create table statements, the create function statement, and
the exact commands you are using for access. Also, when you are in a
database, you can execute SELECT USER_NAME() to find out what your user name
is in that database.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...[vbcol=seagreen]
> Hi,
> Yes, this is very confusing, unfortunately this is what I have to work
> with. Thanks for responding though.
> the connection string specifies "michael" as the user when connecting to
> the database. On the local database server when it does the select * from
> xxx it is expecting to see the table which is owned by dbo, and it does
> produce these results. When it looks at the function, it does it with
> michael.xxx Note that michael is the owner of the database. I have
> attempted to detach the database and reattach it on another server and it
> works like that as well. However the server I need to get these tables
> and
> functions to I do not have access to reattach the database, only to move
> the
> database over via dts. The only clue I can find is the difference in the
> sp_help results which I provided earlier.
> Thanks again.
>
>
> "Kalen Delaney" wrote:
>|||Oops, I did make one mistake. You do not have to specify an owner name when
selecting from a table valued function, but you DO need to include the param
list, even if there are no params.
When you say SELECT * from xxx you can only be selecting from a table or a
view, NOT from a function. The owner of the object will depend on the user
name your login name is mapped to.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...[vbcol=seagreen]
> Hi,
> Yes, this is very confusing, unfortunately this is what I have to work
> with. Thanks for responding though.
> the connection string specifies "michael" as the user when connecting to
> the database. On the local database server when it does the select * from
> xxx it is expecting to see the table which is owned by dbo, and it does
> produce these results. When it looks at the function, it does it with
> michael.xxx Note that michael is the owner of the database. I have
> attempted to detach the database and reattach it on another server and it
> works like that as well. However the server I need to get these tables
> and
> functions to I do not have access to reattach the database, only to move
> the
> database over via dts. The only clue I can find is the difference in the
> sp_help results which I provided earlier.
> Thanks again.
>
>
> "Kalen Delaney" wrote:
>|||Here a suggestion: try to add another user (with the role db_owner set?) and
use this account to connect. As this new account will have a different user
name, SQL-Server shouldn't look anymore for any object with michael as the
owner.
S. L.
"zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...[vbcol=seagreen]
> Hi,
> Yes, this is very confusing, unfortunately this is what I have to work
> with. Thanks for responding though.
> the connection string specifies "michael" as the user when connecting to
> the database. On the local database server when it does the select * from
> xxx it is expecting to see the table which is owned by dbo, and it does
> produce these results. When it looks at the function, it does it with
> michael.xxx Note that michael is the owner of the database. I have
> attempted to detach the database and reattach it on another server and it
> works like that as well. However the server I need to get these tables
> and
> functions to I do not have access to reattach the database, only to move
> the
> database over via dts. The only clue I can find is the difference in the
> sp_help results which I provided earlier.
> Thanks again.
>
>
> "Kalen Delaney" wrote:
>|||Thank you. It appears in the database that it works in the user is the dbo,
and in the other database the user isn't. I believe that will solve my
issue.
"Kalen Delaney" wrote:

> A login name of michael in the connection string does not mean the user na
me
> is also michael. A login name maps to a user name when the login is given
> access to a particular database. A login michael could be a user named
> michael in one database, a user named clerk in another, and a user named
> user1 in a third database. If michael is the owner of a database, his use
r
> name will be dbo in that database.
> My guess is that the login michael is dbo on one database and not dbo on
> another.
> How exactly are you 'looking' at the function michael.xxx and how do you
> know you are looking at the results of the function and not the table xxx.
> Can you post the create table statements, the create function statement, a
nd
> the exact commands you are using for access. Also, when you are in a
> database, you can execute SELECT USER_NAME() to find out what your user na
me
> is in that database.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "zx6er93" <zx6er93@.discussions.microsoft.com> wrote in message
> news:519D7F1E-5CAF-41DE-9C13-F299BADC3732@.microsoft.com...
>
>

Friday, 24 February 2012

conflict as the same row being changed at the same time by two sites

Hello
I made merge replication between 2 machines successfully.
BUT,
when a user on each machine insert a new row in the same table in the same
time,the insert for the publisher wins and the insert for subscriber
deleted.
I need to keep the two inserts in the database.
And that also happen with the update.
Thanks alot
Tamer,
it sounds like you need to partition the inserts, so an insert on the
publisher will have a different id to an insert on the subscriber. You can
do this in various ways, but probably the most common is to use identity
ranges to ensure the values don't overlap.
HTH,
Paul Ibison

Sunday, 19 February 2012

Confirm password

Hi,
I have a login that has access to a certain database, when
i go to the logins session of EM to give the login acces
to another user, i map the user with the login in a new
database an i give him de db_owner role (Dev Host), when i
do the ok or aply, i'm prompt to confirm the password. For
me this is strange '? The login exists, i just want to
give the user access to another database.
Can anyone explain me this
Thanks in advance
Miguel CorreiaMaybe this will help...
http://support.microsoft.com/default.aspx?kbid=826161
Tim
>--Original Message--
>Hi,
>I have a login that has access to a certain database,
when
>i go to the logins session of EM to give the login acces
>to another user, i map the user with the login in a new
>database an i give him de db_owner role (Dev Host), when
i
>do the ok or aply, i'm prompt to confirm the password.
For
>me this is strange '? The login exists, i just want
to
>give the user access to another database.
>Can anyone explain me this
>Thanks in advance
>Miguel Correia
>.
>

Confirm new password? Please help.

Hi,
All of sudden, all my SQL 2K servers start to ask "Confirm
new password" when I assign a database access permission
to a local user. Even though I type in the correct
password, it still say "The confirmation password is not
correct". Right now I simply cannot assign any database
access to the local users. But the domain trusted users
don't have this problem. Does anyone know what the problem
is?
Thank you in advance for your help.
David...I'm trying to remember back - I had this occur ages ago. Have you either
service packed the server or switched client workstation? I "think" it was
fixed by service packing my client machine (to get it inline with the server
).
Alicia
Http://www.sqlporn.co.uk|||I applied SQL 2K security patch about a month ago, and
brought the builder version to 8.00.818. I just found the
article that addresses this problem.
http://support.microsoft.com/default.aspx?kbid=826161
Thanks for your information.
David.
>--Original Message--
>...I'm trying to remember back - I had this occur ages
ago. Have you either service packed the server or switched
client workstation? I "think" it was fixed by service
packing my client machine (to get it inline with the
server).
>Alicia
>Http://www.sqlporn.co.uk
>.
>|||This was a bug with the security rollup for SQL. You need to install build
819.
826161 FIX: You Are Prompted for Password Confirmation After You Change a
http://support.microsoft.com/?id=826161
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin,
I downloaded and applied the hotfix. The problem is gone.
But when I check the SQL server product version that is
still showing 8.00.818(SP3). Is that right? I thought it
should show the version # 8.00.819(SP3). Is there
something that I didn't do right?
Thank you for your help.
David.
>--Original Message--
>This was a bug with the security rollup for SQL. You
need to install build
>819.
>826161 FIX: You Are Prompted for Password Confirmation
After You Change a
>http://support.microsoft.com/?id=826161
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>|||to dive in, the hotfix just changes a DLL (can't remember which) and doesn't
affect sqlservr.exe so therefore you still see 818. This is expected
behaviour
regards,
Andy.
"David W." <anonymous@.discussions.microsoft.com> wrote in message
news:17d6101c44992$2faee360$a501280a@.phx
.gbl...[vbcol=seagreen]
> Hi Kevin,
> I downloaded and applied the hotfix. The problem is gone.
> But when I check the SQL server product version that is
> still showing 8.00.818(SP3). Is that right? I thought it
> should show the version # 8.00.819(SP3). Is there
> something that I didn't do right?
> Thank you for your help.
> David.
> need to install build
> After You Change a
> confers no rights.|||Hi David,
Sorry. That is correct. SQL Server .exe is still 818.
01-Jun-2003 01:02 2000.80.818.0 7,544,916 bytes Sqlservr.exe
This is the only component stamped 819.
01-Jun-2003 01:01 2000.80.819.0 492,096 bytes Semobj.dll
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Configuring Users - best Practices

Hello All,
I need to find out the best practices approach for user configuration in SQL
Reporting Services. I have teams of developers, who would be using VS.NET
development and deploying using VS.NET. Whats the best approach I could take
for development...Ideally I would like each team of users to have a folder
and so that they could deploy the reports only to those folders. Also how do
I set VS.NET to deploy a specifc sub folder.
Any thoughts or suggestions would be of good help.
Thanks,
ChrisYou can't deploy to a SubFolder in RS2K. Deployment from VS takes your
project name and uses it when you deploy. Only get one level.
There is a My Report folder you can turn on using the Report Manager under
system settings, but not sure that'll help you solve the problem you've
described.
If you need more, most straight forward approach is to go into Report
Manager after you deploy and move things around. Either that or use Report
Manager to 1st create the directory structure (folders / subfolders)
desired, then as Content Manager use the Upload File button on the tool bar
from within the subfolder to upload your reports.
If you find this to be burdensome, perhaps consider creating a VB script
like the one at
http://msdn.microsoft.com/library/en-us/rsamples/htm/rss_sampleapps_v1_1apd.asp
to automate the deployment process. Note, you could also do this
programmatically using the RS2K SOAP API. Perhaps as a program or daemon
that traverses a directory structure looking for .rdl files and then creates
the equivalent folders in RS to upload the reports to.
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Christopher Pragash" <chrispragash@.hotmail.com> wrote in message
news:uyCnuc6mEHA.1304@.TK2MSFTNGP09.phx.gbl...
> Hello All,
> I need to find out the best practices approach for user configuration in
> SQL
> Reporting Services. I have teams of developers, who would be using VS.NET
> development and deploying using VS.NET. Whats the best approach I could
> take
> for development...Ideally I would like each team of users to have a folder
> and so that they could deploy the reports only to those folders. Also how
> do
> I set VS.NET to deploy a specifc sub folder.
> Any thoughts or suggestions would be of good help.
> Thanks,
> Chris
>|||John H. Miller wrote:
> You can't deploy to a SubFolder in RS2K. Deployment from VS takes
> your project name and uses it when you deploy. Only get one level.
I don't think that's correct, John. I am able to deploy to any folder I
want in RS2K (SP1) by setting the TargetFolder property under Deployment in
the project's Property Pages.
I would think Chris could set up a separate folder for each developer and
set up permissions that allow a developer to publish only to his/her own
folder. Each developer would simply set the TargetFolder to the virtual
path to his/her folder.
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]|||Christopher Pragash wrote:
> Hello All,
> I need to find out the best practices approach for user configuration
in SQL
> Reporting Services. I have teams of developers, who would be using
VS.NET
> development and deploying using VS.NET. Whats the best approach I
could take
> for development...Ideally I would like each team of users to have a
folder
> and so that they could deploy the reports only to those folders.
you can:
* create separate security groups containing the various teams of
developers
* create the various target folders on the report server
* assign the pertinent security group to the 'Content Manager' role
(using Report Manager is the easiest way to do this)
the above assumes all users are in a single domain and that you have
the ability to create groups. if not, you can use report manager once
again to create the folders and then add each developer individually to
the content manager role.
> Also how do I set VS.NET to deploy a specifc sub folder.
once you have created a BI project in the VS designer, you right-click
the project and select properties. from there, you can figure the
appropriate subfolder to deploy to, as well as the target server.
hope this helps.
cheers,
~stuart|||John H. Miller:
> You can't deploy to a SubFolder in RS2K. Deployment from VS takes
> your project name and uses it when you deploy. Only get one level.
Jake Marx:
<< I don't think that's correct, John. I am able to deploy to any
folder I
want in RS2K (SP1) by setting the TargetFolder property under
Deployment in
the project's Property Pages. >>
Jake is correct. You can set the targetfolder property to be
arbitrarily deep. if the directory structure doesn't exist, it will be
created on the first deploy.

configuring user security problem!

Hi,
I am new to sql reporting services. I have just installed sql
reporting service sp1. We have our own web app which uses forms
authentication. I have a page where i use reportviewer control (which
came along with samples)...to run a report...there is a separate
physical report server and on that server, for the Reports virtual
directory i have "anonymous access" set. Within this i have set domain
user and password! Now microsot recommends to use "integrated windows
authentication". But as soon as i set it, my web page gives me report
server error that access is denied. I understand that my web page is
impersonating as local\ASPNET user.
I am very confused! can you help me?
I will really appreciate it
Thanks,
SauminIf you use forms authentication you have to create an extension to implement
it. You need to read up on how to do so if you need this capability. It
requires some effort on your part and it requires enterprise edition. I
suggest that you remove anonymous access for now (so you can learn how to
write a report etc and then implement form based security later). For now,
use integrated security (anonymous access and integrated security is
mutually exclusive). To supplement the info on security in the bol I suggest
this link from Scott Allen:
http://odetocode.com/Articles/215.aspx
Bruce L-C
"Saumin" <saumin_patel@.hotmail.com> wrote in message
news:35be972d.0408171307.3e2d9cab@.posting.google.com...
> Hi,
> I am new to sql reporting services. I have just installed sql
> reporting service sp1. We have our own web app which uses forms
> authentication. I have a page where i use reportviewer control (which
> came along with samples)...to run a report...there is a separate
> physical report server and on that server, for the Reports virtual
> directory i have "anonymous access" set. Within this i have set domain
> user and password! Now microsot recommends to use "integrated windows
> authentication". But as soon as i set it, my web page gives me report
> server error that access is denied. I understand that my web page is
> impersonating as local\ASPNET user.
> I am very confused! can you help me?
> I will really appreciate it
> Thanks,
> Saumin

Configuring SSIS package to read a value before executin

Hi,

Is it possible in any ways to Configure a SSIS package in such a way that based on the User Input the package runs. For e.g if there is a table which has say 10 distinct groups. Normal SSIS package would ideally pick all the data from the source to the Destination

I want to know how to configure in Such a way that I should be able to say Group X as the input and data related to GroupX alone should be copied.

Select * from SomeTable where GroupName = @.CongigVar.

how to achieve something like this?

thanks

Hello,

What kind of input are you intrested in getting?

If you have the group name that you want to get information from in a table, you can, in control flow, make a query to a table, and put it on a variable, then on the data flow you can use the variable as a parameter.

To use it on a parameter your query should be

"Select * from SomeTable where GroupName = ?"

Then in the parameter button on the left you choose the variable that will pass tha value to the question mark.

Is that good for you?

Regards,

|||

Hello,

I think that might just be what I need, but when I tried creating one, its failing with the error, 'Parameter Name Unrecognized'. I think am doing it wrongly. Can you post some links that explains how to put a value from a table onto a variable, and then use it as i/p to a Data flow?

thanks

|||

Nfrf wrote:

Hello,

What kind of input are you intrested in getting?

If you have the group name that you want to get information from in a table, you can, in control flow, make a query to a table, and put it on a variable, then on the data flow you can use the variable as a parameter.

To use it on a parameter your query should be

"Select * from SomeTable where GroupName = ?"

Then in the parameter button on the left you choose the variable that will pass tha value to the question mark.

Is that good for you?

Regards,

There's no need to store values in a table. You can parameterize your package by passing it values when you call it. Explore the /SET option of dtexec. All the information you require is in Books Online.

-Jamie

|||

Yes it is true. I just gave him a way.

The thing that i thnik he wanted to know was the parameter on the query.

Even so thanks

Friday, 17 February 2012

Configuring SqlDataSource at runtime

In a web site the user has to choose one out of several sql tables to deal with.

He will then be allowed to view the selected table data through a GridView, to insert a new row into and to update a row in the selected table by means of an array of TextBoxes created dynamically on the base of the selected table.

I think it is possible to solve the problem fully by properly configuring at run time an SqlDataSource.

I have solved the issue of data display by declaring in the code behind within the OnPageLoad sub the following:

SqlDataSource1.SelectCommand ="Select * FROM " & selectedTable

While for the GridView1 I have added the selected table columns to the columns collection as follows:

for i=0 to ColumnCount-1

Dim cacAs BoundField =New BoundField

cac.HeaderText = HeaderNamesArray(i)

cac.DataField = ProductNamesArray(i)

Me.GridView1.Columns.Add(cac)

next

I have difficulty on how to do similar declarations for the insertcommand and update command.

Update and Insert are a bit more tricky. Essentially you have to build the statement including all of the table fields.

Insert Into(Field1, Field2, etc) Values(Value1, Value2, etc)

You can get the field names from the .Columns collection on a dataset, but you'll also need the datatype for the field, since you'll have to delimit string values with single quotes.

Update involves on additional issue in that you have to know the primary key...

Update Table Set Field1=Value1, etc Where PK=PKValue of row that you are on.

In short you've got your work cut out for yourself, but it can be done.

Good luck.

|||

Ciao Alex

I understand that it is too complicated to go the way I thought feasible.

I will have to accept to use a DetailsView control which is not the best graphical solution for my application.

Thanks for your concrete help

federico

Sunday, 12 February 2012

Configuring Distributor

I keep getting the error message:
An error occurred during decryption.
There is no remote user 'distributor_admin' mapped to local user '(null)'
from the remote server 'repl_distributor'.
Changed database context to 'master'. (Microsoft SQL Server, Error: 15466)
I have tried a local server account (as BOL describes), I have tried using
my own SQL account (that has sa priviliges of course), I have even tried the
domain account that runs SQL Agent service (despite the note right there on
the screen that recommends not to!). I get this error at the end of all the
steps whether I try to create a new publication or if I try to configure the
publisher.
ANY help would be appreciated!!
Is this SQL 2005? And is it part of an upgrade? SQL Server 2000 used a
remote server called repl_distributor which uses the distributor admin
account. In SQL Server 2005 this has been replaced by a linked server. In
some cases I have seen the upgrade of a replication topology with a remote
distributor does not work successfully.
In my cases I had to add the linked server and password manually, IIRC.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:A3B17D13-3D6F-4B84-970A-F8FDDCB16144@.microsoft.com...
>I keep getting the error message:
> An error occurred during decryption.
> There is no remote user 'distributor_admin' mapped to local user '(null)'
> from the remote server 'repl_distributor'.
> Changed database context to 'master'. (Microsoft SQL Server, Error: 15466)
> I have tried a local server account (as BOL describes), I have tried using
> my own SQL account (that has sa priviliges of course), I have even tried
> the
> domain account that runs SQL Agent service (despite the note right there
> on
> the screen that recommends not to!). I get this error at the end of all
> the
> steps whether I try to create a new publication or if I try to configure
> the
> publisher.
> ANY help would be appreciated!!
|||It is a fresh install. Add a linked serer to what? could you send me the
details of adding the linked sever I am completely lost.
"Hilary Cotter" wrote:

> Is this SQL 2005? And is it part of an upgrade? SQL Server 2000 used a
> remote server called repl_distributor which uses the distributor admin
> account. In SQL Server 2005 this has been replaced by a linked server. In
> some cases I have seen the upgrade of a replication topology with a remote
> distributor does not work successfully.
> In my cases I had to add the linked server and password manually, IIRC.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:A3B17D13-3D6F-4B84-970A-F8FDDCB16144@.microsoft.com...
>
>
|||Check to ensure there is a linked server called repl_distributor and it uses
the security context of distributor_admin
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mrdj" <mrdj@.discussions.microsoft.com> wrote in message
news:D77607EC-1CA2-4D9E-8822-F4DCAA1D4976@.microsoft.com...[vbcol=seagreen]
> It is a fresh install. Add a linked serer to what? could you send me the
> details of adding the linked sever I am completely lost.
> "Hilary Cotter" wrote:
|||I tried adding the linked server and it STILL failed! But I'm making progess
because now I know exactly where the error is occuring, when adding the
linked server:
Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91
An error occurred during decryption.
Funny, I tried looking up this error number when all this first happened
(notice below, the text is a little different from my original error) and I
got nothing in my serarch results from Microsoft. I was able to get this to
work on another server, so at least I know it is some internal setting
specific to this server...Any ideas?
REALLY appreciate your help so far!!
"Hilary Cotter" wrote:

> Check to ensure there is a linked server called repl_distributor and it uses
> the security context of distributor_admin
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:D77607EC-1CA2-4D9E-8822-F4DCAA1D4976@.microsoft.com...
>
>
|||Hilary,
Should this linked server be created on the distributor, or the subscriber,
or both? I am seeing a similar error when trying to setup replication in my
production environment, although it worked fine in test (currently being
rebuilt, so I have no comparison to use atm). I am thinking this might be a
part of my problem.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uoTqv58THHA.3592@.TK2MSFTNGP03.phx.gbl...
> Check to ensure there is a linked server called repl_distributor and it
uses[vbcol=seagreen]
> the security context of distributor_admin
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "mrdj" <mrdj@.discussions.microsoft.com> wrote in message
> news:D77607EC-1CA2-4D9E-8822-F4DCAA1D4976@.microsoft.com...
the[vbcol=seagreen]
In
>