Minimum Permissions for Get-DbaDbUser

Words: 806

Time to read: ~ 4 minutes

Update: 2020-07-15 – Thank you Garry Bargsley for being an unofficial editor ūüôā

Update: 2020-07-17 – Thanks to Shawn Melton for spot-checking this and letting me know ALL permissions needed!

TL;DR:
All Users:
A user on the database with ALTER ANY USER permission.

Current User and System Users:
To work against all databases for the current user and system users requires CONNECT ANY DATABASE.


Update


2020-07-17

Thanks to Shawn Melton for pointing out that CONNECT ANY DATABASE allows the user to see only themselves and the system users.

To see all users from Get-DbaDBUser, the caller will need a user on the databases and the permissions ALTER ANY USER.

CONNECT ANY USER

Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -Database __DBA -EnableException -Verbose |
    Group-Object -Property Database

4 records are returned – the user itself and the system users.

User & ALTER ANY USER

USE __DBA;
GO

CREATE USER LimitedPermissions FROM LOGIN LimitedPermissions;
GO

GRANT ALTER ANY USER TO LimitedPermissions;
GO

The LimitedPermissions login now has a user in the database and we’ve granted that user the ALTER ANY USER permission.

Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -Database __DBA -EnableException -Verbose |
    Group-Object -Property Database

Now, we can see all the users; the user itself, the system users, and the other user I created on the database.


Original Article


The Backstory

Work is in the process of automating tasks. Part of this automation includes verifying the automation that we’ve done.

Where am I going with this?

Well, when we’ve automated the creation of database users we also want to verify that we’ve created the users that we say we’ve created.

My fellow co-workers have, thankfully, seen the dbatools light and we use the command Get-DbaDbUser to get the users in a database and compare the list against the users we were supposed to create.

If there are any users that should have been created but don’t show up, well then we have a problem.

The Principle of Least Privilege

Works fine for me […] but it looks like […] can’t run it with her “public” access to the db server.

I’m not going to sugarcoat things – the person that sent me the request has more access than they rightly need. The “public” access worker did not need any of that access so I wasn’t going to just give her the same level.

Plus, we’re supposed to be a workforce that has embraced the DevOps spirit and DevOps is nothing if it doesn’t include Security in it.

So, if I could find a way to give the user enough permission to run the command and not a lot more, then the happier I would be.

But, I was surprised how difficult it was to find out what permissions were needed to run Get-DbaDbUser. Even more surprised when I failed and realised I’d have to find out myself.

If anyone else can Google/Bing it and get the answer, please let me know ūüėź

The Test

Let’s create a new user with no permissions in SQL Server.

USE [master];
GO

CREATE LOGIN LimitedPermissions WITH PASSWORD = N'MorePermissionsMoreProblems!';
GO

Now let’s test it out. I have a database in my instance called __DBA. Can we access the users in that database?

<#
    $Cred
    -----
    Username = LimitedPermissions
    Password = 'MorePermissionsMoreProblems!'
#>
Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -Database __DBA -EnableException

It doesn’t work. What’s even more surprising is that it silently doesn’t work. No warnings, no permissions errors, or nothing. And I included the -EnableException switch!

The Investigation

It’s good to know that you can check out the contents of the dbatools (and other) commands from PowerShell. No, I’m not talking about opening the .ps1 files. I’m talking about using the Function:\ psdrive.

Get-ChildItem -Path Function:\Get-DbaDbUser |
    Select-Object -ExpandProperty Definition

See those $server.databases and $db.users? For me, that means that it’s using SMO (Server Management Objects). If there was any hope of me google/binging permissions before this, well it’s gone now.

The Will is going

To cut a rather long story short, eventually I came to the idea of thinking that maybe it only needs to connect to the database. So let’s try that.

USE __DBA;
GO

CREATE USER LimitedPermissions FROM LOGIN LimitedPermissions;
GO

And now let’s try our Get-DbaDbUser command again.

Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -Database __DBA -EnableException -Verbose |
    Select-Object Database, Name, LoginType, UserType

Double-Checking

Let’s try all of the databases on the instance now

Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -EnableException -Verbose |
    Group-Object -Property Database
Oh it has the system databases as well now!

