CODEWITHSUNDEEP
t-sqlazurestored-proceduresazure-sql-database
Dawid
Dawid

Reputation: 102

Azure SQL how to create database user by procedure?

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!

Upvotes: 0

Views: 666

Answers (1)

Peter Bons
Peter Bons

Reputation: 29711

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.

Upvotes: 2

Related Questions