473,226 Members | 1,529 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

How to change owner for all tables in a database.

I am trying to change owner of all tables in a database. How can I do
it?

Aug 3 '06 #1
1 17420
(al******@gmail.com) writes:
I am trying to change owner of all tables in a database. How can I do
it?
DECLARE @tbl sysname
DECLARE tblcur INSENSITIVE CURSOR FOR
SELECT name FROM sysobjects
WHERE xtype = 'U' AND uid = user_id('oldowner')
OPEN tblcur
WHILE 1 = 1
BEGIN
FETCH tblcur INTO @tbl
IF @@fetch_status <0
BREAK

EXEC sp_changeobjectonwer @tbl, 'oldowner', 'newowner'
END
DEALLOCATE tblcur

The above is untested, and you may have to look up details in Books Online.

Furthermore, I'm assuming SQL 2000. On SQL 2005, the preferred solution is
different. In fact, odds are good that on SQL 2005 you would not need to do
this at all, since schema and onwer are separated. All depending on why you
want to change the owner, that is.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 3 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Victor Spång Arthursson | last post by:
Hi! Have a problem, probably easy to solve... I want to dump a database which resides on my local server with another, and not existing, owner than the one who actually owns it locally. The...
12
by: Arno R | last post by:
Hi there, I just distributed an application in which I (try to) change db.properties depending on CurrentUser() For instance I set the property's AllowBypassKey and AllowBuiltinToolbars to False...
6
by: David Allison | last post by:
Relationship problem ~ How to tell Owner his db is crap. His db was made in 1999 - 12 tables - none normalised - none with relationships.(YES REALLY) He only knows about inputing data - 22,000...
4
by: N. Graves | last post by:
Hello; I have a table that is equipment. This table has a filed called Owner and that Owner filed is Looked up from a table called employee. and the query request the serial number of the...
1
by: Ange T | last post by:
Hi, I have a secured Access 2000 database, and have encountered a strange error that I can't find any similar postings on. I'd really appreciate your help! In my database, I have a number of...
2
by: Richard Sherratt | last post by:
I create a new user and make them a member of Admins. I then log on as that user and try to modify an existing query. I'm told that I don't have the necessary permissions. The Admins group has full...
3
by: eighthman11 | last post by:
Hi everyone. I inherited a database that had a user name ("field") in the user group not admins. This seemed to be fine for woking with the database because it was owned by user "field" and all...
5
by: papayaya | last post by:
Our programmer has left the company and the tables and views were created with his credential as owner. How do I change the ownership of all the tables and views using the stored procedure -...
3
by: coolminded | last post by:
hi, i have create one database with owner 'postgres' and i created tables with the same owner. but now i want to change the owner of the database as well as the tables. i changed the owner of...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.