Ownership Of Tables

Nov 17, 1999

Is there anyway to change the ownership of a table.. I have developers who create tables and the tables are then shown to be owned by them.. These developers are in the db_owner database role.. The application will not run successfully unless the table is owned by the dbo..

The only way I see that I can handle this is to have them generate a script and have myself (full sa) run it..

Any other suggestions?
Thanks

View 1 Replies


ADVERTISEMENT

Switching Schema Ownership Of Views, Stored Procedures, Tables

Feb 20, 2007

Hi all--I used the following type of script to migrate tables, views and stored procedures from 'dbo' to 'hqmsuser':
alter authorization on dbo.PK_T_PQ_MASTER to HQMSUSER
alter schema HQMSUSER transfer dbo.PK_T_PQ_MASTER
go

This was per recommendations from the BOL to use instead of the 'sp_changeobjectowner' procedure. I returned the following error:

Msg 15346, Level 16, State 1, Line 3
Cannot change owner for an object that is owned by a parent object. Change the owner of the parent object instead.
Msg 15347, Level 16, State 1, Line 4
Cannot transfer an object that is owned by a parent object.

How do I find and change the parent object on this from 'dbo' to a non-dbo schema ownership?

View 1 Replies View Related

DBO Ownership

Sep 20, 2007

Sometime ago, I had read a posting here about where in the system we can specify default owner of all objects in user database as being a DBO.

The topic came about, because somebody found out that if an object referred to in a query, does not have a fully qualified name, then it takes some time for SQL server to figure out what the owner of the object is; this causes performance problems.
e.g. select * from employees and select * from dbo.employees.

If anybody knows what that setting is...I will really appreciate it

Regards
Paresh Motiwala
Boston, USA

View 4 Replies View Related

Database Ownership

Apr 1, 2008

My system was crashing so I reinstalled everything. including SQL 2005 Express with management studio. I renamed the computer from the previous install. but for some reason the sql 2005 express management studio remembers the old machine name even when I create a new DB inside the management studio with the new computer name.when I try to change the ownership in properties-files it cant find the 'new computer nameuser'i double checked the computer name its correct. The funny thing is i installed express with the 'new computerame' anyone have a clue that may help with this what file needs to be edited?Thanks inadvance for any help. 

View 2 Replies View Related

Change Ownership

Jul 5, 2001

Is there a way in SS 7.0 to change ownership of an object from a user owned object to dbo owned object other than dropping the object and re-creating it? E.G. juser.table to dbo.table
Thank you,
GaetonC

View 2 Replies View Related

Change Ownership To Dbo

Jul 26, 2000

I have tables created by a user called "toms". He has dbo permission etc.
I want to make all these tables to be owned by sa ( dbo). How do I do that?

the owner of the database is sa

thanks,
Rachel

View 1 Replies View Related

DTS Package Ownership

Nov 2, 1999

I work in a development team of 5 people - when we create DTS packages they are owned by the individuals' user. This means that if a problem is discovered, only the original developer can change their package - which is a problem if they are busy / on holiday etc.

Is there a way to change each others packages without having System Admin priveleges (definitely not an option)

Similarly, how are people handling version control of packages - they is no way to put a package into Sourcesafe / something similar is there ?

View 1 Replies View Related

Change Of Ownership

Mar 9, 2001

How do you change the ownership of a DTS package?

View 1 Replies View Related

Change Of Ownership???

Jun 28, 2004

I am having a problem with object ownership.
The person who setup the SQL Server setup the "dbo" account with his personal permissions. I need to change "dbo" so that the login name is the system account and not a personal account. Is this possible?

If this is not possible can I change the ownership of a system object?
I was able to change ownershipof user objects using the "sp_changeobjectowner" stored procedure, but it did not seem to work for system objects.

Any way around this?

- Eric

View 2 Replies View Related

Ownership Issue

Sep 15, 2004

One of my apps uses a login that is tied down to only use the sprocs and tables it is allowed to access. Its just given 'public' role. However, it needs to be able to add and delete fields from one table in particular. Is there a way to allow this? The login it uses has been given full rights to the table but an alter table command faults. The tbale must be a 'dbo' because other apps use it -- can I be granted DDL rights on a per table basis?

View 6 Replies View Related

Package Ownership

Aug 8, 2007

Ok... Now I'am panicking.... I dont know how to resolve this issue...

Package created by me, imported to sql server by dba, its executed under a different user id (all are NT logins)

Execution Command



Code SnippetDTEXEC /SQL "pkg1" /SERVER SERVER1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E





Below is the error.



Code Snippet
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 9:01:51 AM
Could not load package "pkg1" because of error 0xC0014062.
Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E14 (Only the owner of DTS Package 'pkg1' or a member of the sysadmin role may create new versions of it.). The SQL statement that was issued has failed.
Source:
Started: 9:01:51 AM
Finished: 9:01:51 AM
Elapsed: 0.156 seconds




Please some body help me on this.
Thanks

View 8 Replies View Related

Using Ownership Chains

Dec 11, 2005

How I can disable Ownership chains.For Example Joe creates a procedure called procedure1, which depends on Table1 (also owned by Joe), and with executed procedure1 insert one record in table1,we assume Joe grants Mary permission to use procedure1,But  Mary do not permission To insert  record to Table1 (Deny To Insert in Table1),and I Expect To see "Insufficient Permissions" error, But It did't happen and record inserted in Table1.

View 1 Replies View Related

Dbo - Database Ownership

Jun 8, 2006

I have created tables within SQL Server database. My tables I have created do not have the dbo ownership(?)...how does one transfer a table to dbo?

advTHANKSance

View 7 Replies View Related

Change Ownership

May 28, 2007

Hello I'm trying to change the ownership using this command :



sp_changeobjectowner 'govern.NA_WEB_SUBS_HITS' , 'dbo'





and then I received this following error message :



Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 75
Object 'govern.NA_WEB_SUBS_HITS' does not exist or is not a valid object for this operation.



Can anyone help please??



Thanks,



Stanley

View 5 Replies View Related

Change Object Ownership

Feb 16, 2007

A co-worker recently quit the company, and all of his storedprocedures and DTS packages are apparently under his name. Is thereany way to change his account to another (ie administrator or SA)??I am afraid to delete his account in fear all the stored procedureswill be lost. (we have backups, but ... ugh)

View 1 Replies View Related

Difference B/w Permission Vs Ownership

Oct 26, 2000

hi what is the differenct b/w Permission vs ownership in a window NT property.I know that I use permission to give permission to the NT users to have certain rights to folder. what about ownership. What do I use it for and how does it server me

Ahmed

View 1 Replies View Related

Change Ownership Of Table On SQL 7.0?

Oct 29, 1999

Just curious - how does one change the ownership of a *table* (not a database) in SQL 7.0?

Thanks
RM

View 1 Replies View Related

DTS Package Ownership Issue

Apr 11, 2006

Hi
In production, I have couple of DTS packages saved under the ownership of the old dba(her network id). All the new DTS packages have my network authentication id as the ownership.

I read an article in the sqldts.com saying, this will not affect the day to day activities. Link below

http://www.sqldts.com/default.aspx?212

My questions
1) Is there anyway I can create the DTS package with an sql authentication id (sa) instead of the my windows authentication id.This will clear off the issue of person specific ownership for the dts package.
2) I am aware of the undocumented stored procedure
sp_reassign_dtspackageowner to change the owner name.Incase i am changing it for all the packages in the server, what will be the impact?
Most of the dts packages are run using the jobs and i assume there wont be any problem with that.
what about the packages directly invoked using the applicaiton?

Thanks in advance
Sreenath

View 2 Replies View Related

Ownership Chains And Permissions

Feb 20, 2007

If I, as dbo, create a stored procedure to delete a record from a table (also owned by dbo) and I grant a user (user1) permissions to execute this stored procedure does the fact that an ownership chain exists between the stored procedure and the table mean that there will be no net effect if I deny delete permissions on this table to this user?

View 2 Replies View Related

Changing Ownership Of Objects

May 6, 1999

Hi !!
I would greatly appreciate your help. I am a newbie at SQL Server..and am a
trainee DBA for my company. the problem I am facing is that till now the
developers were using any one of three (user created)logins and were creating
and manipulating objects. All the creation and modification of objects and
databases will be done by me. The problem is that the owners of the previously
created objects is one of those logins.
Is there anyway in which I can become the owner of all the objects and deny
them any rights on altering the objects ?? or do I have to drop them all and
recreate them with me as the DBO ?? Thanx

View 1 Replies View Related

Changing Table Ownership

Jan 3, 2007

Hi,

Just want to ask that if it is possible to change a table ownership?

thanks

View 9 Replies View Related

Change Ownership Of A Table

Jan 24, 2007

I am trying to change ownership of a table in my sql database to dbo. I am getting this error "Object 'UserLog' does not exist or is not a valid object for this operation." Here is my script:
use ePAC
EXEC sp_changeobjectowner 'UserLog', 'dbo' I checked and this is the correct spelling of this table.

What could I be doing wrong?

View 2 Replies View Related

Package Ownership Issue

Aug 8, 2007

Ok...I dont know how to resolve this issue...

Package created by me, imported to sql server by dba, its executed under a different user id (all are NT logins)

Execution Command

DTEXEC /SQL "pkg1" /SERVER SERVER1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E


Below is the error.

Code SnippetMicrosoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 9:01:51 AM
Could not load package "pkg1" because of error 0xC0014062.

Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80040E14 (Only the owner of DTS Package 'pkg1' or a member of the sysadmin role may create new versions of it.). The SQL statement that was issued has failed.

Source:
Started: 9:01:51 AM
Finished: 9:01:51 AM
Elapsed: 0.156 seconds

Any pointers???

Thanks
Karunakaran

View 2 Replies View Related

Ownership Issue Problems..... (I Think)

Jul 20, 2005

