Owner Of Table Creation In SP

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


ADVERTISEMENT

Default Table Owner Using CREATE TABLE, INSERT, SELECT && DROP TABLE

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

The Owner Of The Table

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

How Do I Get Table Owner?

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

Table Owner

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

Table Owner

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

Owner Of The Table

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

The Owner Of The Table

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

Sql Db - Change Name For Owner Of Table

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

Change Table Owner To Dbo?

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

How To Change Owner Of A Table?

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

Change Table Owner

Nov 18, 2005

Is there an easy way to change myself as a specific table owner to dbo?

View 2 Replies View Related

How To Change Owner Of Table ?

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

Owner Of A Temp Table

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

Changing Table Owner

Jul 20, 2005

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

View 3 Replies View Related

How To Change The Table Owner To Dbo

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

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?

View 3 Replies View Related

Find The Owner Of A Table

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

How Do I Change The Table Owner?

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

Common Table Expressions With Table Creation

May 20, 2008

I have multiple Common Table Expressions i.e CTE1,CTE2,...CTE5.

I want to Create Temperory Table and inserting data from CTE5. Is this possible to create?

like:


create table #temp (row_no int identity (1,1),time datetime, action varchar(5))

insert into #temp select * from cte5

View 4 Replies View Related

Select Without Table Owner Failing ?

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

How Do I Change The Owner From A Table In A Database

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

Table Creation

Dec 20, 2005

Hi,

First time poster here, basically I have a second year university module on database design and for our coursework we have to model and create a database. One of the questions asks us to create a table that has a constraint on how many rows it can contain. I now that this is possible in some other databases, however I haven't seen a constraint that I could use on create table to limit the number of rows.. Does anyone now if this is possible?

Thanks,

Al

View 5 Replies View Related

Table Owner Name Shows Blank!! URGENT!!

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

Db_owner Role And Table Owner Issues

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

Dynamic Table Creation

Apr 19, 2007

Hi,I'm trying to create some tables dynamically based on the content of another table in the same database. I found a post that does what I want to do, but I can't get my code (that is similar to the post) to work.Given below is my code: 1 DECLARE @deptCode varchar(50), @numberOfDept int, @tableName varchar(MAX), @columnName varchar(MAX)
2 DECLARE @lengthDeptCode int, @lengthTableName int, @lengthColumnName int
3
4 SELECT @numberOfDept = COUNT(DISTINCT DeptCode)
5 FROM tbl_Department;
6
7 WHILE (@numberOfDept >=0)
8 BEGIN
9 SELECT @deptCode = DeptCode, @lengthDeptCode = LEN(DeptCode)
10 FROM tbl_Department;
11
12 SET @tableName = 'tbl_ProjectNumber'+@deptCode
13 SET @lengthTableName = LEN(@tableName)
14 SET @columnName = 'ProjectNumber'+@deptCode
15 SET @lengthColumnName = LEN(@columnName)
16
17 CREATE TABLE CAST(@tableName as char(@lengthTableName))
18 (
19 CAST(@columnName as char(@lengthColumnName)) int IDENTITY(1,1) NOT NULL
20 )
21
22 SET @numberOfDept = @numberOfDept - 1
23 END
  This is actually my first time using SQL programatically so I'm guessing there are alot of problems with it. I just don't know what exactly. The error I get is:Msg 102, Level 15, State 1, Line 18Incorrect syntax near '@tableName'. Thanks. 

View 1 Replies View Related

Dynamic Table Name Creation...

Jun 19, 2007

Hi there,I am trying to generate tables names on the fly depending on another table. So i am creating a local variable containing the table names as required. I am storing the tables in a local variable called@TABLENAME VARCHAR(16)and when i say SELECT * FROM @TABLENAMEit is giving me an error and I think I cannot  declare @TABLENAME as a table variable because I do not want to create a temp table of sorts.I hope I am clear.thanks,Murthy here 

View 6 Replies View Related

Creation Of Temporary Table

Jan 24, 2008

Hi All,
DECLARE @MyTableVar table( EmpID nvarchar(10) )
select login_id from cpm into @MyTableVar
 Above syntax is not working....
Actually i want to table variable and store the result returned by stored procedure.
How to do that...
Thanks and reagards
A

View 3 Replies View Related

Table Creation Question

Mar 20, 2008

I'm working on building my database tables, and I've ran into a bit of a situation.I have one table called 'Entities' that will hold information about people, places, and events.  I would like to have the user be able to add other properties to these entities ad hoc.For example, a person might have a birthdate they want to record.  So I was going to setup a DateProperties table that would have be setup something like this:DatePropertyIDEntityIDName (this would be 'Birthdate' in this situation)Value (the DateTime value)In that same way, I would also want users to be able to add something like Population to the places.  So I was going to setup a NumberProperties table that would have something like this:DatePropertyIDEntityIDNameValueI noticed the structures for the table is basically the same; the only thing that is changing is the Data Type for the Value.  I was thinking there has to be an easier/better way to do this sort of thing, perhaps all in a single table.  I'm still trying to learn all the latest stuff from .NET 3.5.  I was hoping to use LINQ to SQL, but I am willing to try anything.Any suggestions? 

View 1 Replies View Related

Dynanic Table Creation

Oct 24, 2005

Hello friendsI want to have a trigger for creating table on dialy basis.for e.g. I have a table say Data and on each day a new table is created like Data24Oct05 and so on. please help for writing the trigger for the same.thanks

View 4 Replies View Related

Table Creation Message

Feb 14, 2002

Hi!
After table creation I have got a message:

Warning: The table 'test' has been created but its maximum row size (11864) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

What does it mean?
Thank you,
Eve

View 1 Replies View Related

Table Creation Using Cursors?

Mar 12, 2012

I am starting to create a new database table based on an existing dbtable. My existing table has a list of ID's and a date range for each ID.

For example:
TableSource
ID Start DateTime End datetime
Y10012 01-12-12 13:00:00 01-19-12 13:00:00

So for this ID, I need my SQL statement to read this table, then create a new table and insert a new row for every second starting from the start date to the end date. I have several id's that span a week at a time. So I am expecting millions and millions of records once I am done.

End Result:

MainTable
IDFullTime
Y1001201-12-12 13:00:00
Y1001201-12-12 13:00:01
Y1001201-12-12 13:00:02
Y1001201-12-12 13:00:03
....
Y10012 01-12-19 13:00:00

Then once it completes reading S1001, it moves on to the next ID and appends it to the table. The date ranges are different for each ID- so it can't be hard coded.

View 5 Replies View Related

Table Creation && Update

Feb 17, 2004

Hi,

Can you create a table on one line and then update it on the next i.e. in the same query.

i.e.

create table test as select * from original
update test set test.a = .......

I know that they both work individually but whne i try to run them in one file i get "sql command not properly ended" - when i put a comma after 'original' i then get "invalid table name".

Am i just missing something

Cheers

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved