For now I don't understand why this access rights segregation exist. Is it implemented for some security reasons? Or can it be needed by some use cases I can't imagine right now? Knowing almost nothing about the reasons behind concrete PostgreSQL design I wonder why prevent a database owner from altering those tables of the database owned that are owned by other owners. Here's what [the official documentation on altering tables](https://www.postgresql.org/docs/10/static/sql-altertable.html) says regarding who can alter tables of a database: > You must own the table to use ALTER TABLE. Of course I don't argue the design or the documentation. I'm just wondering why it can be needed to limit a database owner that way?

I came across a strange situation that gave me a big headache and after solving it I would also like to understand WHY. Basically, a user had all the privileges he needed to execute a "trigger" (it was actually a foreign key constraint with an associated ON DELETE CASCADE) and all the privileges on all the objects that were touched by the trigger itself, BUT when he executed the trigger he actually used the object's owner privileges and not his own. The owner had fewer privileges than the user had and so it gave a really mysterious error. I understand it is complicated to understand what I mean, so I have created a working example that I hope clarifies. The schema I created is a spotify-type toy example, where there are users and artists, and users have lists of artists they follow. Perform all of the following operations with a superuser (just for simplicity's sake)

`SQL
-- ALL DATA
create schema website;

create table public.artists (
	id_artist serial primary key,
	name text not null unique
);

insert into public.artists (name)
values ('Heilung'), ('Rammstain'), ('Iron Maiden');

create table website.users (
	id_user serial primary key,
	email text not null unique
);

insert into website.users (email)
values ('paul90@gmail.com'), ('johndoe9876@gmail.com');

create table website.users_list (
	id_user int not null,
	id_artist int not null,
	CONSTRAINT user_fk FOREIGN KEY (id_user) REFERENCES website.users (id_user),
	CONSTRAINT artist_fk FOREIGN KEY (id_artist) REFERENCES public.artists (id_artist) ON DELETE CASCADE);

insert into website.users_list (id_user,id_artist)
values (1,1), (1,2), (1,3), (2,1), (2,3);

-- USERS AND PRIVILEGES

create user jenny with password '123';

create user tommy with password '123';

-- tommy is owner but has zero privileges
alter table website.users_list owner to tommy;

-- jenny is not owner but has all privileges
grant usage, create on schema website to jenny;

grant all on all tables in schema website,public to jenny;

grant usage on all sequences in schema website to jenny;

` Then run these queries using the user Jenny, one by one.

`SQL
--1)
select *
from website.users_list ul;

--2)
select *
from artists a;

--3)
delete from artists 
where id_artist = 3;

` The first and second query work normally, because Jenny can do SELECT on both website.users_list and public.artists. She obviously has usage privilege on the schema website. But the third one she cannot do. It gives this seemingly meaningless error.

`
ERROR: permission denied for schema website

` When she does a DELETE on public.artists she actually triggers the foreign key constraint of website.users_list so all rows related to the artist with id_artist = 3 must be dropped from website.users_list. BUT in that moment she is using the privileges of tommy (owner of website.users_list) who as we saw above does NOT have the privilege of USAGE on schema website, giving the strange error. Can someone explain to me why this happens? What does the table owner have to do with running a trigger where the user had all the necessary privileges? I find this so confusing and frankly a nightmare to debug (I speak unfortunately from personal experience).

My current user is pronto mediapop_staging=> select current_user; current_user -------------- pronto (1 row) This user a standard super user created on AWS RDS. mediapop_staging=> \du pronto List of roles Role name | Attributes | Member of -----------+-------------------------------+----------------- pronto | Create role, Create DB +| {rds_superuser} | Password valid until infinity | But I'm getting this: mediapop_staging=> REASSIGN OWNED BY pronto TO mediapop_staging; ERROR: permission denied to reassign objects Why? How can I resolve it?

I've enabled Change Data Capture (CDC) on a database using exec sys.sp_cdc_enable_db This creates a new *cdc* schema which is owned by the *cdc* user

select
	s.*
    , dp.name
from sys.schemas as s
join sys.database_principals as dp
    on dp.principal_id = s.principal_id

Because I want to leverage ownership chaining and read from this schema using a proc, can I change the owner to *dbo*?

ALTER AUTHORIZATION ON SCHEMA::cdc TO dbo

