Create View Permissions

CREATE VIEW Permission

I’m mainly writing this as documentation for myself as, in the end, this is the original purpose of this blog, to document SQL Server and new aspects of it that I learn and try.

Personal Template

I’ve always had a little block with regard to this as, for database permissions, I always followed a template in my head:

USE <database>
<Give/Take away> <what permission> <On What> <To Whom>

It’s The Little Things That Trip You

With CREATE permissions this isn’t the case; there is a piece of the above template that isn’t needed, and it’s quite easy to see why when I sat down and thought about it.

Specifically, it’s this bit:

<On What>

I’m granting CREATE permissions; since I haven’t created anything, I can’t grant the permission on anything.
So for CREATE permission, I have to modify my template a bit:

USE <database>
<Give/Take away> <what permission> <To Whom>

If I use this now as a template to a GRANT CREATE VIEW, it will work:

USE [localTesting];
GRANT CREATE VIEW TO [testUser];

And it works!

[SQL Server] Efficiency of Permission Granting.

Words: 349

Reading Time: ~1.5 minutes.

The lead up

Recently I was asked to create a temporary user with SELECT permissions on a database.

So far, not a problem. Taking advantage of the pre-defined roles in SQL Server, I just add this new user to the pre-defined role [db_datareader], which grants SELECT permissions to all tables and views in a database.

Why would I grant SELECT permissions this way and not manually choose the tables and views that this user could connect to?

Well,

  1. This is a test server so there is no sensitive information held that I’m worried about blocking access to,
  2. I didn’t get the exact requirements of the tables this user is to query so I don’t know which tables/views to grant access to and which to deny access to (I consider this a mistake on my part and something I have to act on next time),
  3. The test user is only required for 2 days, after which it is getting reviewed and deleted as quickly as I can, and
  4. Efficiency.

Efficiency, how?

Why grant SELECT on tables individually when I can grant on all tables in 1 fell swoop?

In the same vein, hypothetically speaking, if I was asked to grant SELECT permissions on 96 out of 100 tables, I would GRANT SELECT on all of them and then DENY SELECT on the 4 required as long as no column-level GRANTs have been given on those tables.

 Summary

A recent notion that came to me was that one of the roles of a DBA is to gather knowledge, but to a level that promotes efficiency.

Sure, we know how to grant permissions, but we should also know the pitfalls, such as “deny beats grant unless the grant is on the column level” or “there are some combinations of permissions that allow more than intended“.

Knowing these caveats allows us to say when options can be automated or where rules need to be added to check for different statuses.

This will allow us to move on to the next aspect that needs a DBA’s eye and gentle guiding touch…or 2 cups of coffee and a full throttling !

[SQL Server] How Well Do You know Your sys Tables? Test yourself

I may have been too reliant on Intellisense…

Intro:

Reading Time: ~1 minutes

Recently I had to check on the nature of my check constraints and foreign keys; whether they were trusted or not.

select name, is_not_trusted from sys.check_constraints;
select name, is_not_trusted from sys.foreign_keys;

In case you are wondering, this has some Query Optimiser (QO) benefits so it’s a definitely a bonus to ensure that they are trusted.

However, something that should have taken 5 seconds and be a no-brainer, took me 30 seconds and required a bit of memory power on my part.

All because I had a problem with my SQL Server Intellisense, and said SQL Server Intellisense stopped working.
This forced me to drudge up these names out of my memory from whatever blog post or BOL entry I read them in.

This, in turn, got me wondering; how well do you really know your sys tables?

If your intellisense broke tomorrow, would you know your Dynamic Management Objects (DMO)?

Now before you dismiss this notion as simple, remember that it is not just sys tables that you have to know. This will test your knowledge on your application tables, your columns, stored procedures and functions.

Do you know all their names? Which table has the column “ID”, which has “<table_name>ID” and which has “<table_name>_ID”?

Test yourself:

The fix for my problem with SQL Server Intellisense not working is going to be reversed to allow you to test your knowledge.

In the dialog box, Tools -> Options -> Text Editor -> General, there are two checkboxes under the “Statement completion” section:

  • Auto list members
  • Parameter information

If you uncheck these two checkboxes, your intellisense is gone!

TransactSQLGeneral

Try it out, even for 5-10 minutes.

Hopefully, with your Intellisense gone, your sense will remain.

