The Heap™ – Consultancy ©®

Erik Darling

12:00 AM

Uh I have like 500 answers on here that prove otherwise

Hannah Vernon

2:00 AM

lol

J.D.

Wordle 712 4/6

⬛⬛⬛⬛⬛
⬛⬛⬛⬛🟩
⬛🟩⬛⬛🟩
🟩🟩🟩🟩🟩

Tricky tricky

Great Scrabble word though.

Peter Vandivier

@ErikDarling aren't you just... always having a mental breakdown?

like Chris Farley falling down a hill

Erik Darling

2:16 AM

@PeterVandivier my mental breakdown was complete in the early 90s. I’ve just been coasting since then.

Peter Vandivier

oh i see, like Chris Farley taking a nice drive

Zikato

6:37 AM

Morning
Wordle 713 4/6*

⬛⬛⬛⬛⬛
⬛🟩🟨⬛⬛
⬛🟩⬛🟩⬛
🟩🟩🟩🟩🟩

Smaller father and son playing ball

Paul White

8:11 AM

Wordle 713 5/6*

⬛⬛⬛⬛⬛
⬛🟨🟨⬛⬛
🟨🟨⬛🟨⬛
⬛⬛🟨🟩🟩
🟩🟩🟩🟩🟩

Hannah Vernon

11:00 AM

Wordle 713 4/6*

⬛⬛⬛⬛⬛
⬛🟩🟨⬛⬛
⬛🟩⬛🟩🟩
🟩🟩🟩🟩🟩

Zikato

J.D.

11:48 AM

lol

Lamak

1:11 PM

Wordle 713 4/6

⬜⬜⬜⬜⬜
⬜🟨⬜🟨⬜
🟨⬜🟨⬜⬜
🟩🟩🟩🟩🟩

ducks on the water

a fire in the sky

wrong

1:45 PM

@Zikato hahah

Sean Gallardy

Mario coins

Does anyone here use power automate?

worthwhile?

Erik Darling

i've never heard of it

i avoid things with power in the name

as a rule

man, the resource governor dmvs have really cool information in them

Lamak

@SeanGallardy I've used it....it has its use

the desktop or the cloud one?

Sean Gallardy

2:01 PM

Desktop, was thinking it might be helpful but seems overly complicated?

Erik Darling

just like everything with power in the name

Sean Gallardy

LOL yeah you're probably right

J.D.

2:25 PM

@ErikDarling PowerPoint though...

@SeanGallardy We use it a little bit here, but I haven't had any hands on with it yet, so can't really comment on its usefulness to complexity ratio.

I think one use case we had for it was to integrate a form into our database, but it was pretty limited on how to input the source data and output the results, so we just found it easier to code our own website instead lol.

Peter Vandivier

@SeanGallardy yea, but i don't feel good about it

only the cloud version though

feels like the managed version of automating the finance department with excel macros on the fileshare

Sean Gallardy

2:54 PM

@J.D. This seems to be what I'm pretty much finding

Cool, so I am having the same-ish experience

J.D.

I think the industry these days is trying to come up with low-code / no-code solutions for companies, but then they end up being very limited technologies in what they can actually accomplish, unfortunately.

Kind of like AI, but with less hype.

Hannah Vernon

just tell ChatGPT to do it for you

J.D.

heh

Sean Gallardy

@HannahVernon It'd end up killing people

Hannah Vernon

I believe that you'll find it only kills people who disagree with it

Paul White

2:58 PM

Wait for the upgrade

> Kill All Humans

Hannah Vernon

I can't wait till we get the Boston Dynamics version of ChatGPT

Paul White

3

Hannah Vernon

ChatGPT removed your message

Paul White

fixed

Sean Gallardy

@HannahVernon cbsnews.com/news/eating-disorder-helpline-chatbot-disabled

J.D.

3:13 PM

@SeanGallardy theguardian.com/us-news/2023/jun/01/…

Sean Gallardy