I created a database on my laptop, and because I was hoping to keepthe data, I tried detaching it and copying the files to my server andattaching the database to the server.I quickly noticed that some of my SPs didn't have rights that I wasexpecting them to have. One SP executes a line of dynamic SQL thatupdates a record and I get errors saying it doesn't have SELECTpermission or UPDATE permission.So... I removed the database from the server and scripted the DB.I logged in to Query Analyzer as 'sa' and ran the create scripts.The DB lists 'sa' as the owner, and everything in the database lists'dbo' as the owner, but running those SPs still gives me the sameerrors.Any clues? What can I try to further diagnose this problem?

View 2 Replies View Related

Can Table Ownership Be Changed?

Jul 20, 2005

When I create tables in SQL I can specify dbo as the owner using thesyntax below but when using the upsize wizard in Access 2000 I owneverything. Is there a way that the system administrator can changeownership after the fact?CREATE TABLE dbo.mytable (c1 int not null)Thank You,Randy KJoin Bytes!

View 1 Replies View Related

How Do I Create Dbo Ownership Of An Object

Feb 11, 2008

I have a user who has been granted db_owner rights to a database and want him to be able create new views using the dbo schema. When the user creates a view right now, the schema created is under their username. How can I change this so that when a user creates a new view, they always create it under the dbo schema.

View 3 Replies View Related

Changing The Ownership Of A Object

Jan 9, 2007

Hi all,

I have a problem while executing a stored procedure. I have created a database called "cpd" and created some stored procedures. for all my stored procedure the owner is "CPDUSER". when ever i am executing any stored procedures i have to write the user name first else it is not working.

let's say i have a stored procedure called "cp_checklogin". it takes 2 parameters. to execute this i have to write

exec cpduser.cp_checklogin 'admin@jk.com', 'admin'



but i don't want to write the user name there. and if without username "CPDUSER" i am trying to execute the stored procedure it is throwing me the error that "the stored procedure cp_checklogin is not exist in the database". can anybody suggest me. it's very urgent.



Thanks in advance

Krishna

View 4 Replies View Related

SQL 2012 :: DB Chaining And Ownership Chains

Apr 22, 2014

I am getting the following error when trying out DB chaining and Ownership chains

Msg 916, Level 14, State 1, Line 1

The server principal "user" is not able to access the database "Test" under the current security context.

I did run these scripts to setup DB Chaining

ALTER DATABASE [Test1] SET DB_CHAINING ON;
ALTER DATABASE [Test] SET DB_CHAINING ON;

I then setup user using a sysadmin role with select permission to a view. The View is on Test1 and points to Test database using a synonymn setup on Test1.

According to all the articles I have read this is all I should do and the view should then run.

View 3 Replies View Related

Only A System Administrator Can Reassign Ownership

Jan 30, 2007

Hi guys,

I am new to this forum and new to SQL.

I have a stored procedure which sets the value of a variable (@owner_logon_name, type sysname) to sa. It then calls sp_add_job in the msdb database, which in turn calls numerous other stored procedures. The ASP page I post down to the server is coming back with an internal server error and the log file shows the following message:

"Only a system administrator can reassign ownership of a job."

Even though the login name was set to sa, it seems to be using another role.

The code I have in my sp is:

exec @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID output ,
@job_name = @JobName,
@owner_login_name = sa,
@description etc, etc

Can anyone think why it would do this? An explanation of how sp_add_job works would also be much apprciated.

View 1 Replies View Related

Cross Database Ownership Chaining

Oct 5, 2006

I saw this option in SSMS under "Servers, Property, Security tab". I looked at BOL but I still don't understand what it is or what it is used. Any help appreciated.



TIA,



Barkingdog

View 3 Replies View Related

Cross Database Ownership Chaining

Mar 10, 2008

i have a database with cross database ownership chaining enabled. data base was detached and reatached as a result owner changed from sa to account that was used during reataching. will this affect chaining?

View 1 Replies View Related

Changing The Schema Ownership On Subscriber

Aug 18, 2006

Hi,

I have a replication in which publisher, distributer and subscriber all runing on sql server 2005. all the tables that needs to be replicated are under 'dbo' schema on publisher.

Subscriber is a datawarehouse so i dont want to put tables coming from a system to go under dbo schema as there might be other application replicating same name tables to warehouse. i have created a schema for my application on warehouse but dont know how to tell replication to create tables under application schema created on subscriber. I am using snapshot replication that can be reinitialized if required.

any help will be appriciated.

Cheers,

Furrukh baig

View 1 Replies View Related

Replicate Database Objects That Are Not Dbo Ownership

Aug 25, 2006

I have a database called sky and its tables, views, procs and functions owned by sky. I need to replicate the sky database to another server. I had problem because those objects have ownership sky not dbo. I can not change ownership when replicate the database. How do I replicate database objects that are not owned by dbo? Is this possible or I have to change ownership from sky to dbo before replicate the database?



Thank you very much for your input and suggestions.

View 1 Replies View Related







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