Why I Powershell my Laptop off

Could be my shortest blog post so far…

Intro

Kalen Delaney ( blog | twitter ) has an excellent blog post about Windows Fast Startup and, while I’m not going to repeat what she has said here because, like I already mentioned, it’s an excellent post and I encourage you to read it ( and maybe give her a lil’ subscribe 😉 ), what I will mention is that I encountered this feature with my new laptop and had it interfering with my SQL Server testing (again read her post as to possible causes why).

Using Powershell for documenting Replication had me wondering if there was a way I could get around this using Powershell. So while this is another post that is not about SQL Server, it is about Powershell.

Hey, at least I’m consistent in my consistencies.

What’s the Problem?

A quick lmgtfu, brought me to the following page and command:

shutdown /s

Which pops open a window saying the computer will shutdown and, after a delay, that’s what it does.

At this stage I’ve read enough documentation to know that
shutdown /s
doesn’t follow the standard Verb-Noun convention of Powershell and that delay was slightly annoying.

Plus, everyone raves about the Get-Help commandlet so I figured I would try that.

Get-Help *shutdown*

Gave me a list of commands and one of them seemed to fit what I wanted.

Get-Help Stop-Computer;

Powershell_stopcomputer

Summary

3 things here.

  1. You now know how I turn my computer off all the time
  2. It’s amazing what you can do with Powershell, and
  3. Kalen says

    So you might already know, but I didn’t know, until I learned it, of course.

I didn’t know, but found a work-around so didn’t learn it.
I’d advise you to follow Kalen’s approach (as I’m going to try from now on) but, hey, at least you now know mine.

T-SQL Tuesday #80 – Can Powershell Get What T-SQL Cannot?

No matter who wins Powershell or T-SQL, the GUI loses!

It’s T-SQL Tuesday time! tsql2sday-150x150

Chris Yates (blog | twitter) has given the T-SQL bloggers a “carte blanche” with regard to this month’s theme so even though this T-SQL Tuesday falls on his birthday, he’s the one giving us a gift (awfully nice of him I think).

So a white blank page to work with…in this case it seems only appropriate to write about Powershell. Mainly because if I were to write about it normally, all you would be getting is a white blank page. Basically, about Powershell, I don’t know much…

Therefore to start off this blog post, a little back story about why I’m talking about Powershell is appropriate…

Documenting Replication.

If you really want to get up to scratch with something that you are working with then you can’t go wrong with documenting it. Or at least that’s what my Senior DBA told me just before he went back to his laptop laughing maniacally.

So needing a high level documentation of the publications, articles and article properties of what we replicate, I turned to the only thing I knew at the time; the GUI.

GUI.

Now, due to an unfortunate incident when I was a Software Support Engineer that involved a 3 week old backup and a production database, I prefer to not to use the GUI if I can help it.

I’m not joking about that as well, if there is ANY way that I can accomplish something with scripts instead of the GUI, I will take it!

Especially when the need was to document the properties of over 100 articles, I was particularly not looking forward to opening the article properties window for each of the articles and copying them out individually.

Replication_ArticleProperty
100 X 40 = 4000 no thanks

 

Scripts

Unfortunately, in this case, the scripts were only partially useful.

Oh they were great for the publications

EXEC sys.sp_helppublication;

and to get the articles

EXEC sys.sp_helparticle @publication = publication_name;

but the article properties themselves remain elusive!

From BOL, the only way to actually interact with them seemed to be when you were creating the articles or if you wanted to change them, yet nothing for just viewing the states of them.

Finally after a lot of Google-fu, I managed to get most of the schema options with a good few temp tables and Bitwise operators

Replication_PreCreationCommand_SchemaOptions

but nothing I could find helped me with the create commands.

Replication_PreCreationCommand

These create commands are kinda important when you think about what they do.

Replication_PreCreationCommand_Options

Drop the object, truncate all data and the delete data. The delete data option is probably most dangerous if you have a row filter set up as you may not even be aware that data has been deleted until it’s too late and users are screaming at your door!

So in a blind fit of panic and a desperate attempt to thwart my GUI foe, I turned to Powershell.

Powershell

I was thankfully able to find an elegant, well-explained script by Anthony Brown and then proceeded to butcher it without remorse until it returned what I wanted.

I’ve included the full script at the end of this post with a few…shall we say…forewarnings.

