| Author |
 Topic  |
|
Seleck
Starting Member
Germany
14 Posts |
 Posted - 13 November 2003 : 06:35:21
|
I am running two differnt snitz in our intranet enviornment with MS SQL server2000 + NT Authentification.
One snitz is online and running properly. 2nd. one i am using for experimental purpose on same server with different database name and folder (totally seperate, only same server).
Both Snitz are using the same UID define in config.asp as strConnString but diferent database name
for example : in snitz1, UID=test and databse=snitz1
and snitz2, UID=test and database=sniz2
At start, both dbowner are same (test)create via setup.asp defined in config.asp
But Everytime when i import the database from snitz1 to snitz2 via internalservertool DTS, it change the owner of databse into dbo.
This of course cause an error "ODBC Drivers error 80040e14", because the owner defined in config.asp in strConnString is not dbo and cause an error:
Anyone know any suggestion how to import database from one snitz to another without changing of dbowner?
|
SEL
--------
http://www.bradseleck.com |
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
Posted - 13 November 2003 : 07:01:50
|
| there's a stored procedure you can run to change owners, sp_changeobjectowner I think, do a search on books online |
The UK MkIVs Forum |
 |
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 13 November 2003 : 07:18:13
|
| this should not happen if you set the correct users for each of the connections when doing dts, are you by chance using the sa account when you do the transfers ? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Seleck
Starting Member
Germany
14 Posts |
Posted - 13 November 2003 : 07:32:44
|
| No i am using at server my own NT-Loggin account while running dts. it ask you the source and target name of database, you can make choice of both from a list showed in a rollover field. There is no choice to set the dbowner or user while proceeding this process. You can only use Window-Authentification or SQLServer-Authentification. |
SEL
--------
http://www.bradseleck.com |
|
|
|
Seleck
Starting Member
Germany
14 Posts |
Posted - 13 November 2003 : 07:39:56
|
further i did this dts procedure with Window-Authentfication. When you use the SQLServer-Authentification, it ask you username and Password, and as i put there the name of user define in config.asp (test: who also got full write and defined as dbowner), dts denied to work
Hey DavidRhodes, can you please give me more detail about "sp_changeobjectowner" ?, it should only change the apropriate tablefields beeing used by snitzer not the whole db. |
SEL
--------
http://www.bradseleck.com |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 13 November 2003 : 07:56:29
|
when doing a DTS transfer you can set the user you want to use to connect to both the source and destination databases. the one you set to connect to the destination is the one it will use when creating the tables.
if dts did not work when you used the user in your config.asp, then it does not have all the correct permissions. |
|
|
|
DavidRhodes
Senior Member
United Kingdom
1222 Posts |
Posted - 13 November 2003 : 07:57:50
|
Taken from books online (press F1 in Enterprise Manager  )
sp_changedbowner
Changes the owner of the current database.
Syntax
sp_changedbowner [ @loginame = ] 'login'
[ , [ @map = ] remap_alias_flag ]
Arguments
[@loginame =] 'login'
Is the login ID of the new owner of the current database. login is sysname, with no default. login must be Microsoft® SQL Server™ login or a Microsoft Windows NT® user that already exists. login cannot become the owner of the current database if it already has access to the database through an existing alias or user security account within the database. To avoid this, drop the alias or user within the current database first.
[@map =] remap_alias_flag
Is the value true or false, which indicates whether existing aliases to the old database owner (dbo) are mapped to the new owner of the current database or dropped. remap_alias_flag is varchar(5), with a default of NULL, indicating any existing aliases to the old dbo are mapped to the new owner of the current database. false indicates that existing aliases to the old database owner are dropped.
Return Code Values
0 (success) or 1 (failure)
Remarks
After sp_changedbowner is executed, the new owner is known as the dbo user inside the database. The dbo has implied permissions to perform all activities in the database.
The owner of the master, model, or tempdb system databases cannot be changed.
To display a list of the valid login values, execute the sp_helplogins stored procedure.
Executing sp_changedbowner with only the login parameter changes database ownership to login and maps the aliases of users who were previously aliased to dbo to the new database owner.
Permissions
Only members of the sysadmin fixed server role or the owner of the current database can execute sp_changedbowner.
Examples
This example makes the user Albert the owner of the current database and maps existing aliases to the old database owner to Albert.
EXEC sp_changedbowner 'Albert'
|
The UK MkIVs Forum |
|
|
|
Seleck
Starting Member
Germany
14 Posts |
Posted - 14 November 2003 : 06:30:35
|
I make it work with DTS though i had to change into each tablefield manually the sourcetarget from "dbo" to "test".
Every thing work properly and you can see all datas in test snitz too. But ... well i wish this But isn't there..
Everytime when a new user try to post a new registration, it cause following error:
=================================================================
Compilingerror in Microsoft VBScript- Error '800a03ea'
Syntaxerror
/iisHelp/common/500-100.asp, line 129
elseIf (objASPError.Description > "") Then
Microsoft OLE DB Provider for ODBC Drivers- Error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]the ZERO value cannot insert in the MEMBER_ID-columne of SNITZ2.dbo.FORUM_MEMBERS-Tabell. the columne do not allow Zero-Value. Error by INSERT.
/forum2/register.asp, line 599
=================================================================
All tabell allow the zero value so that can't be the reason.
Anysuggestion or help ???
|
SEL
--------
http://www.bradseleck.com |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Seleck
Starting Member
Germany
14 Posts |
Posted - 17 November 2003 : 02:28:18
|
Oh well sorry ruirib,
I won't dare to ask any thing here, if you are going to command me not to ask any thing here or you could have referred to the link of this topic. Thank you for your friendly command. |
SEL
--------
http://www.bradseleck.com |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 17 November 2003 : 03:34:14
|
You might find this usefull in the future when in similar circumstances it will change owners on all user tables in a database for you in one step.
SELECT 'EXEC(''sp_changeobjectowner @objname = '''''+
ltrim(u.name) + '.' + ltrim(s.name) + ''''''
+ ', @newowner = dbo'')'FROM sysobjects s,
sysusers u
WHERE s.uid = u.uid
AND u.name <> 'dbo'
AND xtype in ('V', 'P', 'U')
AND u.name not like 'INFORMATION%'
order by s.name
basically just change the bit in red to the new owner you require. |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 17 November 2003 : 04:53:03
|
quote:
Originally posted by Seleck
Oh well sorry ruirib,
I won't dare to ask any thing here, if you are going to command me not to ask any thing here or you could have referred to the link of this topic. Thank you for your friendly command.
At Snitz we strongly discourage double posting. It's a waste of space and effort from the mods/admins team. It's just too bad if you don't like it, but I won't change the way I moderate the forums assigned to me, just to please you . |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Seleck
Starting Member
Germany
14 Posts |
Posted - 17 November 2003 : 06:10:25
|
Hi Gremlin.
Million thanx for this solution. I am using this now its working perfectly. Me and our Intranet users will enjoy snitz full performance now . Thanx again
regards |
SEL
--------
http://www.bradseleck.com |
|
|
|
Seleck
Starting Member
Germany
14 Posts |
Posted - 17 November 2003 : 06:26:59
|
Ruirib,
I am agree with you about double pasting, but did you read my problem realy carefully ? I guess you didn't.
It has nothing to do with the previous subject. It says how to keep the owner of a db while importing a databse from an existing db to another one.
You won't please me telling me you are a admin and you have a gun to shoot anyone who paste double message. Just think some people are new here and they are in a learning procedure and they might ask sometime stupid help and questions, those have been asked mayn time, Are you going to deal them with same attitude ??
There are thousands of subjects beeing repeated many time in such forums. sure its not good and you guys as forum's admin have to advice people avoiding this. But ruirib, a friendly way won't be bad and people would understand instead feeling insulted.
you might be Moderator or superadmin of this forum ruirib, but ton make the music and you are not doing a favor to snitz, when you start beeing unfriendly with people. You may take an example of Gremilin, He was able to read, detect the problem and answer me with a pefect working solution. Me and users of our intranet are thankfull for his help.
you may delete me and all my posted message now from this forum, because i am crticising you and your unfriendly attitude, but this won't change the fact, Your this "OK!!" was very unfriendly.
regards |
SEL
--------
http://www.bradseleck.com |
Edited by - Seleck on 17 November 2003 06:30:34 |
|
|
|
Topic |
|
Topic Locked
