How To Give Permission To Developers For Change Their Passwords On SQL Server(Grant Alter)

dbtut

6 years ago

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

1	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

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'

1	ALTER LOGIN [test] WITH PASSWORD='12345' OLD_PASSWORD='1234'