none
How to create a user which would be able to create other users ? RRS feed

  • Question

  • Hello all,

    I need to create a user (in both SQL Server 2017 and Azure SQL) which has enough privileges to run the following commands:

    CREATE LOGIN [LOGIN] WITH PASSWORD = [PASSWORD];
    CREATE SCHEMA [SCHEMA];
    CREATE USER [USER] FOR LOGIN [SCHEMA] WITH DEFAULT_SCHEMA = [SCHEMA];
    GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION, ALTER, REFERENCES ON SCHEMA::[SCHEMA] TO [USER];
    GRANT CREATE TABLE TO [USER];
    and is also able to delete the [LOGIN]

    How do I do that ?

    Thursday, March 28, 2019 6:28 AM

Answers

  • I am running the following connected to master db:

    CREATE LOGIN user1 WITH PASSWORD = 'PAssword1234'; (went fine)

    ALTER ROLE loginmanager ADD MEMBER user1;

    and it throws: [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot add the principal 'user1', because it does not exist or you do not have permission. (15151) (SQLExecDirectW)

    How do I verify what is missing ? If I ran the first command and it execs successfully I am assuming I have enough privileges ?

    There is one thing missing:

    The user1 asctually needs to be a USER in master, not just a Login.

    This is because in Azure SQL Database there is no concept of Server Roles. Therefore "loginmanager" actually is a database role. Hence only users, not Logins van be members of those roles.

    So all you need is:

    CREATE USER user1
    	FOR LOGIN user1
    

    - in database master, to be sure

    after that, simply run the ALTER ROLE command again.

    regards

    Andreas


    Andreas Wolter (Blog | Twitter)
    Senior Program Manager SQL Server & Azure Security

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    • Marked as answer by P5000 Wednesday, May 15, 2019 2:48 PM
    Monday, May 13, 2019 5:47 PM

All replies

  • The user must be at least member of the server role securityadmin.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, March 28, 2019 6:31 AM
  • We write software which supports SQL Server but we don't use SQL Server internally so we don't have any DBAs. And I am total noob with SQL Server - can you please show me what to do step by step ?
    Thursday, March 28, 2019 6:36 AM
  • ALTER server ROLE [securityadmin] add MEMBER [mylogin]

    In addition 

    Security admin has these 3 permissions at server level:

    CONNECT SQL
    ALTER ANY LOGIN
    VIEW ANY DATABASE

    At the database level this role adds you the possibility to grant permissions to other users, but you don't have alter any user / alter any role

    CREATE A DATABASE USER

    Requires ALTER ANY USER permission on the database.

    https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user?view=sql-server-2017


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Thursday, March 28, 2019 7:56 AM
  • Hi Uri,

    thank you very much - I am a bit confused - would it be possible to show kind of copy/paste commands how to create such user ? And which steps are at the server and which at the DB level ?



    • Edited by P5000 Monday, April 8, 2019 11:31 AM
    Monday, April 8, 2019 11:29 AM
  • A server level

    ALTER server ROLE [securityadmin] add MEMBER [mylogin]

    A database level 

    GRANT ALTER ANY USER to [db user]


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, April 11, 2019 11:46 AM
  • Thanks Uri !

    I am assuming [mylogin] and [db user] should be the same name ?

    Thursday, April 11, 2019 12:22 PM
  • Correct.... just map the user to the login

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, April 11, 2019 12:34 PM
  • @uri

    one more question: I am connected to SQL Server (it is Azure SQL) and I have two available DBs:

    • master
    • my-testing-db

    Should I run the "ALTER server ROLE" against "master" and "GRANT ALTER" against "my-testing-db" ?


    • Edited by P5000 Tuesday, April 23, 2019 11:12 AM
    Tuesday, April 23, 2019 11:09 AM
  • I ran the "ALTER server ROLE" against Azure SQL master and got this:

    SQL Error [40517] [S0001]: Keyword or statement option 'role' is not supported in this version of SQL Server.

    What is wrong ?

    Tuesday, April 23, 2019 11:14 AM
  • I ran the "ALTER server ROLE" against Azure SQL master and got this:

    SQL Error [40517] [S0001]: Keyword or statement option 'role' is not supported in this version of SQL Server.

    What is wrong ?

    Exactly what the message says. The command is not supported in Azure SQL DB which has a different surface area than the box product.

    I don't really know how you go around to create a user with permission to create logins in SQL DB. I tried "GRANT ALTER ANY LOGIN TO pelle" but that did not go any better. Maybe you need to do it through the portal.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, April 23, 2019 9:37 PM
  • As it says pretty clear "Is not supported in this version of SQL Server"

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, April 24, 2019 4:56 AM
  • 1) I ran the "ALTER server ROLE" against Azure SQL master and got this:

    SQL Error [40517] [S0001]: Keyword or statement option 'role' is not supported in this version of SQL Server.

    What is wrong ?

    Exactly what the message says. The command is not supported in Azure SQL DB which has a different surface area than the box product.

    2) I don't really know how you go around to create a user with permission to create logins in SQL DB. I tried "GRANT ALTER ANY LOGIN TO pelle" but that did not go any better. Maybe you need to do it through the portal.

    ...

    Azure SQL Database does not have server level roles.

    concerning permissions to create Logins:

    Only the server-level principal login (created by the provisioning process) or members of the loginmanager database role in the master database can create new logins. Source: CREATE LOGIN


    Andreas Wolter (Blog | Twitter)
    Senior Program Manager SQL Server & Azure Security

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    Wednesday, April 24, 2019 7:40 AM
  • Thanks Andreas!

    So that P5000 gets this right. The statements to run are:

    CREATE USER pelle
    ALTER ROLE loginmanager ADD MEMBER pelle

    Note that you should run them connected to the master database.

    Then in the user database, you run
    CREATE SCHEMA [SCHEMA];
    go
    CREATE USER [USER] FOR LOGIN [SCHEMA] WITH DEFAULT_SCHEMA = [SCHEMA];
    GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION, ALTER, REFERENCES ON SCHEMA::[SCHEMA] TO [USER];
    GRANT CREATE TABLE TO [USER];

    Note that these two sets of commands must be run on separate connections, as you cannot switch between connections in Azure SQL DB.

    And note that what is said here applies to Azure SQL only. On SQL 2017 you can run all the same script and you use the command ALTER SERVER ROLE as before.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, April 24, 2019 9:13 PM
  • So actually there is no any way to keep it consistent across different SQL databases ?
    Friday, May 3, 2019 2:19 PM
  • So actually there is no any way to keep it consistent across different SQL databases ?

    Not sure what you mean, but the commands for SQL 2017 and Azure SQL DB are different.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, May 3, 2019 9:22 PM
  • I am running the following connected to master db:

    CREATE LOGIN user1 WITH PASSWORD = 'PAssword1234'; (went fine)

    ALTER ROLE loginmanager ADD MEMBER user1;

    and it throws: [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot add the principal 'user1', because it does not exist or you do not have permission. (15151) (SQLExecDirectW)

    How do I verify what is missing ? If I ran the first command and it execs successfully I am assuming I have enough privileges ?

    • Edited by P5000 Monday, May 13, 2019 1:07 PM
    Monday, May 13, 2019 12:30 PM
  • I am running the following connected to master db:

    CREATE LOGIN user1 WITH PASSWORD = 'PAssword1234'; (went fine)

    ALTER ROLE loginmanager ADD MEMBER user1;

    and it throws: [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot add the principal 'user1', because it does not exist or you do not have permission. (15151) (SQLExecDirectW)

    How do I verify what is missing ? If I ran the first command and it execs successfully I am assuming I have enough privileges ?

    There is one thing missing:

    The user1 asctually needs to be a USER in master, not just a Login.

    This is because in Azure SQL Database there is no concept of Server Roles. Therefore "loginmanager" actually is a database role. Hence only users, not Logins van be members of those roles.

    So all you need is:

    CREATE USER user1
    	FOR LOGIN user1
    

    - in database master, to be sure

    after that, simply run the ALTER ROLE command again.

    regards

    Andreas


    Andreas Wolter (Blog | Twitter)
    Senior Program Manager SQL Server & Azure Security

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    • Marked as answer by P5000 Wednesday, May 15, 2019 2:48 PM
    Monday, May 13, 2019 5:47 PM
  • @andreas,

    does it have to be like this:

    CREATE LOGIN user1 WITH PASSWORD = 'PAssword1234';
    CREATE USER user1 for LOGIN user1;
    ALTER ROLE loginmanager ADD MEMBER user1;
    

    Wednesday, May 15, 2019 1:32 PM
  • by the way - this account is for testing purposes - how do I then drop this account (and all of the data created in this account - basically "shred" it ...) ?
    Wednesday, May 15, 2019 1:36 PM
  • @andreas,

    does it have to be like this:

    CREATE LOGIN user1 WITH PASSWORD = 'PAssword1234';
    CREATE USER user1 for LOGIN user1;
    ALTER ROLE loginmanager ADD MEMBER user1;

    that is correct. Only the User-part was missing in your attempt


    Andreas Wolter (Blog | Twitter)
    Senior Program Manager SQL Server & Azure Security

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    Wednesday, May 15, 2019 2:28 PM
  • by the way - this account is for testing purposes - how do I then drop this account (and all of the data created in this account - basically "shred" it ...) ?

    You can get rid of the user and login with the DROP USER respectively DROP LOGIN statements

    - the assignment to the role will be removed automatically

    But if the user created objects and is the actual owner of them (and not dbo as mostly), then you need to transfer ownership fist. The drop would then be prevented.

    If you need to get more details on this I suggest opening another thread so the question & solution ca be found by others as well


    Andreas Wolter (Blog | Twitter)
    Senior Program Manager SQL Server & Azure Security

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    Wednesday, May 15, 2019 2:29 PM
  • Will open another thread then.

    Thanks a lot !

    Wednesday, May 15, 2019 2:47 PM