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 CommentJoey D’Antoni doesn’t trust user logins:
Backing up databases to the cloud is not a new thing. Microsoft introduced the BACKUP TO URL functionality to SQL Server 2012 SP1 CU2. I’m not going to tell you how long ago. Still, it wasn’t last month, and Microsoft recently celebrated the 15th anniversary of Azure so that you can get an idea. When the feature started—it was minimal; you could only backup a database of up to a single terabyte and couldn’t stripe over multiple files. Additionally, you had to use the access key to the storage account, which gave complete control over the storage account—that wasn’t a good thing.
Read on for a quick overview of the feature and guidance on how it all works.
Leave a CommentVlad 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.
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 CommentChen 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.
Leave a CommentKevin 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.
Leave a CommentI’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 closed