Apart, from the system databases (excluding model) it only works on __DBA.

Give it all

Now, let’s use the CONNECT ANY DATABASE server permission.

USE [master];
GO
GRANT CONNECT ANY DATABASE TO LimitedPermissions;
GO

And we’ll run against all databases again.

Get-DbaDbUser -SqlInstance localhost -SqlCredential $Cred -EnableException -Verbose |
    Group-Object -Property Database

Sin é

That’s it! Minimum permissions that I could find for Get-DbaDbUser is the permission to connect to the database.

Hope that helps!

Retrospective: Speaking at DataScotland

Words: 315

Time to read: 1.5 minute

Data Scotland

On the 13th of September 2019, I spoke at DataScotland; my first time talking at a data conference.

My quasi-clickbait title was Feel Validated with dbachecks. If you guessed that I was talking about dbachecks then you’re right.

This is a brief retrospective of that time. Thinking back on that time still makes me relive the emotions that I felt. Nervousness, excitement, and panic.

Good times!

Appreciation

This was my first time speaking at a conference as well as my first time attending Data Scotland.

I recommend that you check it out. It’s an amazing conference created by passionate people and staffed by dedicated volunteers.

The Good

What didn’t count as the good is the way to put this!

  • Amazing fellow speakers.
  • Getting to meet other first-time speakers.
  • Talking with the volunteers.
  • Speaking with attendees.
  • Seeing people who I hadn’t seen in a long time.

Thank you to Craig Porteous, Louise Paterson, Paul Broadwith, and Robert French for all your work and encouragement.

Thank you as well to Brent Miller, Andrew Pruski, David Alcock, and John McCormack for help with the presentation.

The Bad

Feeling drained.

I don’t put this down to DataScotland though.
You may not have heard from me for the last month. I felt drained and took time off from public exposure.

A full year of constant working on the day job and personal work.
2 conferences a month on average for the last year.
Spreading myself out on projects about SQL, PowerShell, Containers, Python, AWS, and Azure without rest.
It’s not something I could sustain without factoring in sharpening the axe time.

Overall

I’m easing myself back into things again with the caveat of planning ahead and making sure I don’t overwhelm myself.
First thing on my list, planning for DataScotland next year.

Whether it’s speaking, volunteering, or attending, I’ll be there.

T-SQL Tuesday #86: SQL Server Bugs & Enhancement Requests

The Good, the Bad, and the Bug-ly.

The Good, the Bad, and the Bug-ly.

This month’s T-SQL Tuesday is brought to us by Brent Ozar ( work | personal | tweets ) and has to do with SQL Server Bugs and Enhancement requests on Microsoft Connect.

Now the reason that this is the topic of this month’s T-SQL Tuesday is nicely spelled out by the host himself:

Now, more than ever, Microsoft has started to respond to Connect requests and get ’em fixed not just in upcoming versions of SQL Server, but even in cumulative updates for existing versions.

