locked
Create user that can create other users RRS feed

  • Question

  • Hi
    How can i create user that can create other users/logins/roles and give permissions to them?
    Im using SQL server 2005.
    Thanks.
    Monday, July 27, 2009 10:01 AM

Answers

All replies

  • Here you have to give login SecurityAdmin server role



    Thanks,
    Nimit
    Monday, July 27, 2009 12:22 PM
  • Hi Nimit.
    i did.It is memeber of security admin role.
    Still server says i have no permission to perform create login.
    Monday, July 27, 2009 2:16 PM
  • are you sure you're logging in as that user during your testing? are you using Windows authentication or SQL Server security? Is it problems with creating the login or granting access to a database?
    Monday, July 27, 2009 5:30 PM
  • Im loged in using this user, 100%.
    im trying to perform
    create login hhh with PASSWORD='k'
    command
    and i receive
    User does not have permission to perform this action.
    Tuesday, July 28, 2009 7:36 AM
  • Could you tell us, the Authentication Mode, you are using for your database instance.

    Correct me, if I have misunderstood something.
     
    1. You logged in to the database instance as a sysadmin (Say LoginA).
    2. Created a new login (Say LoginB) and assign SecurityAdmin Server Role to the Login.
    3. Logged in as LoginB and tried to create a new login with the command:
       
    create login hhh with PASSWORD='k'

    Have you proceed like I mentioned above or you have follow some other steps.

    Sudeepta Kumar Ganguly. sudeeptamcsa@gmail.com
    Tuesday, July 28, 2009 11:54 AM

  • Use below quey may solve your problem

    Create a login and add it to sysadmin server role

    IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'Test')

    CREATE LOGIN Test WITH PASSWORD = '123'

     

    EXEC sp_addsrvrolemember 'Test', 'sysadmin'

    Now you can create other users/logins and roles with Test Login





    Tuesday, July 28, 2009 12:07 PM
  •  In order to grant permission to create new logins, you need to grant ALTER ANY LOGIN. Please refer to BOL for details: http://msdn.microsoft.com/en-us/library/ms189751.aspx

      Since ALTER ANY LOGIN implies more permissions than exclusively creating new logins, you may want to consider wrapping this operation in a signed/EXECUTE AS module. Laurentiu has very good examples in his blog: http://blogs.msdn.com/lcris/archive/2005/06/15/sql-server-2005-procedure-signing-demo.aspx

      I hope this information helps,

      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, July 28, 2009 4:40 PM
  • Thanks Raul
    That what ive been looking for.
    Wednesday, July 29, 2009 8:45 AM