A Fine Slice Of SQL Server
Steve Jones rustles up the herd:
I hosted this month’s T-SQL Tuesday party with my invitation asking about tracking permissions. I didn’t get my own post completed in time, but I’ll add it in the next week sometimes.
In any case, here’s a roundup of the posts I saw. If I missed any, ping me and I’ll add you.
Click through for the full list of respondents.
Leave a CommentHugo Kornelis talks about pain:
But I have been in contracts where I was the only employee able to spell SQL, and hence all other database tasks also fell in my lap. Including permissions.
And yes. I have been in projects where the idea was to investigate all current permissions, check which are and which are not needed, and then correct everything that was wrong.
We never got past stage 1. Even in a moderate sized company, with moderate sized database applications, getting a full overview of who has which permission was sheer hell.
The best I’ve ever been able to do is execute as each user and then query sys.fn_my_permissions. Otherwise, you won’t know the full scope of a user’s permissions because there are group permissions that querying other DMVs as a sysadmin won’t give you. And frankly, even this isn’t a foolproof operation.
Leave a CommentChad Callihan creates a new user:
I can’t remember where I heard the analogy, but think of a SQL Server Login as the key to a hotel. While a Login will get you in the hotel, you need a room-specific key (User) to access specific rooms (or databases) in that hotel.
When it comes to creating new logins and users, dbatools can help make it a more manageable process. This is especially helpful when you’re deploying the same login and/or user to multiple servers at a time.
That’s a nice analogy, and Chad follows it up with a pair of dbatools cmdlets you may find helpful.
Leave a CommentRob Farley gives us a roundup:
I was the host for T-SQL Tuesday this month, inviting people to write about integrity. And because I don’t believe in just providing a list of posts (although I’ve also done that at the end of the post), I have a completely fabricated story for you. And no, this wasn’t created using AI. I have more integrity than that (see what I did there?).
Read on for Rob’s summary and all of the links.
Comments closedShane O’Neill riffs off of a script:
Like many shops out there, we can’t run corruption checking on our main production database instance. So, then, what do we do? We take the backups and restore them to a test instances, and then run corruption checking on those restored databases.
At least this way we can test the backups we take can be restored, as well.
But, I don’t want to spend every day manually restoring and corruption checking these databases, so let’s automate this bit…
Click through for a script that Shane built off of a Madeira Data Solutions script that Eitan Blumin put together.
Comments closedDeborah Melkin shares some thoughts:
The first way to think of data integrity is a very small and literal interpretation. This is making sure that our data in the database is good. In many ways, these are easy to enforce – you add constraints. Primary Keys ensure that you know what makes each row unique. Unique constraints represent what would make each record unique if the primary key constraint, which is often a surrogate key these days, didn’t exist or offer different options.
Read on for more about database design, default constraints, and a dive into data modeling.
Comments closedI cannot tell you how many times I’ve encountered scenarios where “this data looks wrong.” Well… can one ensure that it is being retrieved and displayed correctly from the storage media that it resides on in the first place? Are you viewing/validating the data in question correctly? Whatever client/method you are using to review your data – that is suspect and its integrity is in question.
It is technically possible for bits to flip, but that’s also why we have checksums on disk. I’m sure there are people who have experienced storage corruption that changed just enough to cause problems but not enough to be noticeable, but Andy is right on the money.
Comments closedKevin Chant threw a spanner in the works this time:
You can find out more about both T-SQL Tuesday and Festive Tech Calendar in my original invitation. Where I invited others to write about a Microsoft Data Platform announcement that they considered to be as amazing as a present. In other words, something which made you go “wow”.
Click through for a variety of responses and Kevin’s categorization of them.
Comments closedLouis Davidson looks back on the past:
I will admit that the feature I will mention probably won’t be relevant to you, as it has been nearly 30 years since this feature was introduced. But since this is the most nostalgic time of the year, I want to go back, through the mists of times, to a time where we hadn’t even quite begun to fear the changing of the centuries. To a time when we a single CPU was all we had even thought of, computer screens were often colorless, a few megabytes of RAM was all that was needed. It was a simpler time, though to be fair, it was also a time before we had sports on our phones.
Louis goes on to reminisce about an implementation of database quality guarantees in a world without foreign key constraints. Which is usually about the time I rag on MySQL for its insistence that foreign keys weren’t important to the product until relatively late in its existence (version 3.23, released in 2000).
Comments closedRob Farley cashed in all of his chips and got something nice out of it:
I had written to Santa (okay, the product group at Microsoft) to ask for this. It wasn’t quite “If I’m on the nice list, what I’d like is a unicorn train set”, but more like “If it’s not too much trouble, this thing here would be amazing…”
The thing was the ability to see the “number of rows read” by an Index Seek operation in a SQL plan containing the “Actuals” (post-execution).
Read on for information about why this is so useful.
Comments closed