| Author |
Topic |
|
radha
Starting Member
11 Posts |
 Posted - 2004-10-08 : 07:16:13
|
| Two Datbase are there ex test and test1. I need to copy the test Database to test1.So i used DTS Wizard.I can copy all table,SP,and data also.Eveytime I got "Failed to copy Objects,SETUSER permission not there' like this.But All objects and Data copied perfectly.How to resolve this problem. After generating the Sql Script,If i execute that script ,Same Error I got "SetUSER Permission Denied".How to resolve. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-08 : 14:57:50
|
| If you just want to copy a database from one server to the next, then use BACKUP/RESTORE.Tara |
 |
|
|
radha
Starting Member
11 Posts |
Posted - 2004-10-08 : 22:42:53
|
| In my client place.I don't have permission to restore.So I need to use Dts Wizard.Help me. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-10 : 10:01:29
|
quote:
Originally posted by radha
In my client place.I don't have permission to restore.So I need to use Dts Wizard.Help me.
when you say you're in your client place, do you mean, you want to execute the dts from same server?if yes, then just login to the server using enterprise manager using your login in the server and run the package. if client doesn't have enterprise manager, you may want to explore osql. not sure though, i think you need to have the dts scripted. |
|
|
|
radha
Starting Member
11 Posts |
Posted - 2004-10-11 : 02:58:32
|
| Ya.Iam using DTS.But I got "Failed to copy.Set user Permission Denied".But Tables and Sp all copied Succeefully.How to give SET USER Permission |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-11 : 04:24:25
|
your dts has logins included and with your current permission, you have no rights to perform this.from BOLquote:
SETUSERAllows a member of the sysadmin fixed server role or db_owner fixed database role to impersonate another user.Important SETUSER is included in Microsoft® SQL Server™ 2000 only for backward compatibility, and its usage is not recommended. SETUSER may not be supported in a future release of SQL Server.SyntaxSETUSER [ 'username' [ WITH NORESET ] ] Arguments'username'Is the name of a SQL Server or Microsoft Windows NT® user in the current database that is impersonated. When username is not specified, the original identity of the system administrator or database owner impersonating the user is reestablished.WITH NORESETSpecifies that subsequent SETUSER statements (with no specified username) do not reset to the system administrator or database owner.RemarksSETUSER can be used by members of the sysadmin or db_owner roles to adopt the identity of another user in order to test the permissions of the other user.Only use SETUSER with SQL Server users. It is not supported with Windows users. When SETUSER has been used to assume the identity of another user, any objects that are created are owned by the user being impersonated. For example, if the database owner assumes the identity of user Margaret and creates a table called orders, the orders table is owned by Margaret not the system administrator.SETUSER is not required to create an object owned by another user, because the object can be created with a qualified name that specifies the other user as the owner of the new object. For example, if user Andrew, who is a member of the db_owner database role, creates a table Margaret.customers, user Margaret owns customers not user Andrew.SETUSER remains in effect until another SETUSER statement is issued or until the current database is changed with the USE statement.PermissionsSETUSER permissions default to members of the sysadmin fixed server role and are not transferable.
|
|
|
|
radha
Starting Member
11 Posts |
Posted - 2004-10-11 : 23:18:20
|
| Thank You.But How to identify Whether the SET YSER Permission isthere or not for particular DB Owner.Can you Tell me please! |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-12 : 00:53:11
|
| i think you should post your dts package settings.what are the objects you're copying. if you used wizard then what are the options you checked? |
|
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-10-12 : 05:15:54
|
| Are you creating DTS packages or are you using Import/Export Wizard. Depending on your experience level, you might find Import/Export wizard easier.Right click on the "Databases" and select "All Tasks" then "Import Data..." or "Export Data...".Select the source and destination databases and select the tables and other objects you wish to transfter.Hemanth GorijalaBI Architect / DBA... |
|
|
|
radha
Starting Member
11 Posts |
Posted - 2004-10-13 : 03:14:38
|
| Ya.I am using Import/Export DTS Wizard for Copying all Object Including Tables,Stored Procedure,Data all. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-13 : 03:38:56
|
quote:
Originally posted by radha
Ya.I am using Import/Export DTS Wizard for Copying all Object Including Tables,Stored Procedure,Data all.
and when you run the package, what permissions do you have? you should have atleast dbo coz you're dealing with other objects aside from data. are you by any chance including logins in your package?--------------------keeping it simple... |
|
|
|
|
SET USER Permission Problem in DTS Wizard!