The main point that I had to add was simply this:

PseudoCode:

For whatever article on now,
get the article properties
where the source article is what we’re looking for
return only the PrecreationCommands
formatted in a list
and returned in a string:


$publicationobject.TransArticles `
| Where-Object SourceObjectName -Like $WorkOnNow `
| Select-Object PreCreationMethod `
| Format-List `
| Out-String

Finally returning what I want, which is a simple copy and paste into the relevant section of a Word document

Replication_PreCreationCommand_Final

Time taken:

  • Powershell: 100 + articles all finished in around 4 seconds 🙂
  • GUI: 100+ articles hand typed out in a time I’d not like to figure out, plus however long I spent washing my hands afterwards.

Final Word

As I’ve said before

one of the best thing about SQL Server is, that for all it’s restrictive syntax and rules, there is no 1 way to do anything.

…and there is no excuse for relying on the GUI, unless you want to!
Powershell is an amazing tool to add to your belt and one that I’m definitely going to learn more about.

I challenge you to think about an aspect of your work that is not automated or for which you use the GUI for (shudder).

Now see if there’s a way around it…

Final Powershell Script

The following is the final script used to get the code. I make no apologies for it as I don’t know Powershell yet it’s served it’s purpose and then some. It has returned my creation commands, taught me some fundamentals of the language and ignited a desire to learn it.

However I do apologise for the look of the script. There is something configured with the blog that squashes the script and requires a scroller, I’m working on fixing it.

# Load the assembly needed. (Only required once at the start).
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Rmo")
# Clear screen before each run
Clear-Host;

# Connect to the server.
$servername = "insert server here"
$repserver = New-Object "Microsoft.SqlServer.Replication.ReplicationServer"
$srv = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection" $servername
$srv.Connect()
$repserver.ConnectionContext = $srv

# Connect to the database
$databasename = "insert database here"
$repdb = $repserver.ReplicationDatabases[$databasename]

# Connect to the publication.
$publicationname = "insert publication here"
$publicationobject = $repdb.TransPublications[$publicationname]

<#
# Everything (troubleshooting)
$publicationobject.TransArticles | Where-Object SourceObjectName -EQ $article
#>

# Get everything. (from here on out, it's Butcher town 😦 )
$Schoptions = ($publicationobject.TransArticles | Select-Object SourceObjectName, SchemaOption, PreCreationMethod )
$Schoptions `
| ForEach-Object `
{ `
$NewLine = "`n"
$WorkOnNow = $_.SourceObjectName

# Get SchemaOptions details.
$Schoptions = ($publicationobject.TransArticles | Where-Object SourceObjectName -Like $WorkOnNow | Select-Object SchemaOption | Format-List | Out-string )
$schemaoptions2 = (($Schoptions -split ", ").Trim() ) -csplit "SchemaOption : "
$OptFormatted = ($schemaoptions2 | Where-Object {$_ -ne ""} | Where-Object {$_ -ne "PrimaryObject"} `
| ForEach-Object -Process `
{
Switch ($_)
{
"Identity" {"Identity columns are scripted using the IDENTITY property`t:`tTrue"}
"KeepTimestamp" {"Convert TIMESTAMP to BINARY`t:`tFalse"}
"ClusteredIndexes" {"Copy clustered index`t:`tTrue"}
"DriPrimaryKey" {"Copy primary key constraints`t:`tTrue"}
"Collation" {"Copy collation`t:`tTrue"}
"DriUniqueKeys" {"Copy unique key constraints`t:`tTrue"}
"MarkReplicatedCheckConstraintsAsNotForReplication" {"Copy check constraints`t:`tFalse"}
"MarkReplicatedForeignKeyConstraintsAsNotForReplication" {"Copy foreign key constraints`t:`tFalse"}
"Schema" {"Create schemas at Subscriber`t:`tTrue"}
"Permissions" {"Copy permissions `t : `t True"}
"CustomProcedures" {"Copy INSERT, UPDATE and DELETE stored procedures`t:`tTrue"}
default {"Extras present, please check"}
}
})

# Get PreCreationMethod details.
$CreationMethod = ($publicationobject.TransArticles | Where-Object SourceObjectName -Like $WorkOnNow | Select-Object PreCreationMethod | Format-List | Out-String)
$CreationMethod2 = (($CreationMethod -split ":").Trim() | Where-Object {$_ -ne ""} | Where-Object {$_ -ne "PreCreationMethod"} `
| ForEach-Object -Process `
{
Switch ($_)
{
"None" {"Action if name is in use `t : `t Keep existing object unchanged"}
"delete" {"Action if name is in use `t : `t Delete data. If article has a row filter, delete only data that matches the filter"}
"drop" {"Action if name is in use `t : `t Drop existing object and create a new one"}
"truncate" {"Action if name is in use `t : `t Truncate all data in the existing object"}
default {"Error! Creation Method Switch has failed"}
}
})

#Report the details.
$NewLine
$WorkOnNow
Write-Host '----------'
$OptFormatted
$CreationMethod2
$NewLine
}

 

 

