Changing Table Owner
Jul 20, 2005
Hi- apologies for asking a stupid newbie question, but I'm really stuck at
the moment. I need to change table ownership.
I've got an asp script which is looking for a table owned by the dbo role,
however the table was created under a different ownership. I understand the
problem, and almost understand the solution, but I can't seem to get all the
way.
THE PROBLEM (using [server].[database].[owner].[table])
[mgbsvr1].[dnn].[dnnadmin].[aspsearch]
needs to be
[mgbsvr1].[dnn].[dbo].[aspsearch]
I looked up the books online and found this syntax:
sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'
But I can't see how to use it, nor more importantly, where I should use it.
It won't work in the query tool in Enterprise Manager. If I need to create a
script (which I've never done before), how do I execute the script?
All help deeply appreciated
Manning, Sydney
View 3 Replies
ADVERTISEMENT
Nov 28, 2001
Hello,
Does anyone know how to change the owner of a DTS package? Currently it is set to someone's domain login and I want to change it to the system administrator account.
The reason for the change is because I think this is why the package will not run when scheduled on the server, but will run locally for the user.
Thanks,
Brent.
View 1 Replies
View Related
Sep 3, 2004
We have received a DB from one of our customer.
We want to change the DB and tables (and all objects) owner to sa
How can we do that ?
Thanks
View 4 Replies
View Related
Jul 20, 2005
Hi there,We have a user name convention here that specifies users in thefollowing way:jane.doejohn.smithIn order to change a database object I must specify the owner name alongwith the object name. Does anyone know the proper syntax for doing thiswith a user name that contains a period? When I put the user namefollowed by the object name in quotes such as 'jane.doe.tb_test_table',SQL Server can not find the object.Any help is appreciated.Kelly
View 1 Replies
View Related
Jul 20, 2005
I'm trying to create a new database and new login to allow a client(through my asp.net page) to create their own database, username andpassword. I've tried using the stored procedures I've found in BOL,but I can't get it to work right. It keeps saying that I can't assignthis username as the db owner since it's already a user for thedatabase. I then read other posts about reassigning the db owner toanother dummy account and then trying to reassign it to the new one,but that isn't working either. Can anyone look at my code and tell mewhat I'm doing wrong? Thanks.//create db loginqry = "sp_addlogin '" + username + "', '" + password + "', '" + dbname+ "'";cmd.CommandText = qry;cmd.ExecuteNonQuery();//grant login access to new database to new ownertmpCon = new SqlConnection("Data Source=ourserver;UserID=uid;Password=pwd;Initial Catalog=" + dbname + ";NetworkLibrary=DBMSSOCN;");tmpcmd = new SqlCommand("sp_grantdbaccess '" + username + "'",tmpCon);tmpCon.Open();tmpcmd.ExecuteNonQuery();tmpcmd.Dispose(); tmpcmd = null;tmpCon.Close(); tmpCon.Dispose(); tmpCon = null;//connect to new database under sa account and change owner to newaccounttmpCon = new SqlConnection("Data Source=ourserver;UserID=uid;Password=pwd;Initial Catalog=" + dbname + ";NetworkLibrary=DBMSSOCN;");tmpcmd = new SqlCommand("sp_changedbowner'HolderUserDoNotDelete'",tmpCon);tmpCon.Open();tmpcmd.ExecuteNonQuery();tmpcmd.CommandText = "sp_changedbowner '" + username + "'";tmpcmd.ExecuteNonQuery();tmpcmd.Dispose(); tmpcmd = null;tmpCon.Close(); tmpCon.Dispose(); tmpCon = null;
View 1 Replies
View Related
Jan 29, 2002
Usually all the user datatypes in our databases have the owner dbo.
One has a few that are owned by a user with dbo rights. I am trying to change them to dbo owner.
Sp_changeobjectowner gives 'object does not exist'
Any ideas
Jim
View 1 Replies
View Related
Jan 23, 2007
hello,
i recently changed the machine name of my development computer and am now no longer able to create or view any diagrams for the sql database that was created by the old machine name user. i receive an error where I cannot make myself "the dbo of this database." i can see the old name in the "owner" properties field of the mdf database, but the box is grayed out and i am unable to change it to the new machine/user name. is there a way to change the owner of the database to my new machine/user name? the new name has admin rights and the computer is a standalone workstation not connected to a network.
i am using sql server 2005 express edition with visual web developer.
thanks!
View 7 Replies
View Related
Jul 20, 2005
Hi GuysWonder if you could help me.Basically I produce an accounts package that uses a SQL 2000 DB as theRDBMS. I always instruct users to login as 'sa' and the relevantpassword when doing an update to my program, as sometimes I need to dodatabase changes for new stuff.Found that one of my users has not only logged in with their loginname (in this case Edward), but have also made this login a 'db owner'so that when I created 2 new user-defined data types they belong toEdward rather than dbo.This must have happened a long time ago, but now that they want tomove Edward round the roles and/or delete him from a copy of thedatabase that they have, they can't because he's the owner of theseuser-defined types.This brings me to the reason for my post, how can I change the ownerfrom Edward to dbo for these data types? I found an article ontechnet of how to do this, but when it suggests changing myuser-defined type to standard format it doesn't seem to work.Any ideas?RgdsRobbie
View 1 Replies
View Related
Apr 13, 2006
Im having trouble changing the DB owner from Kate to Bob, because Kate owns some objects in the DB.
I first try to run sp_changedbowner Bob....but it tells me:
The proposed new database owner is already a user in the database.
When I run scripts on a table that such as;
sp_changeobjectowner 'customers', 'Bob'...I get the message of
Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object 'customers' does not exist or is not a valid object for this operation.
Please help with what I can do.
Thank you
View 3 Replies
View Related
Nov 21, 2006
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies
View Related
Jul 27, 2006
Hello,
I need to user full name for the table as seen below.
SELECT @RowCount = COUNT(*)
FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
T2 may be re-created by different users, how can I get this working for all users if the creator is not dbo?
View 5 Replies
View Related
Mar 9, 2005
I need to the table owner.
Not from in Enterprise Manager, but from my app. (it's written in Delphi).
Any suggestions?
Thank you,
Keith
View 2 Replies
View Related
Nov 24, 2005
Hi,
I've a database table named MYTABLE
It's owner is T01.
Now when I log on as MA I want to do a select like this:
select * from MYTABLE -- Gives me an error.
Select * from T01.MYTABLE -- goes fine..
I really don't want to specify the owner for every table. All tables have the owner T01.
Is it possible to use some statement before executing my sql statement.
Something like this :-=
Use owner T01
select * from MYTABLE.
Is it possible ?
//Martin
View 3 Replies
View Related
Aug 4, 2006
hi,how do i change the table's owner as dbo using sql server 2000 query.kalai
View 2 Replies
View Related
Jul 20, 2005
I've got this question:Is it possible to assign the 'current owner' using a query like thefollowing one:set current schema OWNER1 (in DB2 sql, this allow me to reference the tableswithout an explicit indication of the owner (es: after this query I cansimply write 'Select * from dummy', instead of 'Select * fromOWNER1.dummy')).Thank youFederica
View 2 Replies
View Related
Jul 27, 2006
Hello,
I need to user full name for the table as seen below.
SELECT @RowCount = COUNT(*)
FROM T1 c INNER JOIN [MyInstance].MyDB.dbo.T2 b ON c.T1_ID=b.T2_ID
T2 may be re-created by different users, how can I get this working for all users if the creator is not dbo?
View 4 Replies
View Related
Jul 19, 2005
Is it possible to change the name of the owner of a sql database table?Thanks,Zath
View 4 Replies
View Related
Oct 31, 2000
We set up a few tables while being logged in as a different user. Now, those tables are not accessible via some ASP pages because they aren't DBO-owned tables. Relationships do exist on these tables, but no significant data. Is there a way to change the owner, or do I need to delete the table and start over?
View 2 Replies
View Related
Oct 6, 1998
Already know how to change the owner of a database, but that doesn`t seem to change the owner of an individual table.
Any Help???
Please??
View 2 Replies
View Related
Nov 18, 2005
Is there an easy way to change myself as a specific table owner to dbo?
View 2 Replies
View Related
Dec 14, 2006
Hi everyone and Happy Holidays!
I've got a problem with table creation in stored procedures (SQL Server 2000). We've got an application where the user login only has rights to execute stored procedures. The problem is that a stored proc is dynamically creating a table and so the owner of that table is being assigned to whatever login the application is using instead of dbo. It's causing numerous issues. Is there any way that this can be avoided or changed without granting the user sa privileges?
Thanks in advance,
Cat
View 14 Replies
View Related
Aug 22, 2006
I have created some of tables in mydatabase .
Now i want to change owner of that table .
How can i do ?
Thank you very much .
View 1 Replies
View Related
Jul 23, 2005
is there a way to query the system table in SQL Server to determine theowner of a temp table? I doubt this is possible because it seems asthough everyone is aliased as db_owner.
View 1 Replies
View Related
Oct 19, 2007
I have verified that myaspx error was due to the owner of the table is not dbo.
However, I don't know how to use sp_changeobjectowner to change
the table owner to dbo. I even tried sp_changetableowner, but didn't work.
SQL Server gave me a hint of '@newowner' parameter; still, I could not get it.
TIA,
Jeffrey
View 3 Replies
View Related
Apr 22, 2015
I am writing a stored procedure which should rebuild all indexes of all tables in all databases of a SQL server 2000. I use DBCC DBREINDEX (<table name>, ' ', 90)
Since we can't use USE in stored procedures i have to loop thru database names and construct the <table name> in the following format: <database name>.<owner name>.<table name>
At the time of execution the script knows the database name and the table name, but not the owner. i innocently assumed dbo to be the owner of all of my tables. But not so.
My script stumbled upon the following table name Akord.User1.T$reg
There are two problems here:
a) the owner is not dbo
b) the table name T$reg has a dollar sign as part of the name
I have a question
- is it possible to enumerate thru all tables in a database (USE is not allowed) regardless of their owners?
View 3 Replies
View Related
Apr 24, 2008
I know this is a stupid question, but how can I deturmine who the owner of a tabel is?
View 3 Replies
View Related
Nov 16, 2006
I have to make a copy of an existing database and call it something different. That part was easy because I simply backed it up and then restored it with a different name.
OrigDbName
Table1 Owner OrigDdName
Table2 Owner OrigDdName
But now I find that all the tables still have the original database name as the owner and I cannot refer to them programmatically unless I use the original database name.
NewDbName
Table1 Owner OrigDdName
Table2 Owner OrigDdName
So how do I change the owner of the tables to the new database name. What I really need is;
NewDbName
Table1 Owner NewDdName
Table2 Owner NewDdName
Please be as specific as possible because I am not a DBA and I am out of my depth once it gets beyond the basics of using commands like Alter Table.
Thank you.
View 5 Replies
View Related
Mar 1, 2008
i am newbie
i created a database in mssql2000 testdb1 and copied data and views from another testdb2 located on another server on testdb1 when i execute select * from table1 i am getting
Invalid object name 'table1'.
if i give
select * from xxx.table1;
xxx being owner name. it works.
how to make
select * from table1
work
View 1 Replies
View Related
Jul 20, 2005
Dear Group,I have a table in the a database that I need to change the owner to dbo.How can I do this.Thanks in advance.Jeff Magouirk
View 1 Replies
View Related
Oct 17, 2001
I have a table which has a blank as owner name when I see on Enterprise manager. Sp_changeobjectowner is not working on this to change the owner name to dbo.I am still able to create another table with same name under dbo.But I want to get rid of the table which has no ownername. When i run the drop table command it doesn't drop that!!It says invalid object!!Any help!!
thanks,
Di.
View 1 Replies
View Related
Feb 23, 2004
Hi,
I have given a user db_owner role in a database. When he creates a table using Enterprise manager the table owner is dbo. When he creates a table using Query Analyzer the table owner is the user. eg
Enterprise Manager = dbo.Table1
Query Analyer = username.Table1
This causes a problem when the user is writing web applications. Is this an error in the way i have set up permissions ? How can i make them behave the same way?
Thanks for your help.
View 4 Replies
View Related
Dec 14, 2006
Hi,
I am creating reports for an application, that when installed can have various different table owners/qualifiers depending on how client created the DB. How can I create standard reports across all the DB without hardcoding the tablenames qualifier/owner in the dataset query? Again the table structure remain the same just the qualifiers may be different. Any help would be great.
--DCB
View 1 Replies
View Related
Feb 7, 2006
Hi
Having some issues with our apps.
We are trying to get our applications to work with sql2005.
Ive got the databases "setup", and all our apps run fine...
...except for when queries are made without the owner of the
table being specified in the query.
The connection is opened with the username that is associated with that owner.
And it fails in Manager as well. Is there something im missing, because you should
be able to do this.
eg:
select * from <table_name>
Gives the error:
Msg 208, Level 16, State 1, Line 1
Invalid object name '<table_name>'.
However if i were to query like this:
select * from <owner>.<table_name>
it works fine.
View 11 Replies
View Related
