Problems Creating XML Schema Collection

Ever created an XML Schema collection before? Our developers work with a lot of XML so I wasn’t surprised when eventually a request came in about permissions with XML SCHEMA COLLECTION.

Surprised that they had a permissions issue, yes, but not surprised that they were working with XML.

Why is it “an XML” and not “a XML”?

For information purposes, I’d normally provide a brief description of what an XML SCHEMA COLLECTION is but, being completely honest, I’m still not sure I can vocalize it in an understandable way. It’s kind of like explaining the colour purple without using other colours (and yes, that’s colour with a ‘u’).

I know what it is, I just can’t explain it properly…yet

So what I’m going to do is point you to the link for Microsoft docs for XML Schema Collection (done) and just gloss right over it (nothing to see here).

Permissions Shane, you mentioned permissions.

Right, sorry.

Investigation first. This was on the Development server and they had emailed me the creation code along with the error message they had received, which was this guy:

CREATE XML SCHEMA COLLECTION name AS
N'RANDOM XML ALERT...'

Msg 2797, Level 16, State 2, Line 20
The default schema does not exist.

However, when I ran the code, I got a different error message, mainly this guy:

CREATE XML SCHEMA COLLECTION name AS
N'RANDOM XML ALERT...'

Msg 9459, Level 16, State 1, Line 3
XML parsing: line 2, character 34, undeclared prefix

Which meant I had to go back and tell them to fix their darn XML.

Now I’m pretty sure we have a problems though:

That error message did not fill me with confidence. Yeah, sure they had bad XML but I was now fairly sure that there was also a permissions problem. Mainly because if there’s one thing that I’ve learned so far, it’s this:

No good can come from two different people getting two different errors from the same code!

Proper XML:

Proper XML was provided and ran by the developers but the same error message came back…

CREATE XML SCHEMA COLLECTION name AS
N'random xml alert...'

Msg 2797, Level 16, State 2, Line 20
The default schema does not exist.

The difference this time was, when I ran the code, I received the following message

Command(s) completed successfully.

…That’s not good.

Developers happy. DBAs not.

At this stage, I’m nearly convinced that it’s a permissions issue.

Checking the permissions required to create an XML Schema Collection doesn’t help, since the Devs were part of the db_dlladmin database role, so that should have been covered.

In my head I’m thinking of all the things that I can do to try and troubleshoot this problem.

  1. Extended Events my session,
  2. Ask my Senior DBA,
  3. Cry

Then I realize that I’m jumping the gun again and I slow down, and check the first error message again. This time without the developers shouting in my ear, about permissions.

The DEFAULT schema

That says “schema”, not “permission”. Maybe the difference between the DBAs and the Devs was to do with default schema and not permissions this time. Let’s check it out!

SELECT
    IIF(principal_id = 1, 'DBA', 'Dev') AS DBPrincipal,
    default_schema_name
FROM sys.database_principals
WHERE principal_id IN (1, 14);
DiffSchema
Devs don’t even have a default schema!

Wait, so it was a SCHEMA issue?

Have you checked the Examples section of Microsoft Docs? Normally, they are a great source of material for examples but if you check out the examples for XML Schema Collection , not one of them shows the schema name in the examples.

So, I walk over to the original developer and his machine, change his code to…

 
CREATE XML SCHEMA COLLECTION dbo.name_test AS 
N'RANDOM XML ALERT...' 

And it works!

Apparently what had happened was the Senior Dev had gotten sick of developers not specifying the schema when creating objects and had asked the Senior DBA to remove the default schema for Developers. That seems to work (by that I mean, everything error-ed out correctly), they were happy that developers now had to specify the schema, and life moved on.

Yet, later on, when the developer read the docs for XML Schema Collection, and saw that there was no schema in the examples, it didn’t cross their mind that a schema was required. So they didn’t specify it and that, in combination with no default schema, caused this whole mess.

The (fast food) takeaways:

  1. Slow down! Don’t jump the gun,
  2. Developers don’t know everything,
  3. It’s not always permissions,
  4. Schemas are important(!),
  5. Having checklists for investigations are highly useful, and
  6. Documentation, especially on past decisions, are even more useful!

Apologies for the blurb of a blog post but I have to go.
Apparently, there’s a permissions issue with a Stored Procedure now…

 

 

Keeping New Lines in SQL Server.

Where I compare scripts to BBQ because of course I would 😐

I have this personal opinion that one sign of a good DBA is their ability to automate things and, before the DBA world found PowerShell, the way to do this was with T-SQL.

