Как изменить владельца базы данных ms sql

When I accidentally click on the Database Diagrams tab, I get one of the following errors: Database diagram support objects cannot be installed because this database does not have a valid ow...

When I accidentally click on the Database Diagrams tab, I get one of the following errors:

Database diagram support objects
cannot be installed because this
database does not have a valid owner.
To continue, first use the Files page
of the Database Properties dialog box
or the ALTER AUTHORIZATION statement
to set the database owner to a valid
login, then add the database diagram
support objects.

— or —

The database does not have one or more
of the support objects required to use
database diagramming. Do you wish to
create them?

What’s the syntax for changing the owner of this database to ‘sa’?

asked Apr 20, 2009 at 18:13

Even Mien's user avatar

Even MienEven Mien

43.3k43 gold badges115 silver badges119 bronze badges

2

To change database owner:

ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa

As of SQL Server 2014 you can still use sp_changedbowner as well, even though Microsoft promised to remove it in the «future» version after SQL Server 2012. They removed it from SQL Server 2014 BOL though.

answered Jul 9, 2014 at 3:53

Alex Aza's user avatar

Alex AzaAlex Aza

75.5k26 gold badges153 silver badges132 bronze badges

3

to change the object owner try the following

EXEC sp_changedbowner 'sa'

that however is not your problem, to see diagrams the Da Vinci Tools objects have to be created (you will see tables and procs that start with dt_) after that

answered Apr 20, 2009 at 18:17

SQLMenace's user avatar

SQLMenaceSQLMenace

131k25 gold badges202 silver badges224 bronze badges

3

This is a prompt to create a bunch of object, such as sp_help_diagram (?), that do not exist.

This should have nothing to do with the owner of the db.

answered Apr 20, 2009 at 18:17

gbn's user avatar

gbngbn

417k81 gold badges581 silver badges670 bronze badges

2

Here is a way to change the owner on ALL DBS (excluding System)

EXEC sp_msforeachdb'
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
 exec sp_changedbowner ''sa''
END
'

thor's user avatar

thor

21.1k29 gold badges85 silver badges168 bronze badges

answered Apr 28, 2016 at 18:03

sawsomething's user avatar

1

Источник

When I accidentally click on the Database Diagrams tab, I get one of the following errors:

Database diagram support objects
cannot be installed because this
database does not have a valid owner.
To continue, first use the Files page
of the Database Properties dialog box
or the ALTER AUTHORIZATION statement
to set the database owner to a valid
login, then add the database diagram
support objects.

— or —

The database does not have one or more
of the support objects required to use
database diagramming. Do you wish to
create them?

What’s the syntax for changing the owner of this database to ‘sa’?

asked Apr 20, 2009 at 18:13

Even Mien's user avatar

Even MienEven Mien

43.3k43 gold badges115 silver badges119 bronze badges

2

To change database owner:

ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa

As of SQL Server 2014 you can still use sp_changedbowner as well, even though Microsoft promised to remove it in the «future» version after SQL Server 2012. They removed it from SQL Server 2014 BOL though.

answered Jul 9, 2014 at 3:53

Alex Aza's user avatar

Alex AzaAlex Aza

75.5k26 gold badges153 silver badges132 bronze badges

3

to change the object owner try the following

EXEC sp_changedbowner 'sa'

that however is not your problem, to see diagrams the Da Vinci Tools objects have to be created (you will see tables and procs that start with dt_) after that

answered Apr 20, 2009 at 18:17

SQLMenace's user avatar

SQLMenaceSQLMenace

131k25 gold badges202 silver badges224 bronze badges

3

This is a prompt to create a bunch of object, such as sp_help_diagram (?), that do not exist.

This should have nothing to do with the owner of the db.

answered Apr 20, 2009 at 18:17

gbn's user avatar

gbngbn

417k81 gold badges581 silver badges670 bronze badges

2

Here is a way to change the owner on ALL DBS (excluding System)

