Transact SQL :: Name A Table Regardless Of Its Owner
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?
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.
Hi- apologies for asking a stupid newbie question, but I'm really stuck atthe 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 theproblem, and almost understand the solution, but I can't seem to get all theway.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 ascript (which I've never done before), how do I execute the script?All help deeply appreciatedManning, Sydney
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 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>
autorized user: sa any user : nuran temporary table: birtablo
I need a stored procedure will execute by sa and it will create some required temporary tables for each users. For example table name is birtablo. I mean sa will create table for nuran, and when I checked the owner of the table (birtablo) I want to see nuran not dbo. sa will execute following command:
create table nuran.birtablo (...........)
Is it possible to cerate a table by sa on behalf of any user? If it is, could you please explain?
I am looking for a Sql query to verify the inserted values from one table(in CSV file) with another table(in sql database)
For example: I have below Values column that is present in once CSV file, after my data migration the values get stored in Results table under Message column.
I need to very whether values(1X,1Y) are inserted in Message record "successfully inserted value 1X"
Values (CSV) 1X 1Y
Results Table(SQL) CreatedDate Message 2015-08-04 08:45:29.203 successfully inserted value 1X 2015-08-04 08:44:29.103 TEst pass 2015-08-04 08:43:29.103 successfully inserted value 1X 2015-08-04 08:42:29.203 test point 2015-08-04 08:35:29.203 successfully inserted value 1Y 2015-08-04 08:30:29.203 Test Pass 2015-08-04 08:28:29.203 successfully inserted value 1Y
If all values are inserted:
Output: All values from values table are inserted successfully Total count of values inserted: 2 If only few values are inserted, example only 1X from Values table is inserted in Message
Example: Results Table CreatedDate Message 2015-08-04 08:45:29.203 successfully inserted value 1X 2015-08-04 08:44:29.103 TEst pass 2015-08-04 08:43:29.103 successfully inserted value 1X 2015-08-04 08:42:29.203 test point
Output: All values from values are not inserted successfully in result table. Total count of values inserted: 1 Missing Values not inserted in results table are: 1Y
convert my table(like picture) to hierarchical structure in SQL. actually i want to make a table from my data in SQL for a TreeList control datasource in VB.net application directly.
ProjectID is 1st Parent Type_1 is 2nd Parent Type_2 is 3rd Parent Type_3 is 4ed Parent
I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-
Faculty_Subject.Class_Id=@Class_Id
Then i don't get all subjects from subject table, how this can be achieved.
Sql Code:- GO ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details] @Class_Id int AS BEGIN
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set tables multiple times
