A Fine Slice Of SQL Server
Vlad Drumea rebuilds a machine:
Export-DbaLogin does a great job of exporting all logins, but it doesn’t offer a way to migrate the sa password to another instance.
In this case, I didn’t have the sa password and was required to ensure that the rebuilt instance is a 1:1 copy of the original one (edition excluded).This also meant that the existing password used for sa had to be transferred to the rebuilt instance.
Read on to see how you can accomplish this.
Comments closedHugo 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.
Comments closedChad 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.
Comments closedChen Hirsh shares a tale of woe:
Have you ever made a change in your system and immediately regretted it? A few weeks ago, I did just that while working with a customer on their Databricks platform. His IT guys made some changes, moving a user to another domain. In Databrick, this is considered a new user, so I added the new user and gave him the same permissions as the old user.
And then, without thinking twice, I deleted the old user from Databricks.
Things did not go well from there. Read on to learn what happened, why, and how to avoid this problem in the future.
Comments closedKevin Chant needs a service principal to help:
In this post I want to share one way that you can authenticate as a service principal to run a Microsoft Fabric notebook from Azure DevOps.
Some of you may recall that I previously covered how to run a Microsoft Fabric notebook from Azure DevOps.
I decided to published a newer version of the aforementioned post to amplify the fact that the REST API that runs a notebook on demand now supports service principals.
Service principals are the way to go for this, so long as you’re having one Azure-based service communicate with another Azure-based service. No passwords, no API keys, nothing you need to remember or change every 90 days.
The problem is, this works beautifully for assets inside of Azure, but not so much outside of Azure. But that’s a story for a different day.
Comments closedI’m doing some sql server security privilege troubleshooting , because a customer has reported an incident – they have privileges yesterday to a certain SQL table but today those privileges no longer. I’d like to know if there is a way to identify what changes were made and by which login those changes occured. Can you supply a method ?
Read on for the answer, though Tom has an important caveat.
Comments closedMika Sutinen builds a baseline:
I’ve recently had to work a bit more with the Microsoft Defender and the vulnerability assessment in Azure. Following those efforts, it dawned to me that the topic of security baselines is sometimes slightly misunderstood. So, in this post, we’ll look into what a security baseline should cover (and what they probably shouldn’t).
But first things first. Security baselines are provided by the Microsoft Defender for Cloud service, which I always recommend enabling for Azure workloads (unless there’s a 3rd party solution for it already). If you don’t have anything of the sorts enabled for your databases and servers, I highly recommend you go and turn Defender on. Seriously. Do it now.
Read on to learn more about why having a security baseline is so important and where to draw the cut-off between security and functionality.
Comments closedYou can’t just exec
DROP ROLE your_role_name;if it’s granted perms or other roles are granted to it. I had to go fishing to find all the grants to revoke them. Note: if you are worried about re-granting later, you can always fiddle with this to output the grants for these perms as a rollback.
Read on for a few scripts to help out with finding what that role owns, revoking rights, and reassigning ownership.
Comments closedVlad Drumea troubleshoots an issue:
This is one of those things that’s obvious for anyone with a bit of SQL Server and Windows experience, but stumps a lot of newcomers.
Especially when it comes to students going through tasks like creating and restoring databases.It’s also one of those things that I have to explain on a regular basis, so might as well have detailed explanation with examples I can direct people to in the future.
The latest example being this reddit post in r/SQL, where the wrongest answer has the highest upvotes.
Read on for a story about Operating system error 5 (Access is denied). And if I hear anybody switching the virtual service account running SQL Server to Local System, I will personally tell you it’s an awful idea and to stop it.
Comments closedAndy Brownsword works around an issue:
I recently had an issue when sourcing data in Power BI from a server which was accessed by a DNS alias. Here I’ll demonstrate the issue and how to resolve it.
After entering the server details, we could be greeted with the message below:
The server name provided does not match the server name on the SQL Server SSL Certificate. Please contact your administrator or try changing your Connection encryption settings
Click through for a solution if you cannot re-issue the certificate with the relevant DNS alias.
Comments closed