Semi-Unique Constraints.

When full uniqueness is a bit too much.

What are Unique Constraints? How can we enforce them and finally, is there anyway that we can have modified uniqueness…can we have Semi Unique Constraints?

Unique constraints.

There are many use cases that require that a column in a table only have unique values. The main example of this is a table’s PRIMARY KEY which, needing to uniquely identify a record, needs to be unique by default.

In fact, SQL Server enforces this uniqueness by creating a unique index on whatever column(s) the Primary key is defined on.


-- Drop Table if Exists.
IF OBJECT_ID(N'dbo.SemiUniqueColumn', N'U') IS NOT NULL
DROP TABLE dbo.SemiUniqueColumn;

-- Create Table.
CREATE TABLE dbo.SemiUniqueColumn
(
ID int IDENTITY(1, 1),
UniqueColumn int,
SemiUniqueColumn int,
CONSTRAINT [PK_SemiUniqueColumn] PRIMARY KEY CLUSTERED (ID)
);

-- Check Primary Key Exists.
EXEC sp_helpindex N'dbo.SemiUniqueColumn';

SemiUniqueConstraints_PK
Index Description : unique.

Unique But Not Primary

Primary keys are not the only options that can be unique, SQL Server recognizes this, and so there is the option of marking other columns as unique as well. Whether this be actioned by a UNIQUE CONSTRAINT or a UNIQUE INDEX is user’s perogative.

I’ll be creating this using a UNIQUE INDEX but for the purpose of completeness, the syntax for UNIQUE CONSTRAINTs is


ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column(s))

Now say we are forced to ensure that the column UniqueColumn is unique, so we create a UNIQUE INDEX on this.

-- Ensure UniqueColumn is Unique by Creating Unique Index on it.
CREATE UNIQUE NONCLUSTERED INDEX [UIX_SemiUniqueColumn_UniqueColumn] ON dbo.SemiUniqueColumn (UniqueColumn);
SemiUniqueConstraints_UIX
Index Description : unique.

We insert values into this table and, as long as the uniqueness of these rows are satisfied, we’re going to have a good time.


-- Insert Data.
WITH RecordSet (SeqNum) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)
INSERT INTO dbo.SemiUniqueColumn (UniqueColumn) SELECT SeqNum FROM RecordSet ORDER BY SeqNum DESC;

-- Select Data.
SELECT ID, UniqueColumn, SemiUniqueColumn FROM dbo.SemiUniqueColumn;

SemiUniqueConstraints_TblSelect.png
All Unique = All Good.

When Is A NULL Not a NULL?

Short answer, when you try and compare it to another one.

Now you may think that I’ve lost the plot and gone off on a tangent but bear with me here a second and I’ll explain.

A NULL is UNKNOWN and an unknown value should not equal another unknown value. What happens if your first unknown turns out to be the number 1 and the second unknown is the number 2? 1 <> 2 so comparing them then would be pretty foolish.

If you ever look at some generated script and see at the very top of the script, hiding amongst the XACT_ABORTs and the NOCOUNTs is another option called ANSI_NULLs (although not anymore as it’s on by default and should be left that way IMHO).
This ensures that NULLs are treated as unknown and cannot be compared to another unknown except for some specific cases (GROUP BY, UNION ALL, INTERSECT, EXCEPT, etc)

What Does This Mean For Us?

Good question! Remember our Unique Index on UniqueColumn? What happens if we run the following?


-- Insert NULL into UniqueColumn.
INSERT INTO dbo.SemiUniqueColumn
( UniqueColumn )
VALUES
( NULL );