EXEC sp_msforeachdb'
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
 exec sp_changedbowner ''sa''
END
'

thor's user avatar

thor

21.1k29 gold badges85 silver badges168 bronze badges

answered Apr 28, 2016 at 18:03

sawsomething's user avatar

1

Источник

In this article, we will explore different ways in SQL Server to change the database owner. We can use any of the following methods in SQL Server to change the database owner.

  1. T-SQL sp_changedbowner statement
  2. Database property settings in SQL Server Management Studio

To understand the concept more clearly, we are going to prepare a demo setup and perform the following activities.

  1. Create a database named EltechDB
  2. Change the database owner using SQL Server Management Studio
  3. Change the database owner using a T-SQL statement

Additionally, I will show a script used in SQL Server to change the database owner of several user databases.

First, let us create a database. To create a database, run the following query

use [master]

go

create database [EltechDB]

When we create a new database, the user who has created a database becomes the owner of the database. In our case, the owner of the database is Nisarg-PCNisarg because we have connected to the SQL Server using Nisarg-PCNisarg login.

We can view the database owner from SQL Server Management Studio. To do that, right-click on EltechDB and click on
Properties.

View database property

On the General screen of the Database Properties dialog box, you can see the Owner of the database
in the Owner row. See the following image to see the exact location.

Database Owner

Alternatively, you can see the owner of the EltechDB database by executing the following query.

use [master]

go

select db.name as [Database Name],sp.name [SQL Login Name] from sys.databases db left join sys.server_principals sp on db.owner_sid=sp.sid

where db.name=‘EltechDB’

Query to view the database owner

As you can see in the above query output, the database owner of the EltechDB is Nisarg-PCNisarg
login.

Change the database owner using SQL Server Management Studio (SSMS)

First, let us understand the process of changing the database owner using SQL Server Management Studio. To do that,
open database property as explained above. In the Database Properties dialog box, click on Files.
Click on the […] icon, which is next to the Owner text box.

View list of users

A dialog box Select Database Owner opens. In the Select Database Owner dialog box, you can specify
the user name in Enter the object names to select the dialog box. If you are not sure
about which SQL Login to choose, you can click on Browse.

Locate the existing users

Another dialog box Browse for Object Opens. You can choose the desired user from the list. In our case, we are
selecting sa user, so select sa from the list and click on
OK.

select sa account

Back to the Select database owner screen, the sa username is added to the list. Click OK to save the changes and
close the dialog box.

sa account has been chosen

On the Database Properties screen, you can see that the owner of the EltechDB has been changed.

Method1: SQL Server change database owner using ssms

Click OK to save the changes and close the dialog box. Now, let us understand another approach in SQL Server to
change the database owner.

Change the database owner using sp_changedbowner

We can use the system stored procedure named sp_changedbowner to change the database
owner. The syntax of the sp_changedbowner is following:

Exec sp_changedbowner [user_name]

In the syntax, the value of the user_name parameter is the SQL login that you want to use as the database owner. In
our case, we are changing the database owner from sa to Nisarg-PCNisarg. To do that, execute the
following query.

use [EltechDB]

go

exec sp_changedbowner ‘NISARG-PCNisarg’

go

To populate the database name and its owner, you must design a query that uses INNER JOIN to join sys.database and sys.server_principals DMVs and retrieve the database name and user name.

use [master]

go

select db.name as [Database Name],sp.name [SQL Login Name] from sys.databases db left join sys.server_principals sp on db.owner_sid=sp.sid

where db.name=‘EltechDB’

Current database owner

Recently, I was working with a client, and I observed that the owner of several databases is the domain accounts. As a SQL Server best practice, the owner of any user database must be sa. Therefore, we decided to change the database owner to sa login.

The client has 50 user databases, so changing the database owner using SQL Server management studio was not
feasible. I would have used the sp_changedbowner stored procedure. However, in that
approach, I must manually copy and paste the database name in the stored procedure, so I had created a script that
iterates through all user databases and creates a dynamic T-SQL command that is used to change the database owner.
Below is the query.