For example, a T-SQL script to get permissions assigned to a database principal could also include a column to REVOKE those permissions. This could be “automated” with some dynamic SQL.

SELECT dprin.name AS DatabasePrincipalName,
       OBJECT_NAME(dperm.major_id) AS ObjectName,
       dperm.permission_name AS PermissionName,
       N'REVOKE '
         + dperm.permission_name
         + N' ON OBJECT::'
         + OBJECT_NAME(dperm.major_id)
         + N' FROM '
         + dprin.name COLLATE Latin1_General_CI_AS AS RevokeMe
FROM sys.database_permissions AS dperm
INNER JOIN sys.database_principals AS dprin
  ON dperm.grantee_principal_id = dprin.principal_id
WHERE dprin.name = 'public';
RevokePermissions
This can be improved A WHOLE LOT…

What about if we want to improve this?

This is nice but what about if we are paranoid forward-thinking enough to realize that this could cause us problems?

“How?” You ask. Well what happens if there existed another database, say [NeedsAllPermissions], with the same table name and the same login has permissions on it.

Are you going to revoke permissions from that database? It needs ALL of them! It says so in the name!

So in an effort to not shoot ourselves in the foot, we add in the database name to our revoke script.

SELECT dprin.name AS DatabasePrincipalName,
       OBJECT_NAME(dperm.major_id) AS ObjectName,
       dperm.permission_name AS PermissionName,
       N'USE '
         + DB_NAME()
         + 'GO'
         + N'REVOKE '
         + dperm.permission_name
         + N' ON OBJECT::'
         + OBJECT_NAME(dperm.major_id)
         + N' FROM '
         + dprin.name COLLATE Latin1_General_CI_AS AS RevokeMe
FROM sys.database_permissions AS dperm
INNER JOIN sys.database_principals AS dprin
  ON dperm.grantee_principal_id = dprin.principal_id
WHERE dprin.name = 'public';

WithDBNoChar10

Yes, we’re only using our database now!

So all is well with the world…

Until the day comes when you actually want to revoke permissions to that user. So you run the above code, copy the RevokeMe column and paste it into the management window. and you get…

NoNewLine
No GO my friend…

GO is a special little guy. It’s not exactly T-SQL. It’s a way of telling the SQL Server Management Studio (SSMS) to send everything before it, from the beginning of the script or the preceding GO, to the SQL Server instance.

If you read the documents, the main point to take away is…

A Transact-SQL statement cannot occupy the same line as a GO command. However, the line can contain comments.

GO is a special little snowflake and needs to be on his own line then. Simple enough if you know that SQL Server converts CHAR(10) into a New Line.

If you didn’t know that, well you know that now….P.S. CHAR(13) is a carriage return 😉

So let’s update our script with some CHAR(10) and see what happens then.

SQL & BBQ, both work well with CHAR

SELECT dprin.name AS DatabasePrincipalName,
       OBJECT_NAME(dperm.major_id) AS ObjectName,
       dperm.permission_name AS PermissionName,
       N'USE '
         + DB_NAME()
         + CHAR(10)
         + 'GO'
         + CHAR(10)
         + N'REVOKE '
         + dperm.permission_name
         + N' ON OBJECT::'
         + OBJECT_NAME(dperm.major_id)
         + N' FROM '
         + dprin.name COLLATE Latin1_General_CI_AS AS RevokeMe
FROM sys.database_permissions AS dperm
INNER JOIN sys.database_principals AS dprin
  ON dperm.grantee_principal_id = dprin.principal_id
WHERE dprin.name = 'public';

 

WithDBAndChar10
That smokey, wood-fire CHAR

Now, when we paste the RevokeMe column to a new window, we get…

StillNoNewLine
Oh look, it’s a wild, rare nothing…I love them!

…absolutely no difference. 🙂

Why am I smiling?

Here, around 500 words in, we get to the meat of our post. How do we keep new lines when copying in SQL Server?

Tools | Options | Query Results | Results to Grid | Retain CR/LF on copy or save

Two things need to be done here.

  1. This checkbox needs to be enabled.

    KeepNewLines
    CHECK!
  2. A new window needs to be opened and used.

New window open, we run our script again, and this time, when we copy and paste the results, we get…

FinallyWorks
Winner, Winner, BBQ Chicken Dinner

Dessert:

So if you are using T-SQL to create scripts, and you’re having this problem with GO or just new lines in general, make sure that the “retain CR/LF on copy and save” checkbox is ticked.

Now, improve that script more, throw it in a stored procedure, and you never know, it may be semi-useful. 🙂

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 !

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.