If you’ve kept an ear to the ground and an eye out on the social media, (seriously get on Twitter if you haven’t already; with #sqlhelp and #sqlfamily, you’ll never be alone, stuck, and/or bored again), you’d realise that this is true. Microsoft has engaged more than ever with it’s audiences, from hiring a PowerShell developer to actively engaging in various Trello Channels. So much so that a twitterbot was created to keep track of Connect items closed as fixed (Closed As Fixed) by MVP Chrissy LeMaire ( blog | tweets ).

Shameless Plug:

Now, I happen to have a Connect item and I’m going to link to it here. I can do this as this blog shares a commonality with Lesley Gore’s birthday party song. (It’s mine and I can cry if I want to)

The Good…

First of all, lets see what we’re running on here…

SELECT @@VERSION

 

 

version
I really should be doing these against SQL Server 2016… ūüė¶

First contender: Inserting to an indexed view can fail

What would happen if I told you that, with regards to a view, sometimes inserting into the table could fail? Well that’s what this Connect item from Dave_Ballantyne found, along with the reason.

CREATE TABLE myTable
(
    Id integer not null,
    InView char(1) not null,
    SomeData varchar(255) not null
)
GO

CREATE VIEW vwIxView
WITH SCHEMABINDING
AS
SELECT 
    ID, 
    Somedata, 
    LEFT(SomeData, CHARINDEX('x', SomeData) - 1) AS leftfromx
FROM 
    dbo.myTable
WHERE 
    InView ='Y'
GO

CREATE UNIQUE CLUSTERED INDEX pkvwIxView ON vwIxView(Id)
GO

 

 

Now that we have the groundwork laid out, it’s time for our insert attempt:

DECLARE @id integer,
@inview char(1),
@Somedata char(50)

SELECT @id = 1,
@inview = 'N',
@Somedata = 'a'

INSERT INTO myTable(Id, InView, SomeData)
SELECT @id, @inview, @Somedata

 

daveballantyneerror
Do you see a LEFT() or SUBSTRING() ANYWHERE there???

He’s even gone so far as to give an explanation as to why this happens!

This is due to the compute scalar being executed prior to the filter.

Taking a look at the estimated execution plan, we can see that he’s right!

daveballantyneerrorplan
Let’s do it first, then see if we should have!

Imagine trying to troubleshoot what was causing the inserts to fail? Horrible! I can imagine tearing my hair out at that one!

I have this done as “The Good” just for the fact that, not content to just report the bug, Dave_Ballantyne even went so far as to find the possible cause. Now it’s just up to Microsoft to get to the fixing…

…The Bad…

Next up, we have moody31415 with “Can’t create Materialized View that references same table twice

Groundwork:

CREATE TABLE test (A int)
GO
CREATE VIEW vtest WITH SCHEMABINDING
AS
SELECT t1.A, t2.A as A2
FROM dbo.test T1
JOIN dbo.test T2 ON T1.A=T2.A
GO

The problem occurs when we try to add a unique clustered index on this bad boy:

CREATE UNIQUE CLUSTERED INDEX UCL_Test
on dbo.vTest (A, A2)

 

maddog_selfjoinviewserror
Ever get those times where you just can’t stand yourself?

Now, I originally put this down as “The Bad” because I thought that the issue could be down to trying essentially index the same column twice but that’s not the case…

CREATE TABLE dbo.B (col1 tinyint, col2 tinyint)
GO
CREATE VIEW dbo.SecondOne
WITH SCHEMABINDING
AS
SELECT t1.col1, t2.col2
FROM dbo.B AS t1
JOIN dbo.B AS t2 ON t1.col1 = t2.col1
GO
CREATE UNIQUE CLUSTERED INDEX UCL_test
ON dbo.SecondOne (col1, col2)

 

maddog_selfjoinviewserror_02
…I mean After-A-Massive-Meal-Cant-Stand-Yourself

In the end, the reason that I have this in “The Bad” section is that I went to the documentation and read this part…

The SELECT statement in the view definition must not contain the following Transact-SQL elements: […] self-joins […]

Now it’s unknown whether this was there when this Connect item was created, but it’s there now (and I didn’t have enough time to re-plan this blog post)

…and The Bug-ly.

In my quest through the magical lands of Connect I stumbled across this little beauty of a bug by Anatoly V. Popov that I had to mention as “The Bug-ly”.

Altering indexed view silently removes all indexes

Yes, I know it’s the same as my one but dammit if I don’t think it’s a bug! That’s why it’s getting my “The Bug-ly” title.
This leaves me with a bit of a conundrum though…Do I close mine and comment on this one to get re-opened? Or do I try and continue to push on my one?

To be honest I don’t know which option to choose but, for anyone new to SQL Server, filing a Connect Item is an exciting but nerve-wracking time.
Exciting because you’ve worked with SQL Server enough to think you know how something should be done.
However, it’s nerve-wracking because you haven’t worked with SQL Server for long enough to know if you are just whining or have an actual item to be taken seriously.

Finding this Connect item was a small little shot of validation for me. Just a little something to say “Hey, I thought this too and I’m with you”.

Summary

It’s a great thing that Microsoft have started to respond to the Connect Items again and going through them you realise how different people are using SQL Server in ways that you didn’t even think to imagine.

So check out a few, leave your comment and up-vote/down-vote as you see fit.

Just…just don’t be this guy please

dt_ofdbas

 

 

The Danger of Disabled Logins

When disabling sysadmin logins just ain’t enough

words: 691

Reading Time: ~3 minutes

Intro:

I’m becoming more and more of a fan of Powershell the more that I interact with it. And I’m a big fan of the work that those over at dbatools are doing (seriously, check it out and also check out their Slack channel).

So when reading an article by Steve Jones (b|t) that mentions using Powershell, especially dbatools, I took immediate attention!

However, while reading the blog, what jumped out at me was the fact that dbatools copies the logins and the passwords. I think that’s epic and saves so much hassle, especially with migrations and environment creation.

But when you script out a login in SSMS, you get the following “warning comment” (and this comes straight from Microsoft) :

/* For security reasons the login is created disabled and with a random password. */

I understand why you don’t want to script out a login’s password for security reasons but disabling it…does that even do anything?

Something that I only recently learned is that, for logins with high privileges, disabling them is not enough; they must be removed.

Overkill, I hear you say?

Example, I retort!

Example:

I will admit that for my example to work, there needs to be help from a member of the securityadmin server role login. So for this example we’re going to have…

  1. A disabled sysadmin login,
  2. A “compromised” securityadmin login,
  3. An “attacking” low-privilege login.

Window 1 (High Permission Account):


-- Create a high privilege login (HPL)
CREATE LOGIN [AllThePower]
    WITH PASSWORD = '1m5trong15wear!';

ALTER SERVER ROLE sysadmin
    ADD MEMBER AllThePower;

-- Disable it.
ALTER LOGIN AllThePower DISABLE;

-- Create a "compromised" login
CREATE LOGIN Enabler
    WITH PASSWORD = 'AlreadyHereButCompromised';

-- Make them part of security so can grant permissions
ALTER SERVER ROLE securityadmin
    ADD MEMBER Enabler;

-- Create a low privilege login (LPL)
CREATE LOGIN Copycat
    WITH PASSWORD = 'NotAsStrongButDoesntMatter';

 

 

So now we have all our actors created, we need to connect to the database with all 3 accounts.

Simple as “Connect” -> “Database Engine” -> Change to SQL Auth. and put in the details above for who you want.

Window 2 (CopyCat):

First things first, check who we are and who can we see?


-- Who are we?
SELECT
    SUSER_NAME() AS LoginName,
    USER_NAME() AS UserName;

-- Who can we see?
SELECT
    *
FROM sys.server_principals;

 

copycatsee
We can’t see “Enabler” or “AllThePower”

Okay, so we can’t see it but we know that it’s there.

Let’s just cut to the chase and start “God-mode”


-- Can we get all the power
ALTER SERVER ROLE sysadmin
ADD MEMBER CopyCat;

copycatinitialsysadminfail
It was worth a shot…

Can we impersonate it?

-- Can we impersonate AllThePower
EXECUTE AS LOGIN = 'AllThePower'
SELECT
    SUSER_NAME() AS LoginName,
    USER_NAME() AS UserName;

 

copycatinitialattemptfail
I’ve put the different possibilities on individual lines…We “do not have permission” btw

Time to go to our compromised account:

Window 3 (Enabler):

Now, who are we and what can we see?

enablersee
Sauron: I SEE YOU!

Notice that “Enabler” as part of securityadmin can see the disabled “AllThePower” login?

Great, we can see it, so let’s promote our CopyCat login!

enablerinitialattemptfail
Look but don’t touch

So even though we’re now a member of the securityadmin role, we still can’t promote our login!

I think you’d be safe in thinking that people would give up here, but we know from server_principals that “AllThePower” is there, even though it’s disabled!

So even though we don’t have the ability to promote our login, we do have something that we can do in our power.

GRANT IMPERSONATE.


-- Give CopyCat Grant permission
GRANT IMPERSONATE ON LOGIN::AllThePower TO CopyCat;

enablerinitialattemptsucceed
Every little helps

Window 2 (CopyCat):

Now can we impersonate our Disabled login?

copycatinitialattemptsuccess
Whoooo are you? Who-oo? Who-oo?

And can we get all the power?

copycatsecondattemptsuccess
I know an uh-oh moment when I see it…

Finally, we’ll revert our impersonation and see if we actually are sysadmin?


-- Go back
REVERT;
SELECT
SUSER_NAME() AS LoginName,
USER_NAME() AS UserName;

-- Are we superuser?
SELECT IS_SRVROLEMEMBER('sysadmin') AS AreWeSysAdmin;

CopyCatGodMode.PNG
I..HAVE..THE POWER!!!

And now I can do whatever I feel like, anything at all!

Summary:

I’m a fan of removing high-permission accounts that are not needed but I’ve never put into words why. Now I know why disabling is just not enough and, for me, removing them is the option.

sp_rename to change schema?

words: 519

Reading time: ~2.5 minutes

The Set Up:

Recently I was asked by a developer whether they could use sp_rename to change the schema of a table.

I said no but I realised that I don’t know for sure as I’ve¬†never tried it this way.

Granted I have never needed to when we have such a descriptive command like ALTER SCHEMA.

So I tested to see if sp_rename could change the schema of a table and thought I would share my results.

Here they are:


SP_RENAME:

Script 1:


SELECT [Schema Name] = SCHEMA_NAME([schema_id]),
 [Table Name] = [name]
 FROM sys.tables
 WHERE [name] = N'Alphanumeric';

original_table

Now taking a look at the documentation for “sp_rename”, turns out all we need is

  1. the current name,
  2. the new name we want to call it, and
  3. an optional object type (which I’ll include because I like typing).

So with that, it seems simple to run the following…

Script 2:


 EXEC sp_rename
 @objname = N'dbo.Alphanumeric',
 @newname = N'deleteable.Alphanumeric',
 @objtype = 'OBJECT';

sp_rename
That error message! I enjoy that error message ūüôā

So now all there is left to do is check if it worked, so we run our first script again and we get???:

original_table_changed
eh…what?

I repeat the above:¬†eh…what???

Where did my table go???

Please tell me I didn’t delete the table? It’s a test system and I took a backup before starting but it’s a whole lot of hassle to recreate the table.

However, on a whim, I changed my first query to use a LIKE:

Script 3:

sp_rename_found
ehh..WHAT??

So I haven’t changed the schema? I’ve renamed it to be dbo.deletable.Alphanumeric?

Is that even query-able?


SELECT * FROM dbo.deleteable.Alphanumeric; -- Fails!

SELECT * FROM [dbo].[deleteable.Alphanumeric]; -- Works!

Okay, let’s just change it back quickly and pretend it never happened:

Script 4:


EXEC sp_rename
@objname = N'deletable.Alphanumeric',
@newname = N'Alphanumeric',
@objtype = 'OBJECT';

error_message_02
You’re killing me!!

Okay, okay maybe it’s like the SELECT statement and I need to wrap it in square brackets?

Script 5:


EXEC sp_rename
@objname = N'[deletable.Alphanumeric]',
@newname = N'[Alphanumeric]',
@objtype = 'OBJECT';

error_message_03
I count that as a metaphorical middle finger to me…

Maybe we’re being too specific?

Script 6:

EXEC sp_rename '[deleteable.Alphanumeric]', 'Alphanumeric';

finally_works
Hopes are up…

A quick run of our first script to confirm?

works_again
…and¬†we’re back to normal!!!

Now, as to why that syntax works but the others don’t…I have no idea.

I will try and figure that out (fodder for another blog post ūüôā ) but I’m going to need a few more coffees before I go touch that again.


ALTER SCHEMA:

It’s a bit sad though… all that hassle for something that didn’t even work in the end?

Now, lets check out the documentation of “ALTER SCHEMA”.

  1. where we are changing it to, and
  2. what we’re changing.

Seems simple, but then so did sp_rename and that burnt me.

Script 7:

alter_schema
Give my “deleteable” the “dbo.Alphanumeric” object!

A quick check to see if it actually¬†worked as I’m not swayed anymore just by a lack of warnings:

actually_works
Yes!


Sum it up:

If I didn’t know the answer at the start, I definitely do now.

Can you change the schema of an object by using “sp_rename”?

Hell no.

Save yourself the hassle and just stick to ALTER SCHEMA. It’s easier, believe me.

 

[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 !

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.

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.