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?
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.
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
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;
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'
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.
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
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.
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.
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
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?
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?
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.
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.
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?
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.
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
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.
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?
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.
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>
