Jon Shaulis - Data Professional - Page 2 of 7 - SELECT Knowledge FROM Internet WHERE Topic = 'Data';

T-SQL Tuesday #119 Changing my mind

Thank you Alex Yates for this month’s topic!

Something I’ve changed my mind on recently is working on contracting projects instead of performing work directly for a company. Very recently I had a (good) problem of choosing between a few different job roles and companies. The choice was not easy and two years ago I told myself I’d never do contract work again. I’m not talking about starting an S-Corp or an LLC but working for a company that contracts your work out to another company. I have also in the past changed my mind about how I update rows in critical systems and whether you can ROLLBACK a TRUNCATE statement (in SQL Server).

What is DAMA?

DAMA is the Data Management Association International which is an association of data professionals. They have curated a certification program, framework, and documentation when it comes to database. There are many frameworks out there that you can use when dealing with data architecture:

DAMA
DCAM
CMMI
IBM
Stanford
Gartner

I’m not going to go deep into the others or their differences right now, but here’s a link from datacrossroads that talks about them all briefly. So, what exactly is the framework? Well, it is essentially a high-level guide to help obtain or work towards Data Governance while enhancing your company’s data maturity.

Upcoming blog content

A lot of the content on my website is a mix of technical and conceptual. Over the next few months to probably the end of the year, I’m going to focus on more conceptual articles. The majority of them will cover my experience and time spent learning and working on certifications.

My goal is to achieve my CDMP (Certified Data Management Professionals) Associate certification and my MCSE (Microsoft Certified Solutions Expert) in Data Management and Analytics by the end of the year. These goals are relatively intensive in coordination with my new(ish) job that I accepted in June. Due to the intensity, I’ve pushed the work back on some technical articles and presentations I’ve been working on. So in order to keep up with frequent postings, I’ll be creating those two series of articles related to obtaining certifications.

The end result should be a series of studying articles and a final wrap up post describing my journey achieving the certs. I do have multiple technical projects planned in the pipeline to write about and distribute afterward. Creating a resume dashboard in PowerBI with ETL packages digesting data from LinkedIn is one. The other project is to create a business glossary generator with PowerShell.

T-SQL Tuesday #118 My fantasy SQL features

Thank you Kevin for hosting this month’s T-SQL Tuesday! It’s an interesting topic to reflect on features we wish we saw in SQL Server. I’m excited to read some of the other ideas and maybe we’ll see one or two get pushed out in the future!

I have three features I’d love to see from SQL Server. Two of these mostly exist already, but they are not easy to utilize. I wish so badly that SQL Server could generate smart Entity Relation Diagrams (ERD) and documentation. Really, I wish it could create the artifacts the way Redgate does. If I had a third feature to wish for while knowing it would be unrealistic, but I’d love if they had a feature to change the culture of a workplace.

Develop your career – Brand yourself!

This post should really be titled “Obtaining interviews – Building your portfolio”, but I honestly think the current title is better. (Both are still true!) Now, a lot of people are going to tell me that they don’t need to make themselves a brand to land a great job. I’m going to say that you’re right, but I’m also going to say that it’s easier when you do.

Whiteboarding in interviews

A long time ago, I was asked to perform a programming function (FizzBuzz) on a whiteboard during an interview with T-SQL. I was able to get the theory behind it, but I am pretty poor at memorizing syntax. I’d rather spend my time studying concepts, theory, and technology. Indexes and cursors are two great examples. I don’t think it really matters if you don’t memorize how to write those by hand, it’s more valuable to understand how they work instead.

Verify access to many SQL Server instances

There may come a day where you need to verify you have access to connect to a SQL Server instance. Perhaps you need to verify this access for a hundred or maybe even a thousand servers. Doing this by hand is not great and you’re going to want to capture what you did and did not have access to. This PowerShell script will help you accomplish that.

Get notified when a user with administrative privileges logs into your SQL Server

I was helping someone set up some monitoring in their database and they were asking about being notified when someone with administrative privileges logs into SQL Server. While many of you know that I try to use the right tool for the right job, some may cringe when I say triggers can help out in this scenario.

Continue reading “Get notified when a user with administrative privileges logs into your SQL Server”

Permission was denied on the object ‘master_properties’, database ‘SSISDB’, schema ‘internal’.

I ran into an interesting issue where all DDL and DML statements were being denied on my SSISDB, but specifically on my Internal schema. Here’s a few example error messages I saw:

Description: The SELECT permission was denied on the object ‘master_properties’, database ‘SSISDB’, schema ‘internal’. The UPDATE permission was denied on the object ‘operations’, database ‘SSISDB’, schema ‘internal’. The EXECUTE permission was denied on the object ‘insert_operation’, database ‘SSISDB’, schema ‘internal’.
The SELECT permission was denied on the object ‘current_user_readable_projects’

I’m not sure when or how this issue came to be, but I do know how I solved it!

Resolution:
Make sure dbo owns schema Catalog and also owns schema Internal.

Both schemas were owned by another user who was not ‘dbo’.

What else did I try? Just about everything.

I changed from local system account running the SQL Agent to a known domain sys admin user. I don’t recall the exact error, but it gave an error stating the user needed to be a member of the sysadmin group or I needed a proxy account.

When I added the user to Sysadmin or created a proxy account, the original errors returned.

I double checked the deny permissions, those were not set.

At one point I started to receive this error:

Failed to execute IS server package because of error 0x80131904.
Description: The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘SSISDB’. You should correct this situation by resetting the owner of database ‘SSISDB’ using the ALTER AUTHORIZATION statement.

I set Master and SSISDB to have the same owner here and still saw the same error afterward.

Here’s the URL that helped me the most to resolve the problem:

https://www.sqlservercentral.com/forums/topic/ssisdb-select-permission-was-denied-on-object-projects-database-ssisdb-schema-catalog

I have added in a form for feedback, it would be very helpful if you would take a few minutes to fill this out. My goal is to adjust the content to best help others resolve their problems.

Alert when SQL Server jobs fail

I would always recommend an enterprise monitoring solution for your important SQL Servers. There may be some that are less important though. You can always configure alerts at an individual job level, you can create triggers or extended events to notify you, or even use base alerts in SQL Server. If you need a quick and dirty solution to throw into place temporarily though, I like this option.

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this: