1

I've inherited a very old app that runs on SQL Server 2005. I'm trying to move this app to Azure.

One thing the stored procedures in this app do is create new database logins and new users. I know that many of the functions and system procedures it uses no longer exist in SQL Server, or in Azure.

Is there some equivalent of procedures like sp_addlogin, sp_droplogin, sp_grantdbaccess, sp_revokedbaccess etc. which can be executed from a user database (the procedures are there and must run there)?

I see lots of articles that talk about "connect to master", but how do I do that from within a stored procedure on my custom database?

For example, the "create login" command has to be run in master, but my procedures are in a user database. How do I do that?

Any help would be appreciated. If what I'm trying to do isn't possible, I'd appreciate knowing that, too.

Charlieface
  • 52,284
  • 6
  • 19
  • 43
DCPetterson
  • 21
  • 1

1 Answers1

-1

I'm not sure where you get the idea that CREATE LOGIN has to be run in the master database. Seems to work just fine when run from a user database:

use master
go

drop database if exists StackOverflow;
create database StackOverflow;
go

use StackOverflow;
go

if exists(select * from sys.syslogins where name=N'StackOverflow')
    drop login [StackOverflow];

create login StackOverflow
with    password='St4ck0verflow',
    default_database = StackOverflow;

create user StackOverflow
from login StackOverflow
with    default_schema = dbo;
AlwaysLearning
  • 7,915
  • 5
  • 27
  • 35
  • 1
    You did this test in a SQL Server database. In Azure, you can't do a "use", and you can't do a "CREATE LOGIN" from anywhere but Master. – DCPetterson Jun 27 '20 at 19:30