Category: SQL Server (page 1 of 2)

Banner image showing an icon representing a transfer
Created with icon from UniconLabs on FlatIcon.com
January 21, 2025 / 0 Comments

How to easily transfer logins between servers

During server upgrades and migration projects, you will likely run into the task or issue where you need to transfer the server logins from the previous to the new server. For non-SQL authenticated users, this is simple. But if your source server happens to have more than a handful of SQL Authentication users or you don’t have access to the passwords of those users, it would normally be very difficult to transfer those logins to the new server.

I recently setup three new test environments based on a current production environment, and there were literally dozens of SQL Authentication users on the production server that needed to be copied to the new environments. I didn’t have access to the passwords of any of those users so I couldn’t just recreate them manually. That is where two non-standard Microsoft stored procedures come in very handy to help migrate all these users.

What’s in this post

Some Context

If you haven’t ever been challenged with this task before, copying SQL Authentication users from one server to another, you may not know why this can be a challenge, so I’ll describe that first. The problem with copying logins between servers is that unless you know the password of the account, just scripting out the login as a CREATE script will not give you the script that will recreate the login exactly how it is on the original server. For good reason, when you right-click on a login and opt to script it as a CREATE statement, SQL Server will generate the script but the password it puts in the script is a randomly generated string that isn’t what the true password is. So if you took that script and ran it on your new server, the login would be created, just with a different password than the login originally had, which could cause a lot of problems for whatever or whoever is using that login. (Side note: I don’t know if I’ve see this before, but when I scripted out my test login on my personal computer to demonstrate for this post, SSMS automatically added a comment saying the password is random, which is a helpful note there.)

Screenshot showing the script that is generated if you right-click on a login and opt to "Script as CREATE", with a random password being generated for the script

The Helpful Stored Procedures

Given the above context of why transferring these logins isn’t as simple as scripting them on the source server and then running those scripts on the destination, how then are we supposed to accurately copy those logins to a new server? While there are some StackOverflow answers online that essentially provide a homegrown method of achieving the same goal, they all seemed overly complicated to me so I didn’t want to attempt them. And thankfully I found the Microsoft approved method online that very easily does all the required work for you.

To create the stored procedures that accurately script out all your SQL Authentication logins without generating any security issues or wrong password problems, go to this Microsoft document page. On that page, you’ll want to scroll down a bit until you see “Method 2” in the bulleted list, which is where you find the scripts to create the helpful stored procedures.

Screenshot of the section of Microsoft documentation where it mentions the two stored procedures that can generate scripts to recreate SQL Server logins

Note: For your own safety, please review the code before you run the script to ensure you know exactly what it’s doing and that it won’t cause issues for your specific system.

Copy the scripts from the web page into your SSMS new query window and then execute on the master database of the server you wish to copy logins from.

The script as it exists on the Microsoft website starts with the USE master command, but this will not work if you are trying to run the script on an Azure SQL Database, which no longer allows the USE statement to switch database contexts. If you are running the query on such a database, you will need to remove that top line and make sure yourself that you are running the command on the master database.

Scripting the Logins

Once you have executed the Microsoft script, there will now be two new stored procedures on your master database: dbo.sp_hexadecimal and dbo.sp_help_revlogin. The first one is used by the second, and you will only be executing the second procedure manually.

When you are ready to generate scripts to recreate all the logins on your server, you can execute the stored procedure dbo.sp_help_revlogin on the master database. Doing that will return data in the “Messages” tab of the results window with all necessary CREATE statements, including encrypted/hashed passwords, for your logins.

EXEC dbo.sp_help_revlogin
Screenshot of the basic output of the stored procedure which generates the create scripts for SQL Server logins

Reviewing the output further, I can see that the SQL Authentication users I have on my server have a “password” value displayed, which I can assure you is not the actual password I set for my users. Instead, the password value you see in the script is a securely hashed version of the password stored in the depths of the server security settings. The script has included the HASHED keyword at the end of the password which will notify the new server we are going to run this on that the password should be handled as the hashed value it is so the server doesn’t literally set the login password to the provided value.

Screenshot showing the output of the stored procedure that generates scripts with hashed passwords to recreate SQL Authentication logins

Creating the New Logins

Copy the output presented in the “Messages” tab of your query window into a new query window with a connection to the server you want to add the logins to. Now to create all your logins from the original server, all you need to do is to execute the script. It’s as simple as that! You have now copied all your logins, including any login-level permissions they were assigned on the original server, added to your new server.

Summary

Copying SQL Authentication logins from one server to another doesn’t have to be a difficult or time-consuming process involving looking up old saved passwords or writing your own script to maybe fully copy the logins. Instead of trying to write a homegrown solution yourself, save yourself the time and effort and use the two stored procedures that Microsoft already wrote and shared to do the work for you.

Banner image of an icon representing a pivot table
January 14, 2025 / 0 Comments

I Finally Learned How to Pivot

This may seem crazy to some of you, but so far in my career I have been able to successfully avoid learning how to use the PIVOT feature of queries in SQL. Every example I ever came upon in code someone else wrote, I was able to get the gist of what was happening in the code without truly understanding what the query was doing. Until this week when I decided enough was enough and that writing a PIVOT query was going to be the easiest way to get the data I needed to check my work on project I am developing. This post won’t go into too much detail about writing complicated PIVOT queries, because the one I wrote wasn’t that complicated, all things considered. I mostly want to celebrate and share that I finally learned how to do something that I thought was going to be more difficult than it was.

What’s in this post

My use case

The reason why I decided I needed to finally learn how to write a PIVOT query is that I am working on duplicating and automating a manual system a business user does every quarter to project sales data using a very complicated Excel spreadsheet. As I’m working on turning this spreadsheet into an automated system with SQL and Python (since we have the same data in a database just waiting to be used), I am constantly checking my queries and results against the source of truth that is the Excel spreadsheet.

This morning, I was tired of essentially having to PIVOT my data manually or in my head to check my work, so set about learning the PIVOT query structure.

The data in the spreadsheet looked something like this:

StateCounty20202021202220232024
IDAda10001283147619234657
IDCanyon5455657687
IDBoise5455657687
IDValley5455657687

But when I was querying the data from the database, the results of my SQL queries were looking more like this:

StateCountyYearSales
IDAda20201000
IDAda20211283
IDAda20221476
IDAda20231923
IDAda20244657

If it was actually that few rows of data, it wouldn’t be a problem, but I was needing to compare the data coming from my queries against what was in a spreadsheet that had over 3000 lines (there are so many more counties in the US than I ever would have guessed…). And I wanted to do the comparison using Excel so that I didn’t have to manually compare the spreadsheet data against my own, row-by-row. So in comes the pivot query to make my data look more like the data in the spreadsheet.

Writing the PIVOT Query

Learning how to write the query was more challenging than I expected because it seems like in the top results and examples online, even in the Microsoft docs, the queries are written in a confusing and non-intuitive way so I couldn’t figure out how to manipulate them to fit my own needs. The exception to this was this wonderful YouTube video by BeardedDev, which finally helped me figure out how to write my first pivot query that was actually what I needed it to be.

His video was so much more helpful than the written tutorials and docs that I looked through because he started out with the outline of what the query should contain, showed the general query he was starting with that he wanted to convert, then walked through step by step how to switch the original query into a PIVOTed query. I highly recommend watching that video. I don’t even want to try to explain it in my own words because he did it so well.

In the end, this ended up being the query I wrote which is exactly what I needed to make the formatting of my queries match what was already in the spreadsheet from the business user.

SELECT 
[State], [County], [2020], [2021], [2022], [2023], [2024]
FROM
(
	select [State], [County], YEAR, Sales
	from dbo.HistoricSales
) AS Src
PIVOT
(
	SUM(Sales) 
	FOR [YEAR] IN ([2020], [2021], [2022], [2023], [2024])
) AS Pvt
order by [State], [County]

Summary

Writing a PIVOT query in SQL isn’t super difficult if you are given the right examples and instructions. I highly recommend checking out BeardedDev’s video that I linked above for learning how to write a PIVOT query the easy way. That video is what finally helped me understand that type of query after working with SQL for over 6 years and not being able to understand it.

Banner image showing an icon representing a firewall
January 7, 2025 / 0 Comments

The Fastest way to Add Azure Firewall Rules

To keep resources secure in the Azure cloud environment, there are usually multiple levels of security that must be cleared for someone to be able to access a resource. For Azure SQL Databases, for example, the user who is trying to access the database must have access granted for their user account on the database but they also need to be given access for their IP address through the network firewall rules for the server or database resource.

I usually only need to add or update a single user’s firewall rule at a time when our business users get their IP addressed updated sporadically, but I had a situation last week where I needed to add over 40 firewall rules to an Azure SQL Server resource for a new database I imported on the server. I did not want to manually add that many firewall rules one at a time through the Azure Portal because that sounded tedious, boring, and like too many mouse clicks, so I instead figured out how to do it the fastest way possible–running a T-SQL script directly on the server or database through SQL Server Management Studio (SSMS).

Note: This would also work through Azure Data Studio or your chosen SQL Server IDE, I simply prefer to use SSMS so use that as a reference in comparison to completing the same actions directly through the Azure

What’s in this post

Server vs. Database Firewall Rules

According to this Microsoft document, it should be possible to grant firewall rule access for a user to a single database on a logical Azure SQL Server resource. In my experience in Azure so far, we’ve only ever granted firewall access at the server level instead, since that is acceptable for our business use-cases. But since it’s possible to set the same firewall rules at the database level according to Microsoft, I added how to do that to this post, as well as the server-level rules which I will be creating for my use case.

T-SQL for Creating Firewall Rules

Did you know it was possible to create both database-level and server-level firewall rules for an Azure SQL Database through SSMS using T-SQL commands? I didn’t before I started this project request last week. Going forward, I will likely use the T-SQL route through SSMS when needing to make multiple firewall rules instead of using the Azure portal, to save myself time, effort, and mouse clicks.

Create a Server-Level Firewall Rule

To create new firewall rules at the server level, you can connect to your Azure SQL Server through SSMS and run the following command on the master database.

/* EXECUTE sp_set_firewall_rule N'<Rule Name>','<Start IP Address>','<End IP address>'; */
EXECUTE sp_set_firewall_rule N'Example DB Rule','0.0.0.4','0.0.0.4';

When you run that command, you are executing a built-in stored procedure that exists on the master database that will insert a new record for a server-level firewall rule into the table sys.firewall_rules, creating the server level firewall rule that will allow access through the specific IP address or IP address range. In the example above, I have the same value for both the Start IP Address and End IP Address parameters, but you can just as easily set that to a range of addresses, like 10.0.5.0 for the start and 10.0.5.255 as the end. I usually prefer to do that for a user’s IP address since our systems can change the last value fairly regularly, but since my current task was to grant access for servers, I set the start and end values to the same single IP address since I’m not expecting them to change frequently.

Create a Database-Level Firewall Rule

If instead of granting access to all databases on the server through a server-level firewall rule you would prefer to grant access to only a single database on your Azure SQL Server instance, you can do that as well. The T-SQL command to create a database-level firewall rule is the following. Notice how it’s very similar to the server-level command above, but with “database” specified in the stored procedure name.

/* EXECUTE sp_set_database_firewall_rule N'<Rule Name>','<Start IP Address>','<End IP address>'; */
EXECUTE sp_set_database_firewall_rule N'Example DB Rule','0.0.0.4','0.0.0.4';

The parameters that are required to run this database stored procedure are the same as what’s required for the server-level procedure, so you can either specify the same Start and End IP address values to allow access through a single address, or you can specify different values for the two to give access to a range of addresses. This procedure inserts records into the system table sys.database_firewall_rules on the database you ran the command on.

Speeding up the Creation of Execution Scripts

Knowing the above commands is helpful, and even works if you only have one or two firewall rules to add, but I promised I would tell you the fastest way possible to add 40+ firewall rules to a server, so how do I do that? How do I save myself the trouble of writing the above procedure execution commands 40+ times or copy/pasting/updating the line over and over again?

The trick I use is to generate my SQL queries in Excel, since Excel has the ability to concatenate strings and then to use the same formula across however many rows you have to generate the same concatenated string using multiple distinct values. I use this Excel trick quite frequently, whenever I need to generate the same type of SQL query/command multiple times based on specific values.

In this use case, I was provided a list of the names of the servers that needed inbound firewall access to my Azure SQL Database along with the IP addresses they would be using. I copied that information into two columns in Excel then wrote a formula in a third column to generate the SQL command that I needed to run to make a firewall rule for that particular server and IP address.

Here is an example of how I accomplish this type of task in Excel:

Screenshot of text in Excel demonstrating how to quickly develop SQL queries for adding firewall rules

In Column A of the spreadsheet, I copied in the list of the server names I was provided. I am going to set the name of the firewall rule for each to the name of the server that is being given access. Column B then has the IP address that the server will be connecting through.

Note: all server names and IP addresses were made up for this example.

Then in Column C, I use the CONCAT function of Excel to generate the SQL query that I want, which I will then be able to copy to the database and execute to generate the firewall rule. The following is the full formula I used to generate the SQL command:

=CONCAT("EXECUTE sp_set_firewall_rule N'",A2,"','",B2,"','",B2,"';")

After I’ve successfully made the command as I want it for the first server in the list, I then copy that same formula down the column for every row to generate the same command for each server and IP address combination. Once all the queries have been created, I copy the entire Column C into an SSMS query window:

Screenshot of SQL queries in SQL Server Management Studio for adding firewall rules to the server

I then just have to click Execute and all the commands run, creating all the new firewall rules I needed in just a couple seconds. Believe me, this method of using Excel will save you a lot of time copying and pasting and updating IP addresses in the queries.

View existing Server-Level Firewall Rules Through SSMS

If you would like to review the list of server-level firewall rules that currently exist on your server, you can do so by running the following query on the master database of your Azure SQL Server instance.

select id, [name], start_ip_address, end_ip_address, create_date, modify_date
from sys.firewall_rules
order by id

This list will directly correspond to the list of values that you would see under “Networking” for your Azure SQL Server instance in the Azure portal.

View existing Database-Level Firewall Rules Through SSMS

If you would like to review the list of database-level firewall rules that currently exist on your database, you can do so by running the following query on the database you would like to see the firewall rules for.

select id, [name], start_ip_address, end_ip_address, create_date, modify_date
from sys.database_firewall_rules

As far as I am aware, there is no way to view this same list of details from the Azure portal, so this should be the source of truth for database-level firewall rules for an Azure SQL Database.

How to Delete Firewall Rules Through SSMS

Similarly to how there is a more efficient way to create multiple firewall rules through T-SQL queries in SSMS, you can also quickly delete a lot of firewall rules at once through SSMS using a delete procedure. I haven’t yet had to delete a large number of firewall rules at once, but you can follow the same process I outlined above for adding them, but use the deletion procedure instead.

Delete Server-Level Firewall Rules

The T-SQL command you can use to delete a specified server-level firewall rule through SSMS is:

EXEC sp_delete_firewall_rule N'SERVER1';

When deleting a rule, you only need to provide the name of the firewall rule you would like to delete.

Delete Database-Level Firewall Rules

The T-SQL command you can use to delete a specified database-level firewall rule through SSMS is:

EXEC sp_delete_database_firewall_rule N'SERVER1';

When deleting a rule, you only need to provide the name of the firewall rule you would like to delete.

Summary

In this post, I outlined the full process I used to generate 40+ server-level firewall rules on my Azure SQL Server instance as requested. Before starting on this task I had no idea that it was possible to generate these firewall rules through a T-SQL command in SSMS, I only knew about adding them through the Azure portal manually. But like every good programmer, I knew there had to be a better and faster way and I was correct. I hope this post helps save you a little time as well the next time you need to add more than a couple firewall rules to your server.

Created using icon from FlatIcon.com
November 19, 2024 / 0 Comments

SQL Query to Get Schema-Level Database Permissions

This is going to be a super quick post because I only want to share a short query that I’ve developed for myself to be able to see what schema-level permissions have been assigned to users on an Azure SQL Server. If you didn’t see my previous post about my least favorite things about Azure, you should check that out because it includes other queries I’ve been using to identify what permissions have been granted on a server or database.

The Query

My query I use for getting a list of all schema-level permissions on a database uses the sys.database_principals, sys.database_permissions, and sys.schemas views.

SELECT DISTINCT pr.principal_id, pr.[name], pr.[type_desc],
    pr.[authentication_type_desc], pe.[state_desc], pe.[permission_name], pe.class_desc, s.[name]
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.schemas as s
	on s.schema_id = pe.major_id
ORDER BY pr.name;

As you can see, the query is very short but it provides the most useful and relevant information for schema-level permissions on a database. It provides just enough information without being overly complicated or giving you more than you need. This query tells me what users have been given what type of permissions for every schema in my database.

Made with icon from FlatIcon.com
November 5, 2024 / 0 Comments

Figuring out What Version of SQL Server You Have

If you connect to a SQL Server instance using SSMS and you’re trying to find what version of of SQL Server it is, you might be left scratching your head since it doesn’t seem to be in the server properties anywhere. Or at least there isn’t a value that lines up with the version numbers you’re used to hearing (2016, 2019, 2022, etc.). This will be a quick and easy post explaining how you can find the normal version name for a SQL Server you’re connected to.

What’s in this post

How to Find the SQL Server Version

The first thing you need to do is connect to your server instance using SQL Server Management Studio (SSMS). After you connect, you should see the server listed in the Object Explorer, which is where we will be getting the version number from. To the right of the server name in paratheses, you should see something like “(SQL Server 16.0.1000.6 – myusername)”.

That is technically the most correct version number for the SQL Server software you are running, but it’s not the one that most people think of when they think of SQL Server versions. To get to that version, you can look at the very useful table included on this blog post from SQL Server Builds.

For the example screenshot above, the server is really SQL Server 2022 because the “Release To Manufacturing” number of “16.0.100.6” corresponds to that version of SQL Server, as can be seen in the table included in that blog.

You can also find the same version number by right-clicking on the server in the Object Explorer list, then selecting “Properties” which will bring up a dialog where you can see the version and other settings for the server.

Note: For even more ways that you can find the version number of any SQL Server, there is this very useful blog post from SQL Server Builds again that I found super informative.

Summary

Now you should know how you can find the normal SQL Server version number for your instance based on the “Release to Manufacturing” number you will find to the right of the server name in the Object Explorer of SSMS. I wish they would put “SQL Server 2022” somewhere in the Properties of the server so we didn’t have to do this lookup, but for now we aren’t that lucky.

Note: Curious about what “Release to Manufacturing” really means? I found a great explanation on Wikipedia that clarified that term for me.

Photo by Luca Bravo on Unsplash
October 11, 2024 / 0 Comments

A Week in the Life- 10/7 – 10/10

Have you ever wondered what the normal work tasks of a database developer/integration engineer looks like? If you have, then this is the post for you. This is a new series of posts where I simply give an overview of what I accomplished each week, giving insight into what life as a database developer looks like for those who might be curious. I also want to do these reviews for my own records and edification, because it’s always good to keep track of the things you accomplish at your job. This post is going to review the week of October 7 – October 10.

What’s in this post

More SQL Server Setup

The week started with a request from one of my teammates to set up SQL Server 2022 on another Windows Server virtual machine as part of my ongoing project I am a part of. I have somehow made myself the SQL Server 2022 installation guru, so I’m not surprised that I was requested to complete this task.

Setting up the SQL Server itself was as easy as it could be, easier than the last few I did since the application using the server had less configuration requirements. But after installing the server and making sure the firewall rules were in place (as I learned two weeks ago), I then learned even more setup that I had missed previously that needed to be done for the server. The new thing I had missed was getting Entra ID authentication to work with the server, so one of my teammates showed me how to do that so that the new server was now setup perfectly.

Production Upgrade for an Application

My biggest feat of the week was having to do the production upgrade for the legal application project I’m on completely by myself. I was on this project with one of my teammates, but he had to be out this week for personal matters so I was left as the sole developer working on the production upgrade we had been building up to for months. Although I was nervous about being the only one responsible if something went wrong, I stepped into the challenge and completed the upgrade. I did run into a few issues during the upgrade process, but I was able to work them out by the end of the day by working with the vendor of the application, so I have to say the upgrade went pretty dang well given the circumstances. Pat on the back to me for surviving my first solo production upgrade.

Preparing for the Next Application Upgrade

I couldn’t celebrate this week’s successful application upgrade for too long, because I already had to be starting on the steps for the next phase of this application’s upgrade process. Thankfully, this week’s work for that next phase was only to take backups of two databases and provide that to the application vendor, so I wasn’t overly burdened with work for this part.

Writing Documentation about the Application

After completing that production upgrade, facing a few issues along the way, I knew that I needed to write everything down that I knew about the application and what went wrong with the upgrade, or else me and my team might forget the important but small details. None of us think that this type of work is truly something that we as database developers should be doing, we’re not application developers, but we have been told that we need to support this application from front to back so that is what we’re going to do. And since the territory is unfamiliar to everyone on my team, I know good documentation will be essential to continuing to support this application in the future.

Met with Colleague to Review Conference

There are only two women on my team: me and one other woman. We both attended the Women & Leadership conference two weeks ago so wanted to catch up with each other to review our learnings and takeaways so that we can present those ideas to our manager. This conversation was really lovely. When working in a field that is 98% men like I am, it’s always a breath of fresh air to be able to sit down, relax, and catch up with other women dealing similar tasks and issues. Our scheduled half-hour meeting turned into an hour because we were having a good time and brainstorming good ideas to present to our manager. We left with a list of items we wanted to cover with him and a plan for how to get some of his time to present our list.

Presented new Database Architecture to Division IT Team

I need to be forward and say that I was not the one who presented the database architecture to the other team, but I was included in the meeting since my teammate who normally works with this other team is going to be out of office for two months at the end of the year and I need to be apprised of the work he normally does.

This meeting with a business division IT team (not the corporate IT team that I’m on) turned out to be a fantastic relearning opportunity for me to see the database architecture possibilities in Azure. The backstory is that this other team had requested we create them a new database they could use for reporting purposes to not bog down their main application database. My teammate who normally works with them came up with several different new architecture possibilites that would fulfill the request, and ran those possibilities by the team to see which they would be most comfortable with. I had technically learned this same architecture information shortly after I started at the company, but that time is honestly a blur so I appreciated getting to see this information re-explained. I took lots of notes in this meeting and now feel more confident about making similar presentations and architecture decisions in the future when that will eventually be required of me.

Summary

This was a shorter week for me because I took Friday off, but I still accomplished a lot of big projects. I am looking forward to having some calmer weeks in the future where not everything is a “big stressful thing”, but I do appreciate that this week taught me a lot and helped me grow as a developer. I am slowly easing into the point of my career where I can start to be a leader instead of a follower, so I am taking each and every learning opportunity as a gift helping me to grow into that future leader.

Do you have any questions about what a database developer does day-to-day that I haven’t answered yet? Let me know in the comments below!

AI-generated image showing a server rack in the middle of a server room
October 1, 2024 / 0 Comments

My Least Favorite Thing About Azure Databases

Now that I have been working in the Azure cloud environment for a few months with my new job, I have come to the realization that there is one thing related to Azure SQL Server instances that annoys me more than anything else in the Azure environment. Obviously every cloud environment is going to have its pros and cons, so this is only one small problem from the whole ecosystem. Azure has a lot of nice things to work with, so this complaint is very superficial, but I know others at my company have been confused by this issue so thought I would share the annoying difference here for educational purposes.

On-Prem SQL Server Permissions

The one thing that has been driving me crazy about Azure SQL Server instances recently is that there is not an easy way to quickly get an overview of the permissions a user/account/group has on the server and database.

In on-prem/normal versions of SQL Server, you can open the “Security” section of the server or database then right-click on the user or group you want to review the permissions for, and then you’ll get a GUI that quickly shows all the server- or database-level permissions that user has. The following screenshot shows the Server Roles for a given login on my local test server, so you can quickly know what permissions the login has.

And then if you go to the “User Mapping” page of the Login properties, you can see any databases the login has access to as well as the permissions that user has on the database.

I love this interface that I’m pretty sure has been around for decades. It’s so much easier to view and quickly change the permissions for a login/database user that new new system in Azure-based servers.

Azure SQL Server Permissions

In comparison, you can’t easily view or determine the server- or database-level permissions any login or user has on an Azure-based SQL Server. You can’t even view any general properties of the user like you can for on-prem installations. For example, here is a screenshot of what you see on an Azure SQL Server when you right-click a Login from the Object Explorer:

If you do the same thing on the on-prem SQL Server, you can see that there’s an option to view the properties of the user, which will get you to the menus where you can see server- and database-level permissions, along with other properties as well.

If you can’t get a nice GUI representation of those login permissions, how are you supposed to know what permissions anyone has on your server or database? A SQL query.

While that kind of makes sense for a platform where you do most of your work with SQL, it does mean you need to do more work to write or track down a copy of the appropriate SQL query to get the necessary data, instead of to right-clicking on an object and have its information displayed to you immediately. The SQL query you need to get permissions information is also tedious to me because you can only do it for a single database at a time instead of being able to quickly view the permissions for every database on the server in one place. For example, if you have 3 databases on your server, you need to run the query individually for each of those databases. You can’t even utilize the “USE [database]” statement to quickly switch between the databases and get all the results in a single result pane (if you want to read more about that, I have another upcoming post to discuss that topic).

This is the query I use to review database permissions:

SELECT DISTINCT pr.principal_id, pr.[name], pr.[type_desc],
    pr.[authentication_type_desc], pe.[state_desc], pe.[permission_name]
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
    ON pe.grantee_principal_id = pr.principal_id
