locked
Create Login Permissions RRS feed

  • Question

  • I am able to create my own login at the instance level, but i do not have access to create a login for another user. What type of permissions do I have? How can I check that?

    Anonymous

    Wednesday, August 3, 2016 5:51 PM

All replies

  • Hello,

    When you are able to create a login for your own account then you already have permissions to create any other login.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, August 3, 2016 5:55 PM
  • I do not have permissions to create another login. It is failing.

    Anonymous


    • Edited by DevCoder Wednesday, August 3, 2016 6:21 PM
    Wednesday, August 3, 2016 6:20 PM
  • Like Olaf says, if you can create your own login, you can create others

    You can check your own permissions with this functions:

    IS_SRVROLEMEMBER: check server roles

    https://msdn.microsoft.com/en-us/library/ms176015.aspx

    IS_ROLEMEMBER: check database roles

    https://msdn.microsoft.com/en-us/library/ee677633.aspx

    IE, if you want to check if your login is in the sysadmin role:

    IF IS_SRVROLEMEMBER ('sysadmin') = 1 
       print 'Current user''s login is a member of the sysadmin role' 
    ELSE IF IS_SRVROLEMEMBER ('sysadmin') = 0 
       print 'Current user''s login is NOT a member of the sysadmin role' 
    ELSE IF IS_SRVROLEMEMBER ('sysadmin') IS NULL 
       print 'ERROR: The server role specified is not valid.'; 

    Other useful function is sys.fn_my_permissions, this function is for see what permissions you have:

    https://msdn.microsoft.com/es-es/library/ms176097.aspx

    IE, Permissions for object, database and server:

    EXECUTE AS LOGIN = 'writehereyourlogin'

    SELECT * FROM
    (
    SELECT SUSER_NAME() [LoginName],USER_NAME() [DatabaseLoginName],2 [Level],ao.[name],p.[permission_name],p.[entity_name],p.[subentity_name]
    FROM sys.all_objects ao
    CROSS APPLY sys.fn_my_permissions(QUOTENAME(ao.[name]),'OBJECT') p
    UNION ALL
    SELECT SUSER_NAME() [LoginName],USER_NAME() [DatabaseLoginName],1,d.[name],p.[permission_name],p.[entity_name],p.[subentity_name]
    FROM sys.databases d
    CROSS APPLY sys.fn_my_permissions(QUOTENAME(d.name), 'DATABASE') p
    UNION ALL
    SELECT SUSER_NAME() [LoginName],USER_NAME() [DatabaseLoginName],0,@@SERVERNAME,p.[permission_name],p.[entity_name],p.[subentity_name]
    FROM sys.fn_my_permissions(NULL, 'SERVER') p
    ) x
    ORDER BY 1,2,3
    REVERT


    Please mark the answer as solved and vote as helpful to help others

    Wednesday, August 3, 2016 6:24 PM
  • I do not have ALTER ANY LOGIN permission, neither am I part of the admin groups that have access to create logins.

    But I am able to create my login. When I try to create another login, i get permissions denied error.


    Anonymous

    Wednesday, August 3, 2016 6:32 PM
  • Which error message are you getting when it fails to create an other login?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, August 3, 2016 6:38 PM
  • I do not have ALTER ANY LOGIN permission, neither am I part of the admin groups that have access to create logins.

    But I am able to create my login. When I try to create another login, i get permissions denied error.


    Anonymous

    Your login is a SQL Server Login or a Windows Login?

    The instance has mixed authentication enabled?

    How you can create your own login? Please post here the code.


    Please mark the answer as solved and vote as helpful to help others

    Wednesday, August 3, 2016 6:43 PM
  • User does not have permission to perform this action. Error:15247.

    And this error message is expected, because I am not supposed to have permissions to create any login. But why am I able to create my own login?


    Anonymous

    Wednesday, August 3, 2016 6:44 PM
  • Login is Windows Login. Permissions are managed through AD group. Mixed mode not enabled.

    I used the GUI to create my login. Right click on Logins under Security and create new login.


    Anonymous

    Wednesday, August 3, 2016 6:50 PM
  • When you create the login, did you check any server role or mapping any database?

    Please check if you can create the login by default with no mapping or roles.

    Maybe you don´t have permissions on the role you want to asign to the login or mapping a database that you don´t have permissions?

    Test with this sample:

    CREATE LOGIN [<domainName>\<login_name>] FROM WINDOWS; 
    GO 


    Please mark the answer as solved and vote as helpful to help others

    Wednesday, August 3, 2016 7:12 PM
  • Yes I can create my login without any roles or mapping.

    Anonymous

    Wednesday, August 3, 2016 7:34 PM
  • What do mean by "You can create your own login"?? Your login is already created that is the reason you are able to login. Your login is already a part of an AD group which has access to SQL Server. You don't have permissions to assign yourself server level permissions or db level permissions. Hence, you can just create your blank login by Right Click.

    You can check what rights you have at the server level by using the below query:-

    select suser_sname(sid), sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator, bulkadmin from sys.syslogins


    Please mark the answer as helpful if i have answered your query. Thanks and Regards, Kartar Rana

    Wednesday, August 3, 2016 8:55 PM
  • What do mean by "You can create your own login"?? Your login is already created that is the reason you are able to login. Your login is already a part of an AD group which has access to SQL Server. You don't have permissions to assign yourself server level permissions or db level permissions. Hence, you can just create your blank login by Right Click.

    You can check what rights you have at the server level by using the below query:-

    select suser_sname(sid), sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator, bulkadmin from sys.syslogins


    Please mark the answer as helpful if i have answered your query. Thanks and Regards, Kartar Rana

    I can right click on security and create a new login for my Windows ID.  

    Your login is already created that is the reason you are able to login. 

    SQL Logins and AD groups are 2 different things. I am able to login because my Windows Id is part of the AD group that gives me access. But I should not be able to create a login for my windows ID. 

    I checked the results of the query you suggested...I do not have any rights that will allow me to create a login. So why am I able to create one with my windows ID


    Anonymous

    Thursday, August 4, 2016 6:18 PM
  • What do mean by "You can create your own login"?? Your login is already created that is the reason you are able to login. Your login is already a part of an AD group which has access to SQL Server. You don't have permissions to assign yourself server level permissions or db level permissions. Hence, you can just create your blank login by Right Click.

    You can check what rights you have at the server level by using the below query:-

    select suser_sname(sid), sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator, bulkadmin from sys.syslogins


    Please mark the answer as helpful if i have answered your query. Thanks and Regards, Kartar Rana

    I can right click on security and create a new login for my Windows ID.  

    Your login is already created that is the reason you are able to login. 

    SQL Logins and AD groups are 2 different things. I am able to login because my Windows Id is part of the AD group that gives me access. But I should not be able to create a login for my windows ID. 

    I checked the results of the query you suggested...I do not have any rights that will allow me to create a login. So why am I able to create one with my windows ID


    Anonymous

    What are the rights granted to the AD Group that you are part of in SQL Server?

    Please mark the answer as helpful if i have answered your query. Thanks and Regards, Kartar Rana

    Thursday, August 4, 2016 8:40 PM
  • View Any Definition

    View Any Database

    Connect SQL


    Anonymous

    Tuesday, August 16, 2016 8:09 PM
  • I looked at this a little more and I was able to repro the scenario. I created Windows group Testusers, and Windows users Nisse which I added to Testusers. Then I ran

       CREATE LOGIN [STD2016\Testusers] FROM WINDOWS

    Next I started SQLCMD with

       runas /user:nisse sqlcmd

    I found that I could now run

      CREATE LOGIN [STD2016\Nisse]

    Next I dropped Testusers as a login:

      DROP LOGIN [STD2016\Testusers]

    I then exited SQLCMD as nisse, and tried to login again - this failed. Which certainly is a good thing. :-)

    And the reason it fails is that there is no row in sys.database_permissions for STD2016\Nisse. That is, this login is not granted CONNECT SQL, and is thus not able to connect on its own.

    Exactly why you are able to add your login I cannot say, but I know that on database level there are several situations where a database user can be created for a Windows user that only has access for a group, for instance when the user becomes an owner of an object. So I guess this is analoguous. That is, there may be situations where a login can be created implicitly for you, so then you may just as well be able to do it explicitly. But there is no security issue, because you cannot use the login as such to log in.

    Tuesday, August 16, 2016 9:22 PM
  • Hopefully I can clarify this one.

    In this case SQL Server will allow the login to create an explicit login object without granting any new permissions to the individual login as access is governed by the group membership, but the login object itself can be modified to address default options for a particular individual, for example (using the same account names that you posted on your POC):

    CREATE LOGIN [STD2016\Nisse]
    go
    
    ALTER LOGIN [STD2016\Nisse] WITH DEFAULT_DATABASE = [myDb]
    go
    
    ALTER LOGIN [STD2016\Nisse] WITH DEFAULT_LANGUAGE = spanish
    go

    I hope this example helps clarify the usage of this behavior.

    -Raul Garcia

     SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Thursday, August 18, 2016 5:32 PM