Azure SQL how to create database user by procedure?

Question

I would like to register new user from client app, by calling webapi, where dapper will execute procedure to create new db user and store credentials in table (without password). After registration, I will change connection string with user credentials to log in.

Is it possible to dynamically create db users by executing stored procedure on Azure sql db?

I've made such query:

CREATE PROCEDURE [dbo].[AddDbUser]
@Login nvarchar(50), 
@Password nvarchar(50),
@Mail nvarchar(70),
@Phone nvarchar(9),
@SQL nvarchar(1000)
AS
BEGIN

SET @SQL = 'USE [testdb] '
    + 'CREATE LOGIN '+quotename(@Login)+' WITH PASSWORD = 
    '+quotename(@Password,'''')+'; '
    + 'CREATE USER '+@Login +'; '
    + 'EXEC [sys].[sp_addrolemember] ''db_datareader'', '+@Login+'; '
    + 'EXEC [sys].[sp_addrolemember] ''db_datawriter'', '+@Login+'; '
    + 'GRANT EXECUTE ON SCHEMA :: [dbo] TO '+@Login+'; '

    + 'INSERT INTO [dbo].[Users] (Login, Mail, Phone) '
    + 'VALUES ('+quotename(@Login,'''')+', '+quotename(@Mail,'''')+', 
    '+quotename(@Phone,'''')+');'

EXEC (@SQL)
END
GO

exec [dbo].[AddDbUser] 'dawidtest', 'password', '[email protected]', '123123123', null results in message: "User must be in the master database.", but when I add USE [master] instead of USE [testdb] I get message: "USE statement is not supported to switch between databases. Use a new connection to connect to a different database.".

Anybody could provide me some tips how to solve my problem or maybe there is some other more elegant solution?

Thank you in advance!

Peter Bons · Accepted Answer · 2017-06-10 07:08:29Z

2

According to the Microsoft you will have to do in two steps since the USE statement is not supported:

One thing to note is that SQL Azure does not allow the USE Transact-SQL statement, which means that you cannot create a single script to execute both the CREATE LOGIN and CREATE USER statements, since those statements need to be executed on different databases.

So follow the advice given in the message you are getting. Create two sql statements and execute them using different connections.

One that connects to the master db and creates the login and the other one that connects to the specific db and creates the user.

answered Jun 10, 2017 at 7:08

Peter Bons

30k4 gold badges64 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dawid Over a year ago

Thank you! :) As you said, in my controller I've added method for creating user, I'm opening first connection to master db and create Login, next I'm opening second connection to testdb and create user for Login from first connection and add this user to table Users.

Collectives™ on Stack Overflow

Azure SQL how to create database user by procedure?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related