@J.D. HAHAHA I saw that this morning

but don't worry, taking all the jobs

Paul White

@H Did your nested dynamic SQL work?

J.D.

@SeanGallardy Quite a time to be alive.

Hannah Vernon

3:33 PM

> This robot causes harm.

J.D.

3:49 PM

Anyone know a reliable way to check if a trigger being fired was the result of a DML action from another specific trigger. Any chance the EVENTDATA() function stores anything related to the call stack?

Sean Gallardy

4:10 PM

good question

I'd upvote that

Erik Darling

same

Zikato

probably extended events module start and collect tsql callstack

mustaccio

4:25 PM

@J.D. gutenberg.org/cache/epub/29579/pg29579-images.html

Published 70 years ago

J.D.

@SeanGallardy True, I'll post it lol. Was feeling it was too wishy-washy because I don't even know what the thing I'm looking for would be called.

@mustaccio oOo

Paul White

feedback.azure.com/d365community/idea/…

J.D.

"Oct 16 2006 - ...This would definitly be a useful feature and we are considering implementing it in the next version of SQL Server"

👀

Paul White

Yeah

Hannah Vernon

the plebs are angsty again

3

Josh Darnell

4:32 PM

> June 5, 2023

Paul White

Time is a problem

Hannah Vernon

spacetime is a bigger problem

J.D.

Question created

Paul White

@J.D. Sounds like a very fragile arrangement. I suppose one could set and reset a SESSION_CONTEXT() entry in the calling trigger.

Josh Darnell

Are the present DBA.SE mods going to participate in the strike?

Paul White

4:39 PM

Haven't had time to really think about it

It only really matters what the majority of SO mods decide to do anyway

I don't feel especially strongly about the issue at the moment.
It's not like this isn't par for the course behaviour from the company in recent years.

Can you make changes to the first trigger? e.g. to use SESSION_CONTEXT/CONTEXT_INFO? — Martin Smith 5 mins ago

LOL

People and their comments. Just answer the damned question why not.

Hannah Vernon

@JoshDarnell I for one welcome our new AI overlords.

Paul White

3666

Q: Temporary policy: ChatGPT is banned

Moderator Note: This question being featured is still the best tool we have to announce this policy sitewide. However, people have been using this for protracted debate and discussion. As such, this question is now locked. If you want to discuss this policy further, or suggest other related chan...

That's a lot of meta votes

mustaccio

Wait, what? ChatGPT posts are banned, and so are moderators from removing them if they are posted? How does that work exactly?

Paul White

@mustaccio Things change over time

Hence the word "temporary"

I linked it for the vote count

mustaccio

yeah but that policy post is not closed, which implies the policy is still in effect, no?

Paul White

4:54 PM

That's part of the problem

mustaccio

Perhaps ChatGPT took over the SE headquarters

Paul White

> Specifically, moderators are no longer allowed to remove AI-generated answers on the basis of being AI-generated, outside of exceedingly narrow circumstances. This results in effectively permitting nearly all AI-generated answers to be freely posted, regardless of established community consensus on such content.

The SO meta post is local policy

mustaccio

Now they can generate any policy in no time

- Can I post a ChatGPT question?

- No.

- Can I remove a ChatGPT question?

- Can you shut up?

- No

- No.

Paul White

-222

A: What is the network policy regarding AI Generated content?

We recently performed a set of analyses on the current approach to AI-generated content moderation. The conclusions of these analyses strongly indicate to us that AI-generated content is not being properly identified across the network, and that the potential for false-positives is very high. Thr...

89

Q: Please follow your own policies on making new policies

Your policy on making policies is crystal-clear: Stack Exchange, Inc. agrees that it will: [...] Post previews for review of all new official policies in the Moderators Teams instance with the policy tag, marked with links to their public version once published, and maintain a listing of all off...

discussion moderator-tools moderation legal policy

Zikato

Those responsible for sacking the people who have just been sacked, have been sacked.

Hannah Vernon

5:12 PM