use master

go

select ‘use [‘ + db.name+‘]; exec sp_changedbowner [sa];’ from

sys.databases db left join sys.server_principals sp

on db.owner_sid=sp.sid

where sp.name <>‘sa’

The T-SQL script generated by the query is following:

use [AdventureWorks2017]; exec sp_changedbowner [sa];

use [WideWorldImporters]; exec sp_changedbowner [sa];

use [StudentDB]; exec sp_changedbowner [sa];

use [VSDatabase]; exec sp_changedbowner [sa];

use [DBATools]; exec sp_changedbowner [sa];

use [SQLAuthDB]; exec sp_changedbowner [sa];

use [DemoDatabase]; exec sp_changedbowner [sa];

use [EltechDB]; exec sp_changedbowner [sa];

Execute the above query in our SQL Server instance and see how it changes the database owner to
sa login. Once the command executes successfully, run the below query to verify that the
database owners have been changed.

use [master]

go

select db.name as [Database Name],sp.name [SQL Login Name] from sys.databases db left join sys.server_principals sp on db.owner_sid=sp.sid

where database_id>5

login has been changed for all databases

As you can see, the database owner has been changed to sa.

Summary

In this article, we explored different methods in SQL Server to change the database owner. I have explained two approaches. In the first approach, we learned how we could change the database owner using the SQL Server Management Studio. In the second approach, we learned how to use the system stored procedure sp_changedbowner in SQL Server to change the database owner. I have also shown a T-SQL script that generates a dynamic query that changes the database owner to sa login.

  • Author
  • Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Nisarg Upadhyay

Источник

Когда я случайно нажимаю на вкладку «Диаграммы базы данных», я получаю одну из следующих ошибок:

Объекты поддержки схемы базы данных не могут быть установлены, поскольку у этой базы данных нет действительного владельца. Чтобы продолжить, сначала используйте страницу «Файлы» в диалоговом окне «Свойства базы данных» или инструкцию ALTER AUTHORIZATION, чтобы установить для владельца базы данных действительный логин, а затем добавьте объекты поддержки схемы базы данных.

— или —

База данных не имеет одного или нескольких объектов поддержки, необходимых для использования схемы базы данных. Вы хотите их создать?

Каков синтаксис для изменения владельца этой базы данных на «sa»?

4 ответы

чтобы сменить владельца объекта, попробуйте следующее

EXEC sp_changedbowner 'sa'

это, однако, не ваша проблема, чтобы увидеть диаграммы, должны быть созданы объекты Da Vinci Tools (вы увидите таблицы и процессы, которые начинаются с dt_) после этого

ответ дан 20 апр.

Чтобы сменить владельца базы данных:

ALTER AUTHORIZATION ON DATABASE::YourDatabaseName TO sa

Начиная с SQL Server 2014 вы все еще можете использовать sp_changedbowner также, хотя Microsoft обещала удалить его в «будущей» версии после SQL Server 2012. Но они удалили его из SQL Server 2014 BOL.

ответ дан 28 мая ’15, 05:05

Это приглашение для создания группы объектов, таких как sp_help_diagram (?), Которые не существуют.

Это не должно иметь ничего общего с владельцем db.

ответ дан 20 апр.

Вот способ изменить владельца на ВСЕХ DBS (кроме системы)

EXEC sp_msforeachdb'
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
 exec sp_changedbowner ''sa''
END
'

ответ дан 28 апр.

Не тот ответ, который вы ищете? Просмотрите другие вопросы с метками

sql-server
sql-server-2005
permissions

or задайте свой вопрос.

Источник

Понравилась статья? Поделить с друзьями:
  • Как изменить владельца автомобиля через госуслуги
  • Как изменить владельца starline s96
  • Как изменить владельца excel
  • Как изменить вкусы персонажа симс 3
  • Как изменить вкусовые рецепторы