ORDER BY pr.name;

If you want to view the permissions at the server level, run that query on the master database. Then to view the permissions specific to a single database, you will need to switch to that database (using the database dropdown from the toolbar above the query window, or updating your full server connection to specify the other database).

To view the role assignments for users in your database, you can use the following query, which utilizes the views sys.database_permissions, sys.database_principals, and sys.database_role_members to gather all the useful information related to role assignments.

SELECT u.[name] AS [UserName],
       r.[name] AS RoleName 
FROM sys.database_principals u
     JOIN sys.database_role_members drm ON u.principal_id = drm.member_principal_id
     JOIN sys.database_principals r ON  drm.role_principal_id = r.principal_id
WHERE u.[name] = N'TestUser';

Summary

While it’s not hard to get all role assignments and assigned permissions for logins and database users in Azure SQL Server instances, it isn’t as easy as it used to be with on-prem servers. I don’t think the new method of getting the permissions gets the information you need in as useful a way as the old method of using the GUI. I personally don’t prefer having to scroll through a table of information about each user and deciphering the details of their permissions, but I will continue to deal with it since that’s the only option for Azure SQL Servers currently.

Photo by Luca Bravo on Unsplash
September 27, 2024 / 0 Comments

A Week in the Life- 9/23 – 9/27

Have you ever wondered what the normal work tasks of a database developer/integration engineer looks like? If you have, then this is the post for you. This is a new series of posts where I simply give an overview of what I accomplished each week, giving insight into what life as a database developer looks like for those who might be curious. I also want to do these reviews for my own records and edification, because it’s always good to keep track of the things you accomplish at your job. This post is going to review the week of September 23 – September 27, 2024.

What’s in this post

Meeting with a Software Vendor

One part of my job is interacting with various software vendors that we buy products from. I’m currently on a longer-term project where we are doing a piece-by-piece upgrades to one application that our legal department uses, so this week I met with the vendor’s development team to get an overview of the next step of this upgrade process.

One of the joys of working with vendors is sometimes you go into these meetings thinking the purpose is completely different than what it ends up being, then you have to rethink everything you planned for the meeting on the fly. That was how this meeting went. I wasn’t expecting that I would be the one taking lead on driving the topic by asking questions we needed answers to on our side before we could move forward with the next step of the upgrade. I thought the meeting was going to be the vendor giving an overview of the product for my own learning, not me driving the conversation. As someone newer to the company, this change was a little scary, but I handled it well and got good information for our team from the vendor.

Updating Security Certificates

This part of technology is something I’m still not very familiar with but had to dive into this week. My team had an ETL that broke because it could no longer communicate with our customer’s software due to an outdated security certificate. To fix this broken ETL, I had to locate the correct updated certificate and put that on our ETL server and remove the old one. After making such a small change, things were back to working as they normally do. This problem was a good learning opportunity and not nearly as difficult as I was expecting it would be.

Emergency Database Refresh

I got to experience my first emergency refresh request in our Oracle database environment this week because something went wrong with one of our production databases and the app development team wanted to refresh the database into the lowers as quickly as possible to start troubleshooting. I think fate decided we should have a good day, because our Oracle cloning process into our test environments went off without any issues and finished in about an hour, which is almost a record low. We’ve ran into numerous issues in recent months with the pluggable database cloning process in Oracle, so we are very thankful that one of those problems didn’t arise when we needed this emergency refresh.

Completing Database Permissions Requests

Another piece of Oracle work I got to experience for the first time this week was updating user permissions. We received a request from one of our application developers that they needed a service account to be granted the same level of permissions that his own account had (in test) so that he could complete a task. Although the SQL needed to complete a permissions change in Oracle looks a little different from what I’m used to in SQL Server, it overall was very similar to making permissions changes with T-SQL so was easy to complete.

Attending A Women’s Leadership Conference

My favorite part of this week was getting to spend Tuesday and Wednesday at the Women & Leadership Conference by the Andrus Center at Boise State. This was my first time attending this conference, and I was able to go with the handful of other women in the IT department.

As would be expected for this type of conference, the sessions focused on building soft skills in women instead of hard technical skills like previous work conferences I’ve attended. I listened to a range of women speakers, all of whom are in various leadership roles in different fields and states across the country. I wouldn’t say I loved every session I attended, but most of them were interesting and I learned a lot of tips for managing in the work environment as a woman that I hadn’t thought of before. In the near future, I will be writing a recap with more of my learnings from the conference, if you’re interested in hearing more.

Troubleshooting a Virtual Machine (VM)/SQL Server Connection Issue

This issue was not something I had ever considered before, because I’ve never had to think about the networking setup for VMs that I’ve worked with. Normally, someone else sets up the VM and makes sure it has all the networking and firewall rules needed before we get access to the VM. However, one of my project teammates and I found out that it’s not always the case.

I am working on a different application upgrade project from the one I mentioned above, and I was in charge of setting up those SQL Servers last week. This week, the main application developer started his portion of setting up the VMs we put the SQL Servers on, and he found that he was unable to access the SQL Server from his local computer version of SQL Server Management Studio (SSMS). When he messaged me about this connection issue, I had no idea where to start troubleshooting it since I had never had to think about how SSMS connects to the SQL Server on the VM before.

I had to work with our networking team to figure out the issue, which ended up being that the local firewall on the VM was not setup to allow ingress from the two ports that SSMS/SQL Server requires for connections. The networking team wrote two commands to allow that ingress, ran the commands on all 3 servers we setup last week, and then we were able to easily connect to the new SQL Server instances from our local SSMS apps instead of having to login to the VMs directly.

Finishing and Presenting a Python Data Analysis Script

One of the things I was most pleased and excited about this week was finally getting to demo a complicated Python script I wrote to optimize customer orders based on their previous order history, given a list of input parameters from the customer. I have spent months working on this script, going back and forth with the business users about what should be included, adding new features when requested, and even totally reworking the algorithm when their requests got more advanced. My demo of the script had an audience of the business users as well as members of my own team, including my manager.

The demo itself went really well, the script worked exactly as I wanted it to, running in under 5 seconds to optimize the customer data and provide a recommendation for what should be stocked to fulfill those orders, which is a massive improvement from the current process that takes a week to calculate the best possible solutions. What did not go as I expected was hearing from the business users that they are unsure if the solution provided is accurate enough, so they’re afraid to move forward with my script unless I did several things that would likely lead to several more months of rework. At the end of the meeting, we had decided as a team that instead of implementing this solution I worked hard on for months, that isntead we would put their current process onto a newer and beefier server in hopes that it would run faster than the current week.

No developer wants to spend months working on something just to be told that it won’t be used due to reasons that are out of the developer’s control, but that seems to be the situation I am in now. I am staying positive about it though, because it was a great Python development learning opportunity for me when I came into the company, and I have faith that with time, the business users will come around to using the faster and more modern solution when they see that the results I produced are very close to what they already get with the current solution. I might need to do a little more tweaking to get my algorithm’s results into an acceptable range compared to the current process, but I am hoping it won’t require a full rework to do so.

Summary

Sometimes I have weeks at work where I feel like I haven’t accomplished all that much. This week was one of those. But now that I have typed out everything I did like this, I am seeing that I do a LOT of work while I’m at work, and I am proud of everything I learned and accomplished this week, even if every single work item did not go as planned.

Being a database developer or data integration engineer comes with a lot of variation in work, which you can probably see by looking at this week’s and last week’s summaries. There is always something new to learn and work on, so I’m excited I’ve had another interesting week of work and I look forward to next week being interesting as well. (Although I’m technically on vacation next week, so really I mean the following week.)

Do you have any questions about what a database developer does day-to-day that I haven’t answered yet? Let me know in the comments below!

Screenshot of the SQL Server installer dialog with the option to create a new instance or update an existing one highlighted with a red box.
September 24, 2024 / 0 Comments

SQL Server 2022 – Getting the Installer Link to Work

I recently went through a whole day long ordeal trying to figure out how to get the ISO/installer for SQL Server 2022 so I could install it on a few VMs I made for a software upgrade, which I wrote about in a post last week. After spending way too much time just getting the installer downloaded, I then ran into another issue where when I clicked on the link to do a new installation of SQL Server 2022, the link did absolutely nothing. The solution for this problem ended up being simple, but it had me scratching my head and bothering my teammates to try running the installer as well before I figured out what needed to be done.

What’s in this post

The Problem

When you run the setup.exe or installer for SQL Server 2022, and select the option for “New SQL Server standalone installation or add features to an existing installation”, the software may do nothing when you click that link. It may have a spinning cursor for a small moment, but then nothing happens at all, no matter how long you wait.

The Solution

I found the answer to this issue on StackExchange. When I first downloaded and ran the installer, the standard Windows dialog came up asking if I wanted to trust the publisher of the program, since it was Unknown. I clicked yes. But despite that apparently, my computer had the file set to blocked, so when I clicked the link to start a new installation, the installer was blocked and nothing ran. To fix this problem, do the following.

Go to wherever you have the ISO file saved, for me this was the Downloads folder. Right-click on the file and select Properties. Then on that General/main page of that dialog, at the bottom there will be a checkbox that says “Unblock” with a message about security if you unblock the file. Check the box then click “OK”.

If you were experiencing the same issue that I was, you should now be able to reopen the installer file and click the link to start a new installation, and move on with your day.

Summary

There seems to be a bug with the installer for SQL Server 2022 where clicking the option to start a new installation seems to do nothing. This bug can be resolved by “Unblocking” the installer file through the Properties dialog of the file.

Photo by Luca Bravo on Unsplash
September 20, 2024 / 0 Comments

A Week in the Life- 9/16 – 9/20

Have you ever wondered what the normal work tasks of a database developer/integration engineer looks like? If you have, then this is the post for you. I want to start a new series of posts where I simply give an overview of what I accomplished each week, giving insight into what life as a database developer looks like for those who might be curious. I also want to do these reviews for my own records and edification, because it’s always good to keep track of the things you accomplish at your job. This post is going to review the week of September 16 – September 20, 2024.

What’s in this post

SQL Server Administration

This week was a bit of a throwback for me because one of my main tasks was to get SQL Server 2022 and one particular database up and running on three different virtual machines (VMs) for an application upgrade project I am working on. I have not created a new SQL Server, created a database backup, or restored a database backup in years. At my previous job, there was a dedicated team of DBAs that did that sort of work, and I was not on that team. But my current role is more of a jack-of-all-database-skills role, so it was time to blow the dust off my server creation skills.