It inserts no problem, going all the way from 7442, 7441, … 2, 1, NULL. What about if we run it again?

SemiUniqueConstraints_UniqueColumn2ndNull
An Index is apparently exempt from NULL <> NULL.

Semi Unique Constraints.

Now ask yourself the question, what happens if we are told that our other column SemiUniqueColumn can have as many NULLs as it wants but if it gets a value, that value must be unique?


-- Generate Semi Unique Values.
-- Every Sixteenth Value is NULL.
UPDATE
original
SET
SemiUniqueColumn = IIF((original.UniqueColumn % 16) = 0, NULL, original.UniqueColumn)
FROM dbo.SemiUniqueColumn AS [created]
JOIN dbo.SemiUniqueColumn AS [original] ON created.ID = original.ID;

-- Select Data.
SELECT ID, UniqueColumn, SemiUniqueColumn FROM dbo.SemiUniqueColumn;

SemiUniqueConstraints_SemiUniqueColumn.png
Multiple NULLs But How to Force Uniqueness?

I used to think that this would be a complex requirement, possibly requiring a TRIGGER or two to check the inserted value against whatever is already there; but there is a way to have this functionality and have it the way that SQL Server normally would enforce a uniqueness on a column; by using a UNIQUE INDEX.

In case you’re thinking…

“Oh, a unique index doesn’t check what’s already there, is that it?”

I’m afraid that’s not the case.


-- Standard Unique Index Treats NULLs = NULLs.
CREATE UNIQUE NONCLUSTERED INDEX [UIX_SemiUniqueColumn_SemiUniqueColumn] ON dbo.SemiUniqueColumn (SemiUniqueColumn);

SemiUniqueConstraints_FailUniqueIndex.png
Won’t Create As Values Aren’t Unique

Yes, I know that we just showed that SQL Server treats NULLs as equal on Indexes but there is a way around this,  and that’s to not use a simple unique index.

We simply ignore the NULLs altogether by using a UNIQUE FILTERED INDEX.


-- Filtered Index.
CREATE UNIQUE NONCLUSTERED INDEX [UFIX_SemiUniqueColumn_SemiUniqueColumn] ON dbo.SemiUniqueColumn (SemiUniqueColumn)
WHERE SemiUniqueColumn IS NOT NULL;

Don’t trust me? Trust but verify!

First attempt to insert a duplicate value in SemiUniqueColumn:


-- Test it (Duplicate).
INSERT INTO dbo.SemiUniqueColumn (UniqueColumn, SemiUniqueColumn) VALUES (0, 7439);

SemiUniqueConstraints_FUI_Dup
7439 Is Already There So It’s Rejected.

Now we attempt to insert a duplicate value in SemiUniqueColumn but attempt to insert a duplicate NULL.


-- Test it (Not Duplicate).
INSERT INTO dbo.SemiUniqueColumn (UniqueColumn, SemiUniqueColumn) VALUES (0, NULL);

SemiUniqueConstraints_FUI_NotDup
NULL Is Already There But We Don’t Care.

Finally, NULL was chosen just for example purposes, filtering can be done on any value but, at the moment, there are a couple of got’cha’s with them…

Conclusion

I think you’ll agree that this would be so much easier to manage and troubleshoot than multiple triggers.

In fact, indexes are extremely useful aspects of databases and, once you start to realise how they work, you can start to use them for various different things than simple seeks and scans.

But the main aspect here is that complexity is not king. There are a multitude of ways to achieve something, so take a few moments to think about the problem you are trying to face and you’ll be thankful in the long run.

Jumping the Gun

Intro:

I am pretty sure that if I was a fish, I would not survive long enough to grow old as I would fall for the first piece of bait hanging from a lovely, shiny thing that I could see.

The only defence that I have is that, as I’m still a Junior DBA, I can make these mistakes as long as
a). 
they are not extremely serious (no dropping production databases for this guy!), and
b). 
I’m expected to learn from them and not repeat them!

And like most things, it started innocently enough. A simple support ticket coming in with the following error message.

Msg 229, Level 14, State 5, Line 65
The SELECT permission was denied on the object ‘Removable’, database ‘LocalTesting’, schema ‘Superflous’.

What I did:

I saw this error message and immediately thought to myself

AH! No problems, they just need SELECT permissions on that object. 2 second job.

And seeing as the ticket was nice enough to provide the login and user that was receiving the error message (we’ll say it was a user called “NewUser”), I could join that with the error message and grant permissions.

GRANT SELECT ON OBJECT::Superflous.Removable TO NewUser;

Following this was a quick test to impersonate myself as the user and see if it works;

-- Test 01.
EXECUTE AS USER = 'NewUser';
SELECT USER_NAME(), SUSER_SNAME();
SELECT * FROM dbo.GenericView;

FirstTest.PNG

As far as I was aware, I was happy it worked; the user, once notified, was happy it worked and I went on my merry way to grab some celebratory coffee.

Until on the way back I bumped into my Senior DBA and told him proudly what I had done…

What I should have done:

The following is a simplified reproduction of that conversation…

>Is that a new View?
> No…
>> Is that a new User?
> No…although it’s called New.
>> Could they SELECT from that View before?
> Yeah, as far as I know.
>> Alright, so did anything change before the call?
> eh…I didn’t check
>> Okay, from now on: Check.

It was at that stage that we started getting other tickets in from other users with the same error message. So rather than fixing the underlying problem, I had fixed a symptom for a single user.
The symptom was the User not having permission to select, but the underlying problem was that the View had changed.

At this stage I was still confused as it’s a view, what does it matter if the query creating it has changed, how could this have broken permissions?
Again, jumping the gun, I didn’t check…

ViewWithDifferentSchema
Different Schema!

Our problem view has two different schemas and when we check the ownership of the two different schemas, we get the following:

-- Who owns what?
SELECT dp.name AS Owner, s.*
FROM sys.schemas AS s
JOIN sys.database_principals AS dp ON s.principal_id = dp.principal_id
WHERE s.name in ('dbo', 'Superflous');

 

DifferentOwners

 How is this the answer?

Technically, the answer is Ownership Chains.

Originally, our Superflous.Removable table was in a different database on it’s dbo schema where the owner of the view (dbo) had permissions to select from.

Since the owner of the view (OV) had permissions on this schema and the OV gave select permissions on the view to the user (NU), the NU inherited the OV’s permissions.

  1. So SQL Server hit the view, saw it was owned by the OV and didn’t need to check permissions for our NU.
  2. The view first hit the table  dbo.Foo , saw that it was owned by OV and so didn’t need to check permissions.
  3. Now the view calls across to the other database, see’s the owner is not the OV so checks the permissions.
  4. However the OV has access permissions on this table so the NU gets these access permissions, therefore we have no problem!

Now we had recently done a change to have the information from the other database brought over to our database via Replication.

This meant a re-write of our View using the new table and schema with it’s new owner. This new schema that our NU or the OV did not have permissions for.

What this meant was the same procedure was followed by the SQL Server engine with the only difference being that, instead of going across to the other database, it went to our new schema Superflous.Removable . It saw the OV did not have access permissions, so it denied access permissions for our NU.

So basically, when NewUser went to select from our view, they hit the new schema, SQL Server realised it needed to check their permissions and, when none were found, access was denied.

All I had done by jumping the gun and fixing the symptom was made it so that when SQL Server traversed down the ownership chain for the view and came to the new schema, it checked permissions, found the SELECT permission for only this user and continued on.
This was the reason that the view worked for the user but no one else!

Overall Fix:

This MyStuff database principal should not be the owner of our Removable table, in fact the Superflous schema should not even exist, so it was a simple matter of transferring ownership to dbo.

ALTER AUTHORIZATION ON SCHEMA::Superflous TO dbo;

Now all the users, who have read access on the dbo schema, are able to use this view with no further hassles.

Problem solved! Right?

Stop Jumping the Gun!

All the above is what I did.

Trying to fix the permission error, I granted SELECT permission.
Trying to fix the ownership chain, I transferred ownership.
Mainly in trying to fix the problem, I continually jumped the gun.
Which is why I am still a Junior DBA.

What my Senior DBA did was fix the replication script so the new schema wouldn’t get created in the first place, and the table would get created in dbo.
Which is why he’s my Senior DBA.

Jumping the gun isn’t going to give you a head start. It is just going to delay you. Knowing the problems, as well as knowing the solutions, is the answer.

I’m learning the problems…I’ll have the solutions soon, and I aim to share them too.