question

SQLDBA123 avatar image
SQLDBA123 asked

SQL Server logins password not to expire option instance level for all logins ?

Is there any command , SQL server logins password not to expire option instance level for all logins ? Similar like oracle profile no expire.(unlimited)? OR Is it possible with SQL Server Policy based management? I know each login how to alter command. But looking one configuration or command to not to expire password for all logins? Alter login loginName with check_policy=off Thanks in advance
securitylogin
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
This site requires you to vote on the answers. For all the useful answers below, please indicate this by clicking on the thumbs up next to those answers. If one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
No way to do that for all logins in a single command. Since you know the command, a good approach would be to go through each login and set it. You could use a cursor from T-SQL or PowerShell to make it happen.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

emil87b avatar image
emil87b answered
USE [master] GO SET NOCOUNT ON DECLARE @LoginName NVARCHAR(200) DECLARE @max INT DECLARE @i INT DECLARE @SQL NVARCHAR(MAX) CREATE TABLE #T (ID INT IDENTITY(1,1), LoginName NVARCHAR(200)) INSERT INTO #T SELECT name FROM sys.server_principals WHERE type = 'S' AND name not in ('sa') AND name not like '##%' SELECT @i = MIN(ID), @max = MAX(ID) FROM #T WHILE @i <= @max BEGIN SELECT @LoginName = LoginName FROM #T WHERE ID = @i SET @SQL = 'ALTER LOGIN '+@LoginName+' WITH CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF ' PRINT @SQL EXEC(@SQL) SET @i += 1 END DROP TABLE #T
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.