one can only hope

somewhat interesting timeline of events, even if it is a bit out of date.

J.D.

@PaulWhite Interesting, I'm not familiar with this function, so will look into. Thanks!

Josh Darnell

@PaulWhite Thanks for sharing your thoughts 😁 That's for sure, this is basically standard procedure at this point.

@HannahVernon Neat!

J.D.

5:39 PM

So SESSION_CONTEXT() is akin to session storage in a website?...it's essentially just a key / value store?

Josh Darnell

I remember Joe doing something in this area, related to getting "call stacks" like this.

Paul White

@J.D. Yes

Josh Darnell

github.com/idsdavidgutierrez/fit-sql-callstacks

Paul White

Before SESSION_CONTEXT, we used an application lock, or even the existence of a named temporary table to check the caller

Or TRIGGER_NESTLEVEL

That's a pretty obscure function, but probably the most direct way. Handles dynamic SQL too.

J.D.

6:00 PM

Neato, you learn something new everyday.

What would be a legitimate, not terrible, use case for SESSION_CONTEXT()? Just curious.

@PaulWhite I contemplated this, but I care about a specific trigger as the source, and there could be other parent triggers in the call stack I want to ignore.

Paul White

@J.D. You can specify the trigger to look for in a parameter

@J.D. I believe it was added to make Row Level Security easier to implement

But people use it for all sorts of things

3

A: Is there a reliable way to check if a trigger being fired was the result of a DML action from another *specific* trigger?

You can use TRIGGER_NESTLEVEL with an objectid parameter IF TRIGGER_NESTLEVEL(OBJECT_ID(N'dbo.OtherTrigger', 'TR')) > 0 BEGIN -- do stuff END; The EVENTDATA() function is only relevant for DDL triggers anyway.

You probably don't want over use SESSION_CONTEXT where a better solution is available. There were problems with using it in parallel plans. "The total size of the session context is limited to 1 MB. If you set a value that causes this limit to be exceeded, the statement fails."

J.D.

@PaulWhite oOo even more interesting to me then. So it's a newerish feature.

Curious how that would come into play for RLS.

@PaulWhite Sweet, did not realize this. Thanks for the answer! I'll look into it further.

Paul White

@J.D. docs.microsoft.com/en-us/sql/relational-databases/security/…

> This example shows how a middle-tier application can implement connection filtering, where application users (or tenants) share the same SQL Server user (the application). The application sets the current application user ID in SESSION_CONTEXT (Transact-SQL) after connecting to the database, and then security policies transparently filter rows that shouldn't be visible to this ID, and also block the user from inserting rows for the wrong user ID. No other app changes are necessary.

J.D.

Huh interesting. 🤔 Seems like a workaround to implementing proper authentication from the application.

Though I suppose I could see there being edge use cases where different users have access to execute an application process that is ran from an elevated account, but still needs some sort of additional RLS applied for each user who runs said process.

Paul White

6:19 PM

It's apparently very common to connect as a common user

But anyway, session context is just a much more powerful CONTEXT_INFO

People can use it how they like

Persisting the value of a variable across batches, for example

idk there must be thousands of things

Josh Darnell

@J.D. I'm curious what you mean by "proper authentication from the application" here.

Paul White

Presumably, using separate SQL Server logins per user?

J.D.

@JoshDarnell Maybe not best terminology, but yea IMO, most applications should connect to the database as the actual executing user, unless it's a particular service.

Josh Darnell

@PaulWhite I thought that might be it.

@J.D. In my experience (which is mostly with web apps), that's neither common nor recommended.

J.D.

6:38 PM

@JoshDarnell Interesting...why would it not be recommended? Do you normally use a dedicated account in the database connection of a web app?

Paul White

6:53 PM

One issue is the user could use that login to access the server outside the application

J.D.

@PaulWhite You're saying by using a dedicated account, ay?...because my view is one shouldn't have to hard-code the account to the connection string. I prefer using a trusted connection that passes off the calling user's Windows identity, and connects to the database as the actual calling user.