The first step, of course, was to install SQL Server 2022 on each of the Windows VMs, following some guidance from the application vendor. I have never installed any SQL Server with so many custom configurations, so that was an interesting new thing to learn and do this week. After taking too much time to install SQL Server on each of the three Windows servers, I also installed SQL Server Management Studio (SSMS) 20, the main app used to interact with SQL Servers and their databases, on the VMs as well. That was the easiest part of this whole thing.

Before I could restore a copy of our production database onto these new servers, I needed to copy all existing/needed logins from the current production server to each of the new test servers so that the database restoration wouldn’t fail due to a user not having a corresponding login. I also did not want to have to manually repair all those database users when the SID values they were created with didn’t match the SIDs of the logins if I created all the new logins from scratch. This left me wondering how I could script out all the existing production logins, including their passwords (we have a lot of SQL auth logins on this server), so I didn’t have to do any of it manually. I found this very helpful Microsoft article that includes scripts for creating two stored procedures that you can then run to generate the perfect CREATE scripts for every login on a server. I would highly recommend using these scripts if you need to migrate/copy a database to a new server.

The final step that I needed to accomplish was to backup and restore a copy of the current production database to each of these new test servers so the app developers can do their side of the upgrade testing. I had to meet with one of the app developers so we could do our portions of the backup of production at the same time, since that is important on the app side to keep all data in the system aligned. Creating a backup was just as easy as I remembered.

Unfortunately, after I had the 97 GB compressed database backup file created, I then had to figure out how I was going to copy that file from the current production VM to the new test VMs, because we do not have any of these VMs networked together. My first idea was to simply do a copy/paste action from one VM to another, but that was projecting it was going to take 17 hours for one copy, and it kept failing after a few minutes with an unspecified error anyway (I’m guessing network issues). I could not think of any better way of getting the file from one server to another, and Google wasn’t being helpful for my specific situation, so I asked my main mentor coworker what he would do, and he suggested a brilliant idea: upload the backup file to one of our storage accounts in Azure by logging in on the source database, then after it’s uploaded, login to the storage account on the destination servers and download the file. This was the best idea for this situation (and only works because we do not limit internet access on our Windows server VMs, which I had at previous roles, so this method might not work for you). The upload of the 97 GB file took about 25 minutes. The download onto each of the destination servers was about 15 minutes. Much faster than the projected 17 hours of copying over our VPN directly from one to the other!

Ongoing Learning

As a part of any career, one should always be striving to learn new things and grow in their role. My current organization is fantastic at helping me to do this. Not only on an official level with conferences and online trainings, but also unofficially with everyone being willing to share what they know about our systems at any given time.

This week, I got to attend a casual training led by one of our Oracle app developers to tell us everything he knows about the Oracle Grid infrastructure and Automatic Storage Management (ASM), since those topics came up while we were troubleshooting an Oracle issue a few weeks ago, and I knew nothing about them. I also attended a more formal training with our Microsoft representatives to start wrapping up training we’ve been doing for a couple months on Azure AI and Machine Learning tools. Both of these training sessions were informative and useful and make me feel better prepared to handle upcoming projects and issues.

Python Development

Since I started at Boise Cascade, I’ve been doing a lot of development in Python, and am currently working on a project to accurately calculate optimal solutions for fullfilling customers requested wood orders. This project has been a big struggle, not due to technical issues, but due to changing requirements from the business customers, which means I’ve had to write two totally different solutions for this same problem over the past couple of months. However, the struggle is finally almost over because I have mostly wrapped up my rewrite of the solver using a new algorithm that a colleage wrote the basis of for me. (Yay for more experienced developers helping us solve difficult problems!) I am very excited about coming to a close on this program and will hopefully demo it to the business users next week to get final feedback and get it moved to prod in the very near future. The users are also very excited since this new program solves a problem in less than 5 seconds when the previous/current solution takes a literal WEEK to do the same thing. I have found it really interesting to write this program, but I also know I will find new projects equally as interesting when I finally get to move on.

Preparing for New Projects

Once I finally wrap up my wood order optimizer, I will get to move on to rewriting a different solver currently used by someone in the company who would like it automated away. To get up to speed on what the user is requesting, I met with one of my teammates this morning to get context on the solution that is currently in place and what the user wants the new solution to do differently. Basically, I needed to figure out what the heck this project was even supposed to be about. In my prep meeting, I learned that I will definitely need to meet with the user to discuss what they expect and need from the solution since they’re the one currently doing the solving manually, but I at least now have a general sense of what is being requested. I am looking forward to getting started on this project once I have the time.

Summary

That is my whole week in review! While database development usually does include a lot of writing SQL, developing ETLs to move data around, and doing other things directly with databases, that does not mean the work will always only consistent of those types of tasks. It all depends on the company you work for and how your specific role is defined. I am very thankful that my current role includes those types of tasks but also adds other work like writing data integration scripts in Python so that I can stretch my skills and knowledge.

Is there something specific you were hoping I would cover about my time at work that I didn’t cover in this post? Let me know in the comments below!

« Older posts

Categories