This is happening because the principal in the AS clause needs to have the permission with the "GRANT OPTION". Running this will allow you to run your original code:
GRANT ALTER ON ROLE::[AdaptTemplate] TO [AdminSapr] WITH GRANT OPTION
My reading of Books Online made me think that what you're doing is right since AdminSapr is in the db_owner role but in testing I had to do something like the above to get this to work.
By selecting SQL user without login within SSMS' create user dialog, this is the T-SQL that is generated (with no other options besides the user name supplied):
USE [AdventureWorks2012]
GO
CREATE USER [User1] WITHOUT LOGIN
GO
If you were to create a login and attempt to alter the user with the new login, like the following attempt:
create login TestLogin1
with password = 'p@$$w0rd';
go
use AdventureWorks2012;
go
alter user User1
with login = TestLogin1;
go
You will get the following error:
Msg 33016, Level 16, State 1, Line 8
The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.
Because of that, I don't think you can link up a login to this type of user without recreating it. It should be relatively painless, as you could just script out the user, but change the parameters from WITHOUT LOGIN
to whatever login you want it to be mapped to.
EDIT: As pointed out by Aaron below, ensure that you're taking note of permissions and role memberships before making any changes. You'll want to ensure that you can get back to the permissions state you currently are in, if that's the desired behavior.
Best Answer
If at all possible, this should be fixed from the deployment side of things and not from a questionable modification of the target database side of things (referring to the conversation in the comments on the Question related to using the deprecated
sp_addalias
).Depending on what the exact problem is, there are various configuration options of DacPac deployment that can be used to get around such conflicts. These options can either be specified on the command line if running SqlPackage, or can be placed into a DAC Publish Profile (i.e. an XML config file).
The options of interest here are listed in the Publish Parameters, Properties, and SQLCMD Variables section of the
SqlPackage
page.To start with, do you have any of the following set to
True
:DropObjectsNotInSource
(default: False)DropRoleMembersNotInSource
(default: False)IgnoreLoginSids
(default: True)If you have the
DropObjectsNotInSource
property set toTrue
, then try adding:DoNotDropObjectType=Users
ExcludeObjectType=Users
IgnoreRoleMembership=True
IgnoreUserSettingsObjects=True
??If needing to specify the
ExcludeObjectType
property, that would look as follows, depending on where it is specified:/p:ExcludeObjectType=Users
In a DAC Publish Profile: