locked
SQL Server Login Question RRS feed

  • Question

  • Please I am typing this trans:

    USE [master]
    GO
    CREATE LOGIN [new] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    use [master]
    GO
    GRANT ALTER ON LOGIN::[testLogIn] TO [new] WITH GRANT OPTION
    GO

    After executing it and Enter as Login [new]

    EXECUTE AS LOGIN='new'

    GO

     

    I type

    ALTER LOGIN [testLogIn] WITH PASSWORD ='12'

     

    I recieve an error which says that i dont have permission ,so I logged again as administrator and then change the permission from server property which is

    GRANT ALTER ANY LOGIN TO [new]

    and I logged as [new].

    Now when I write ALTER LOGIN [testLogIn] WITH PASSWORD ='12' Every thing is good

    My question is What is wrong with changing specific login permission on others?

     

    Sunday, August 17, 2008 6:50 PM

Answers

  • ALTER LOGIN on a login does not allow you to reset the password of the login - it only allows you to change the password. A password change is different from a reset in that it has to specify the old password via the OLD_PASSWORD clause.

     

    ALTER LOGIN only allows password changes and changing the default database and default language settings. For any other login change, such as password resets, you need ALTER ANY LOGIN.

    Tuesday, August 19, 2008 9:37 PM
  • The ability of resetting passwords shouldn't be granted lightly.

     

    Even if you don't grant securityadmin privileges to weblogin, the fact that he can reset the password of other users, including other securityadmins, makes him a de facto securityadmin. So why not grant him the privileges directly?

     

    You could use certificate signing or impersonation to have the procedure run with elevated privileges, but if you choose to do that, you should add some sort of custom auditing to the procedure, so it logs any password reset - that would allow an administrator to monitor that the procedure was not used to reset the password of any sensitive login.

     

    I'm not sure why a developer should be able to reset passwords. Maybe you should further restrict the use of the procedure to work on login names that follow a specific pattern, like testlogin_*. Then you'll know the developer won't be able to affect any important logins (assuming none follow that naming pattern).

     

    Friday, August 29, 2008 8:42 PM

All replies

  • ALTER LOGIN on a login does not allow you to reset the password of the login - it only allows you to change the password. A password change is different from a reset in that it has to specify the old password via the OLD_PASSWORD clause.

     

    ALTER LOGIN only allows password changes and changing the default database and default language settings. For any other login change, such as password resets, you need ALTER ANY LOGIN.

    Tuesday, August 19, 2008 9:37 PM
  •   The permission was grated on a specific LOGIN securable, not at the server scope. In this case it is necessary to query the appropriate securable:

    SELECT *

    FROM fn_my_permissions('testLogIn1', 'login')

     

      I hope this information helps,
    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Wednesday, August 20, 2008 4:06 AM
  • Thanks very much, your answer was very helpful and specific.

     

    Sunday, August 24, 2008 2:13 PM
  • hi..

     

    I have created procedure for a web developer to use.

    I created a stored procedure.

     

    Set up a role added the login weblogin and then granted exec rights.

     

    Do i have to grant securityadmin rights to teh weblogin user or is there another way i can allow the weblogin rights

    just to run this procedure.   The weblogin is an NT Login account.

     

    Cheers

     

    create procedure [usp_Login_Reset]

    @Password nvarchar(20),

    @username nvarchar(100)

    as

    DECLARE @SQLCMD NVARCHAR(500)

    BEGIN

    Begin

    SET @sqlcmd = 'ALTER login ' + quotename(@username) + ' with password = ' + quotename(@password, '''');

    EXEC (@sqlcmd);

    END

     

    Friday, August 29, 2008 7:52 PM
  • The ability of resetting passwords shouldn't be granted lightly.

     

    Even if you don't grant securityadmin privileges to weblogin, the fact that he can reset the password of other users, including other securityadmins, makes him a de facto securityadmin. So why not grant him the privileges directly?

     

    You could use certificate signing or impersonation to have the procedure run with elevated privileges, but if you choose to do that, you should add some sort of custom auditing to the procedure, so it logs any password reset - that would allow an administrator to monitor that the procedure was not used to reset the password of any sensitive login.

     

    I'm not sure why a developer should be able to reset passwords. Maybe you should further restrict the use of the procedure to work on login names that follow a specific pattern, like testlogin_*. Then you'll know the developer won't be able to affect any important logins (assuming none follow that naming pattern).

     

    Friday, August 29, 2008 8:42 PM
  • I don't know if I understood your question right but if I did then

    after writing the :

    Create Procedure [..]

    @[parameters],,,,,,,

     

    Now add

    WITH EXECUTE AS 'LoginName'

    Such that any one call  this procedure will exexute it as the 'loginName' even if you are doing something in the stored procedure that the caller doesnt have permission to.

     

    Have a nice day

     

     

    Saturday, August 30, 2008 10:45 AM
  • hi, the procedure gets called after a validation check and only for an application logins...

    the web developer is creating the front page for users to reset their logins for the application.

     

    I will also add the additional checks in here too - sorry did not post that part of the procedure it does a check i.e

    not sa, etc..   also it emails to me if reset.

     

    The WITH EXECUTE AS 'LoginName' could you help throw a little more my way never used the EXECUTE and read up on it but don't get it.

     

     

    So i have a login created called weblogin. 

     

    Database

       Added domain\weblogin

       Added Role and domain\weblogin

       Procedure usp_reset_login

       Then granted rights to the domain\weblogin for the usp_reset_login.

     

    So rather than add securityadmin to weblogin i would do

     

    WITH EXECUTE AS 'LoginName'    which would be domain\weblogin

     

    So is this what i would need.

     

    I added

    ALTER procedure MYLOGINRESET

    @Password nvarchar(20),

    @username nvarchar(100)

    --as

    --execute as login

    WITH EXECUTE AS 'domain\weblogin'              

     

    Still get this is trying to reset a userlogin that i have set up called test.

    Cannot alter the login 'test', because it does not exist or you do not have permission.

    Saturday, August 30, 2008 8:44 PM
  • Hello..

    Actually you are not allowed to change specific logins unless the login you are executing from has the right to Alter any login (right click on the server -> properties and then press on that login and check the Alter any login choice), but in your case you must specify the old password in your Alter Login Statement (see the CFG on MSDN for Alter Login).

     

    I hope it will work..........

    Sunday, August 31, 2008 5:53 AM
  • This be from a webpage so do you have other suggestions how i can allow this.   I have to set up all the logins with alter any login? I need the web page to be doing the alter.

     

    Little lost i can add the securityadmin but wanted alternative.

     

    Sunday, August 31, 2008 3:23 PM
  • Hello............

    I dont know if this will work  but  CREATE  LOGIN 'X' for example and give X the permission to alter any login

    after that from the web page add a button such that when the user press you will initialize the password and the login name (ConnectionString) and then create SqlCommand which is Alter login -- with password --- [these values should be taken from textBoxex] and then execute that command, so this will work without being X in sysAdmin Role.

    Try it and I hope it will sove your problem

    Tuesday, September 2, 2008 7:37 AM
  • Yes the web page built.....Alter Login in the server properities for the domain\weblogin does allow you to execute the procedure for alter login.

     

    However if i know login as the domain\weblogin this user can then delete logins.

     

    Anyway to prevent the deletion of logins and only change the logins in a particular DB and not beable to delete sa or change sa for example.

     

    The procedure has all these checks but this does not prevent the developer coming in as the domain\weblogin and playing around.

     

    Is there a way to put in GRANT ALTER LOGIN to domain\weblogin and then after alter do a REVOKE.

    So only if sp is called can it do the alter..

     

    Phew.

     

     

    ALTER procedure [dbo].[usp_Login_Reset]

    @Password nvarchar(20),

    @username nvarchar(100)

    as

    DECLARE @SQLCMD NVARCHAR(500)

    SET @sqlcmd = 'USE MASTER ' + 'GO ' + 'GRANT ALTER ANY LOGIN TO CPRESET'''''

    EXEC (@sqlcmd)

    Begin

    SET @sqlcmd = 'ALTER login ' + quotename(@username) + ' with password = ' + quotename(@password, '''');

    EXEC (@sqlcmd);

    end

    Tuesday, September 2, 2008 1:18 PM
  • You can use certificate signing or impersonation (EXECUTE AS), but I would say it's easier to use signing. Have a look at the example I provided here: http://blogs.msdn.com/lcris/archive/2005/06/15/sql-server-2005-procedure-signing-demo.aspx - it demoes a procedure that allows the creation of new logins - you can change it easily to allow resetting the password of logins.

     

    For impersonation, you have to execute as a principal that has the right to reset login passwords. You just impersonated the same principal (weblogin) and of course that did not help. But impersonation is a bit trickier to use in this scenario, because the permission required here is a server level permission. If you really want to use impersonation, have a look at the presentation I made at PASS: http://blogs.msdn.com/lcris/archive/2006/12/08/sql-server-2005-security-presentations-at-pass-pre-conference.aspx.

     

    Note that for sa's password to be reset, ALTER ANY LOGIN is not enough - you actually need CONTROL SERVER. There is a special case for the password reset permission check and that is: if the login has CONTROL SERVER, then you also need to have CONTROL SERVER to be able to reset its password.

     

    Hope this helps

     

    Tuesday, September 2, 2008 7:01 PM