creation of sql authentication login with a specific requirment

Question

creation of sql authentication login with a specific requirment

vsamantha35

SSChampion

Points: 11551
More actions
June 18, 2020 at 4:51 pm

#3764218

Hi All,
As an admin we create sql authentication logins for application users.
For those logins, we want to give the ability for the user to change the password and should never expire.
To accomplish this requirement, what specific permissions we should give for the sql login?
Thanks,
Sam

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

A SQL login already has permissions to ALTER LOGIN and can use that to change their password. I would not recommend allowing them to set the password to never - this should be controlled by the same policy set for windows user names, in fact - they probably shouldn't be using a SQL login for personal accounts.

If these users are accessing the system through an application - and the application requires a SQL login then the application will need to provide that functionality.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

vsamantha35 SSChampion Points: 11551 More actions · Answer 2

Hi Jeffrey,

Thanks for sharing your thoughts. I has little curious on how to implement this in sql server.

So, I was checking the MSDN documentation and I have some questions based on what I tried.

1. If I give the ALTER ANY LOGIN , then this guy can alter other logins passwords as well right?. If that is the case, is there a way to avoid it? I want to allow him to change is his own's login password but not others login password.

2. What is the minimum age/minimum number of days the password gets expired? I didnt find any documentation around it. The reason, I was asking this is, I created a login with minimal permissions and there is a point mentioned in msdn documentation that you can change your password without ALTER ANY LOGIN permission, but I endup getting below error message. This is what I followed

/*

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-login-transact-sql?view=sql-server-ver15

C. Changing the password of a login when logged in as the login

If you are attempting to change the password of the login that you're currently logged in with and you do not have the ALTER ANY LOGIN permission you must specify the OLD_PASSWORD option.

*/

--- as a sysdamin user create the login Mary

USE [master]

GO

CREATE LOGIN Mary WITH PASSWORD=N'hjshshhssj#123',

DEFAULT_DATABASE=[master],

CHECK_POLICY=ON,

CHECK_EXPIRATION=OFF

GO

use [master]

GO

GRANT CONNECT SQL TO Mary;

GO

-- open a new connection in ssms and connect using "Mary"

select SYSTEM_USER

--Mary

ALTER LOGIN Mary

WITH PASSWORD = 'new pwd' OLD_PASSWORD = 'oldpwd';

ERROR:

Msg 15114, Level 16, State 1, Line 79

Password validation failed. The password for the user is too recent to change.

Thanks,

Sam

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

You will need to speak to your windows admin team.

CHECK_POLICY = ON will force the user to use the DOMAIN POLICY for password.

So complexity requirements, length of password, minimum age of password etc. That is configured by YOUR organisation only your people will know what the settings for that are.

As a side note why are you using SQL auth, this is where you should really be using Windows auth and unloading the authentication to the Domain Controllers instead. Use groups to keep the administration down and save yourself hours of ongoing pain having to change multiple users permissions individually etc.