This command works and changes the owner. But the CDC has been enabled for a while, and I'm worried about breaking something further down the road.

I would like a less privileged user (KINGDOM\joker) to be able to create, manage, and drop databases on an MSSQL 2017 server [14.0.2027.2 (X64)]. KINGDOM\joker should only be able to affect the databases that they create, and should not be able to drop, restore, or take ownership of other databases. I granted KINGDOM\joker the CREATE DATABASE and MSSQL specific CREATE ANY DATABASE permissions. Using SQL Server Management Studio (v18, v19), KINGDOM\joker can create a new database [testDB] but the dbo in [testDB] is 'sa' and KINGDOM\joker cannot take ownership, despite KINGDOM\joker being the recorded owner in the master table.

USE [testDB]
GO
SELECT name,sid,SUSER_SNAME(sid) AS login FROM sys.database_principals WHERE name = 'dbo';

| name | sid | login | |:---- |:--- |:----- | | dbo | 0x01 | sa |

USE [master]
GO
SELECT SUSER_SNAME(owner_sid) AS login FROM sys.databases WHERE name = 'testDB';

| login | | - | | KINGDOM\joker |

ALTER AUTHORIZATION ON DATABASE::testDB to "KINGDOM\joker";

Fails with permission denied. As I understand, [testDB] is create from [model] and the dbo in [model] is 'sa'. I expected the dbo in [testDB] to be changed to KINGDOM\joker by the server when it creates [testDB] from [model]. It seems to have once worked that way but MS changed the behavior with SQL Server 2016, and the MS community post that explained this change is now an invalid link. Is there some MSSQL Server option or setting, or some new MS-specific permission that will allow the owner_sid in sys.databases to ALTER or IMPERSONATE the dbo in [testDB]? OR Any other work-around or method to accomplish the objective described in the first paragraph? BTW, I have considered adding KINGDOM\joker as a user in [model] and assigning the db_owner role, but that would affect every new database.

The Stack Overflow Q & A Modify OWNER on all tables simultaneously in PostgreSQL describes some nifty ways to change table and other objects to a specific user, and it works swimmingly, however all the suggestions seem to ignore the functions I created. Is there a fairly easy way to reset the owner of ALL objects in the database, including the functions? Doing it by hand is highly undesirable.

How can I find all sequences (and more widely - all objects in given schema), which are owned not by certain user, on PostgreSQL (10-15)? I found such query:

SELECT relname, relacl 
FROM pg_class pgc
WHERE relkind = 'S'
AND relacl is not null
AND relnamespace IN (
    SELECT oid
    FROM pg_namespace
    WHERE nspname NOT LIKE 'pg_%'
    AND nspname != 'information_schema'
)
and relname = 'my_table_id_seq';

But any further attempts to work with a relacl column fails with errors, f.e.:

array_to_string(array(relacl))

gives an error: ERROR: syntax error at or near "relacl" Also I've found a explodeacl function, which returns rowset of aclitem[], but I cannot get how to use it. Thanks in advance!

I had cause to change my computer name (it’s running in a virtual machine). I have an existing SQLExpress instance which was installed prior to the rename. There are some features which fail because new databases are still created with the old owner. More specifically, when I try to create a new database diagram, I get the below error. >Could not obtain information about Windows NT group/user 'OLDNAME\user', error code 0x534. (Microsoft SQL Server, Error: 15404). I used the following instructions to rename the server:

sp_dropserver 'OLDNAME\SQLEXPRESS';
GO
sp_addserver 'NEWNAME\SQLEXPRESS','local';
GO
--	Restart Server
SELECT @@SERVERNAME;	--	NEWNAME\SQLEXPRESS

However, this did not affect the default owner for new databases. They are still being created with the owner as OLDNAME\user, who no longer exists. I know how to change the owner of an existing database, after creation. But, how can I get new databases to have the correct owner to begin with?

Created these tables and sequences under postgres user: table creation schema SET search_path TO main; CREATE TABLE table1 ... CREATE TABLE table2 ... CREATE TABLE table3 ... sequence creation schema CREATE SEQUENCE main.seq1... CREATE SEQUENCE main.seq2... CREATE SEQUENCE main.seq3... Now want to change all of them to an another owner named user1. How to do without recreate these objects as they are been using?

