How do I fix database permissions in Azure SQL Server as the owner and only user?-CodePudding

I'm using cloud-based Azure SQL Server and SQL Database. To group tables within the db, I "successfully" created a new schema. However, all attempts to use the schema resulted in errors about not having permission, and I couldn't find a way to add the permission (see below). The trouble wasn't worth the troubleshooting, so I abandoned the idea and went back to my single existing schema name.

Since then, I can no longer create tables on my original schema, named qbo:

CREATE TABLE qbo.NewTable (
    pkey int PRIMARY KEY,
);

Started executing query at Line 1
Msg 2760, Level 16, State 1, Line 1
The specified schema name "qbo" either does not exist or you do not have permission to use it.
Total execution time: 00:00:00.034

I can create a table without a schema specified at all (which I assume is going to dbo), or with dbo specified, but then I can't query said table because the SELECT permission was denied.

CREATE TABLE [dbo].[NewTable] (
    pkey int PRIMARY KEY,
);

Started executing query at Line 1
Commands completed successfully.
Total execution time: 00:00:00.194

SELECT * FROM [dbo].[NewTable]

Started executing query at Line 1
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'NewTable', database 'master', schema 'dbo'.
Total execution time: 00:00:00.036

When I try granting permissions:

GRANT CONTROL ON SCHEMA :: dbo TO <myUserName>

Started executing query at Line 1
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Total execution time: 00:00:00.035

I'm the only db user, and the server admin with the Owner role. I haven't found a way through the Portal to change anything, and any T-SQL statements in Azure Data Studio to attempt correcting everything result in the same type of error above. I'm assuming something in creating another schema changed some backend setting. But from what I can tell, I haven't specifically revoked any of the permissions I started with since creating the database, which has been running smoothly for about 3 months with CREATE, UPDATE, DROP, etc.

At a minimum, I need to go back to fully operating on my own db and qbo schema, but extra credit for helping me understand what I messed up and if I can actually use multiple schemas. Thanks!

UPDATE: Based on your helpful comments and re-reading my own error messages, it finally clicked that I'm connected to different databases. I assumed at first that the db I created was the master db, so the comment didn't make sense. I don't know what the "master" db is yet (default?), but changing the Change Connection drop-down to my main db has cleared up most everything. Not sure how I changed it in the first place, but now I know to look there.

I'm still working on understanding the schemas, but at least I can get work done again.

CodePudding user response：

Based on your helpful comments and re-reading my own error messages, it finally clicked that I'm connected to different databases. I assumed at first that the db I created was the master db, so the comment didn't make sense.

I have been working in my specifically created db up to this point without realizing there was a master db, and somehow in Azure Data Studio I was now connecting to the master. This is probably unrelated to my understanding of the schema creation; I just assumed they were related issues since they happened at the same time.

To fix, I changed the Change Connection drop-down to my main db. After that, I can create, update, drop, etc. again.