Sometimes developers need a permission for change their login’s password.
if you do not give them to necessary permission they can not change their password. They will get an error like below.
Cannot alter the login ‘test’, because it does not exist or you do not have permission.
Normally for a single login we can give this permission as follows.
GRANT ALTER ON LOGIN:: testUser to testUser
But sometimes we may want to do the same for all the logins on the instance.
With the help of the following query we can prepare a script bundle to do this.
select 'GRANT ALTER ON LOGIN:: '+s.name+' to '+s.name from master..syslogins s
where name not like '%mydomain%' AND name not like '%NT%' AND name not like '%#%' AND name not in ('sa')
order by s.[name] asc
You can also tell the developer that you can change your own password by using the following script.
ALTER LOGIN [test] WITH PASSWORD='12345' OLD_PASSWORD='1234'