Paul White

@J.D. I think the discussion is more around multi-tier apps e.g. the web apps @Josh mentioned

J.D.

Otherwise, if you mean the user themselves can connect to the server outside of the application, yes I agree - but that's a good thing, I believe. Typically a user has the same rights to the data regardless of where they're establishing a connection from, whether it be DesktopApp1, WebApp2, or SSRSReport3, etc. I believe proper security at the database level should control that for the user, regardless of where they make the connection - not the application itself that should be the gatekeeper.

Paul White

Updating and deleting data outside the business logic encapsulated in the application? Doesn't sound gr8, m8

J.D.

Depends on where you encapsulate your business logic.

But fair.

Paul White

7:09 PM

10

A: Purpose of SQL Server Application Roles

Application roles do not eliminate the need to supply initial connection credentials. One must first successfully authenticate to the server/database using a login (SQL or Windows account/group), or contained database user) before an application role can be activated. Application roles mostly d...

Josh Darnell

@J.D. I'm not talking about putting credentials in a connection string. The web app connects to the database using integrated security, but the web app does not practice "impersonation" of the end user. So the web app connects to the database as whatever user runs the web app process.

In the case of apps running in IIS, this is whatever the application pool user is - whether that's a service account, or a computer, managed service account, etc.

Impersonating the end user only really works if the end user's identity corresponds to a Windows identity, which isn't always, or even usually, the case.

J.D.

@JoshDarnell Gotcha. And what's your typical process to secure actions or data access per the calling user?

@JoshDarnell Most of my experiences the users always have had a Windows identity. But most of my experiences have been for internal applications for an organization that is primarily on a Windows domain.

Josh Darnell

@J.D. That's done in the application.

J.D.

@JoshDarnell How do you know who the user is?...the application asks for user credentials?

Josh Darnell

@J.D. Ah, okay. I'm often working in external facing and multi-tenant cloud environments. So users could be on various domains, etc, compared to where the actual web app and DB sit.

J.D.

7:22 PM

@JoshDarnell Yup, that makes more sense. I worked for a SAAS company when I worked at the FinTech company, so the primary apps they created were external, probably similar idea.

Josh Darnell

@J.D. There are lot of ways application authenticate users. One way I've used quite a bit is deferring that to a third party (e.g., OAuth or SAML "single sign on" workflows).

J.D.

Gotcha gotcha.

So if you had a use for Row-Level Security, based on the calling user, would you use something like SESSION_CONTEXT() or how would you shake that out?

Josh Darnell

So a user might enter their email address (jdarnell@companyname.com), and based on their email address they would get sent to their Azure AD endpoint to sign in with their domain credentials. Azure AD then posts data to our system confirming the user is who they say they are.

@J.D. Yeah, we would have to use SESSION_CONTEXT or something along those lines.

J.D.

@JoshDarnell Yup, makes sense. We were trying to implement OAuth at one point, but never got there.

@JoshDarnell Word. Just smells janky to me, to use for RLS, but I get it.

Josh Darnell

@J.D. Yeah, that doesn't sound fun. Especially with EF.

J.D.

7:25 PM

Hah yea, not even thinking about EF at that point.

My mindset around security is probably dated. When I first did web, it was ASP.NET Forms, and impersonation was natively supported with setting the right properties in web.config. Nowadays, it does seem rather difficult to initially setup impersonation in .NET Core, especially across an API layer. But I don't do much application development anymore, I'm mostly in the database layer these days, so not my problem lol.

Hannah Vernon

it's 2:30pm on a Friday who's into doing a code push?

J.D.

Does updating STAGING count? Because that's what I'm currently up to.

Hannah Vernon

I'm only doing UAT, but it sounds important, amiright

J.D.

Our STAGING = Our UAT too, so I'm with ya. :)

Paul White

7:56 PM

I just wrote a trigger that modifies another trigger on the same table

Hannah Vernon