I want to change the database owner of an Azure SQL Database. I can do this via the server administration user. However I want to do it using a different user. Why? Because I have a process which copies a database from one server to another using a specific user account that is not the server administration user, and I need this user to be able to set the db owner after the copy completes. Here's the deal. I've an Azure SQL Database **database1** owned by **user1**. I want to change the database owner to **user2**. Both users are SQL authentication logins. Running the below query from the master database, I can see the user who owns database1 is user1 with SID 0x01060000000000640000000000000000F67524E3236132449A7483D4456F051E.

SELECT d.name, d.owner_sid, sl.name
FROM sys.databases AS d
JOIN sys.sql_logins AS sl
ON d.owner_sid = sl.sid;

I login to database1 with user1. Running the below query, I can see my SID is 0x01060000000000640000000000000000F67524E3236132449A7483D4456F051E

SELECT USER_SID()

Logged in to database1 as user1, I run the query below, from the context of database1 (target database), to update the database owner, and I get the error "User does not have permission to perform this action.".

ALTER AUTHORIZATION ON DATABASE::database1 TO [user2];

According to the [Microsoft documentation](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-authorization-transact-sql?view=sql-server-ver15#alter-authorization-for-databases) , the conditions for changing an Azure SQL Database owner are: * The new owner principal can be a SQL Server authentication login. * The person executing the ALTER AUTHORIZATION statement must be connected to the target database. * The person changing the owner of a database can be the current owner of the database. So I am unsure what I've missed, but I would like to know why I am unable to change the database owner.

I am working with a vendor who is developing a SQL Server database outside of our domain. Occasionally, they need to deploy a new copy of that database into our domain by backing up their database, and restoring over the top of the of the database within our domain. The vendor uses a domain login on our side to access this database. This domain login is also the owner of this local copy. This vendor is trusted enough that we have granted the login the "dbcreator" server role. A combination of database ownership and the dbcreator role allows this domain login to restore a copy of the database over the top of the existing database. The restore completes, but as soon as it's done, the domain login can no longer access the database because the database ownership is lost. (I believe because it had a different owner on the vendor's system). After the restore is complete, I have to re-create the user within the database that matches up to the domain login, and make that user the owner again. The domain login can then access the database again. Is there any way I can configure this so the vendor is able to perform the restores when they need to without losing the existing ownership? This way I would not have to be involved every time they deploy a new version of the database. I cannot give them sysadmin because there are other databases on the server.

If I create a new schema and set the schema owner to another schema, for example dbo, what is the meaning of that in regards to my new schema? Does my new schema inherit the same permissions from it's owner dbo?

In the course of reading up on SQL Server security best practices, I learned that databases should generally be owned by a dedicated, low-privileged login (sometimes a separate one for each DB or each application), per these articles . I've also learned that you can't change the owner of master, model, and tempdb, but you can change the owner of msdb. My question is: should you? The main concern with high-privileged database owners is that the TRUSTWORTHY setting could be enabled for that DB, and msdb *has* to have TRUSTWORTHY enabled. It seems like that's a good argument for changing the owner of msdb to a low-privileged account, but I don't know if that could cause problems with instance functionality, and I haven't been able to find any articles or whitepapers even discussing the topic. The closest I've seen is this one , which stops short of making any recommendations. Can anyone provide any insight as to the possible effects of changing the owner of msdb?

I have been asked by a client to implement some views for reporting purposes, the views will be accessed via PowerBI, Excel and SSRS. The designated user will only have access to the view and no underlying tables must be available to the user. The problem I have encountered in that the SQL within the view touches 3 different schemas (All within the same database): - Pupil - Provider - Security The view is: CREATE VIEW dbo.vTestPermissions AS SELECT a.Column1, b.Column1, c.Column1 FROM Pupil.Table1 a JOIN Provider.Table2 b ON a.Column1 = b.Column1 JOIN Security.Table3 c ON a.Column1 = c.Column1 The owners of the Tables/View are as follows: - Pupil.Table1 - Owner Pupil - Provider.Table2 - Owner Provider - Security.Table3 - Owner Security - vTestPermissions - Owner dbo When I select from the view I get an error: > The SELECT permission was denied on the object 'table3', database > 'TEST', schema 'Security' I have tried giving SELECT permission with and without the GRANT option to the schema, and the tables, but that makes the underlying tables available to the user. Any help on this will be greatly appreciated.