I have 2 logins :
- LoginA: role dbcreator + CONNECT ANY DATABASE
- LoginB: role sysadmin
I have 2 databases :
- dbA: everyone has all rights on it
- dbB: only LoginB has rights on it, LoginA is only the owner of that database but no more
I'm wondering if it is possible to create a user (linked to LoginA) in dbB from a stored procedure of dbA.
Considering this stored procedure has to be executed by LoginA.
Maybe some kind of "EXECUTE AS" mechanism could help ?
Also I have issues about how to use a "USE [dbB]" in a stored procedure.
The solution can have some rights modification,
but these can't give securityadmin nor sysadmin roles.
Here is what is done for now but clearly there is a lot of debug and functionalities to go, but the spirit of what I wanna do is in here :
CREATE PROCEDURE [dbB_UserAfterRestore] AS BEGIN SET NOCOUNT ON; USE [dbB] CREATE USER [HOSTNAME\LoginA] FOR LOGIN [HOSTNAME\LoginA] ALTER ROLE [db_datareader] ADD MEMBER [HOSTNAME\LoginA] ALTER ROLE [db_datawriter] ADD MEMBER [HOSTNAME\LoginA] END GO