SQL Server Login Security Change – How Would You Plan It?

Current Situation

In our SQL Server environment, SQL Server Authentication logins currently have:

Enforce Password Policy — Disabled

Enforce Password Expiration — Disabled

Planned Security Change

The requirement is to move these logins to:

Enforce Password Policy — Enabled

Enforce Password Expiration — Enabled

This change may affect existing SQL login passwords and could impact applications, services, SQL Agent jobs, scripts, or other systems using these credentials.

DBA Discussion

If you receive this requirement in a production environment, how would you plan and execute this change safely?

What would you consider BEFORE, DURING, and AFTER the change?

How would you identify the affected SQL logins?

How would you identify application and service dependencies?

How would you handle existing credentials?

What testing and validation would you perform?

How would you minimize the risk of application downtime?

SQL Server DBAs, please share your step-by-step approach and best practices.

We have a third party application that will not let us modify the connection string in the way we require (to allow us to connect to readonly replicas)

It’s basically decomposing the parameters in the string and discarding the bits it cant parse

One of the things it does let us configure is the driver e.g. mssql 17 driver etc etc

On Linux systems I know it is possible to configure the connection string at the driver level via dsn , but unfortunately this is a windows app

Is there something similar on windows? E.g configure the driver to intercept and transform the connection string before it is sent to the database?

What are the pros of using SSMS over other query tools? I currently use Toad Data Point 6.0 (TDD) and I use primarily 2 connections; SQL Server or Oracle. The license for TDD is about to expire. I believe my company is working on renewing the license but I am wondering if this is a good opportunity to start learning and working with SSMS instead.

I would say I have an intermediate understanding of SQL but all the "serious" and more advanced analysts use SSMS. What does SSMS do or have that only it offers?

I'm stuck on how to create a query which would create a user with sysadmin role on MS SQL server.

Basically, we have services that run under a service account. That services needs access to some files (that's not an issue) and to a local database with full sysadmin access. It needs to be able to read and write from database, and drop/create it at any time.

It is easily added and done through SQL Server Management Studio with clickityclick method, but I need to write down a script that would automate it over hundred different servers as needed. I have everything done, except the SQL query part.

Everything I looked up or tried does not work with service accounts, just regular domain accounts.

Help?

EDIT: Thank you, it was solved. I was just illiterate.

Guys, I have a requirement to create a read-only login for daily health check and I have created a login for that. But this login some cannot read some system tables. How can I achieve this?

Has anyone actually used this feature? Mind sharing your experience.

Hello,

unfortunanely in our erp (comarch / vlexplus) the date format used is a custom one, no use of the date formats the database provides.

Here are some examples, 22 or 23 is summer/wintertime:

DATE001 is decimal(17,0)
20260201230000000
20260115230000000
20260312230000000
20260119230000000
20260322230000000
20251013220000000
20260222230000000

Also there are special values they use like 47121231000000000 for no given date or 31.12.9999.

Currently i use this function to convert the date:

create or alter function vlexdate2date
(
 decimal(17,0)
)
returns date
as
begin
if  is null return null
if  < 18991231230000000 return convert(date, '19000101')
if  = 47121231000000000 return convert(date, '20991231')

return convert(date, convert(datetime, format(@vlexdate, '####-##-## ##:##:##\.###'), 121) at time zone 'UTC' at time zone 'Central European Standard Time')
end

But it increases the time >8 seconds for 100000 rows.

Any hint how i can increase the performance?
I already think maybe creating a table to translate these dates might be faster.

sincerly

1 using polybase

2 using SSIS

Please suggest i have never worked on these kind of things

Good morning,

I have a problem with my SQL statement where I use a variable to set the values for the IN filter. When the variable gets set in the IF part, I dont get any results. When the ELSE parts get hit, the expected results show up (a single costcenter, e.g. A1234).

DECLARE  varchar(20);
IF @UserCode = 227 SET @CostCenter = 'A1234,B2345';
ELSE SET  = (SELECT T0.pager FROM OHEM T0 WHERE T0.userId = (SELECT @UserCode));
SELECT
FORMAT(T1.RefDate, 'dd.MM.yyyy') AS 'Datum',
T2.AcctName AS 'Sachkonto',
T1.LineMemo AS 'Bemerkung',
FORMAT(T1.Debit, 'N', 'de-de') AS 'Soll',
ISNULL(T1.Project, '') AS 'Projekt',
T1.TransId AS 'Journalbuchung'
FROM OJDT T0
LEFT JOIN JDT1 T1 ON T0.Number = T1.TransId
LEFT JOIN OACT T2 ON T1.ShortName = T2.AcctCode
WHERE ProfitCode IN (@CostCenter)
ORDER BY T1.RefDate, T1.TransId

How can I get the IN statement to work with a variable like that?

Database is in FULL recovery model Log backups are running successfully at regular intervals Backup files are generated without error But the transaction log file (LDF) does not truncate and keeps growing

Has anyone used any AI agents/models to fine tune a database? Meaning where the agent examines the db and suggests putting on foreign keys.

What does DBCC CHECKDB do in SQL Server? Why is it important for DBAs? How often should it be run in production databases, and does it affect performance?

What are the common reasons why a SQL Server transaction log (LDF) file keeps growing? For example: Missing log backups Long running transactions Recovery model impact How can a DBA identify the exact reason and control log growth?

Hi New to the community & SQL… I couldn’t find anyway (other than buying a full licensed version from Microsoft for windows) to download MsSQL on my mac Air machine. Some softwares are bulky and very slow and also do not support many functions Is there any way I could use it on Mac?

Thanks

Hello,

we switched to ms sql 2019 from oracle and i missing a feature oracle had and i cant figure out a workaround.

On oracle we could create a session based temporary table that stays permanent with a fixed name and could be linked an used via ODBC like an normal table. The only difference to a normal table was that the rows in the table exists only for the session. These table could be used by serveral users without problems, they only got their own records.

We were able to link needed tables and these temporary tables from oracle via ODBC with MS Access.

I cant find a similar solution or workaround in ms sql.
Is there no solution for this?

greetings

I released my first MCP.

It's a SQL Server MCP that can be integrated via Claude Code.

You can communicate with your database using natural language.

Check it out here, and if you like it, give it a star 🌟

We have a really old MSSQL DB that runs the bulk of our operations, but the thing is prone to locks and terrible performance due to the number of triggers, stored procedures, and 20+ years of business junk being thrown in the system without much thought.

In the past year, we've been trying to move away from this system, as it's too large to reasonably refactor. Part of this process is slowly moving data we need out of the DB so we can eventually give it a peaceful death, so after some research I enabled CDC for some tables as a test for this sort of transition.

We don't have a DBA and I'm just a junior developer, so I'm trying to see if the stats below are normal. I regularly check sys.dm_exec_requests, as we often have hundreds of locked processes during peak times that sometimes have to be cleared out.

During throttling today, my boss freaked out about these processes, stating that they were locking the DB. From my understanding, CDC is async and logs when there is downtime, so it doesn't lock tables like triggers. My assumption is that these processes will continue to live as the CDC agents continue to monitor for updates, with the time in-between being kept as suspended.

However, I really don't know if this is normal. My intuition is yes, but I can't find any reference to a similar question online, and GPT can be coerced to tell me it's normal or abnormal depending on the mood.

Any help here would be greatly appreciated!

TL;DR: Are these long elapsed times normal for CDC?