Here is the setup: on MSSQL-Server-2022, we have a contained availability group with 4 replicas: 3 synchronous, 1 asynchronous, and a listener. We created a logon trigger to limit access to a specific login from only one IP address. The trigger works well on the listener and primary replica (got trigger error). But when I try to connect to secondary replicas (synchronous and asynchronous), it does not work at all. (I can connect to asynchronous replica with that login from any IP) Do you have any ideas why this is happening? (The login will use the asynchronous replica to read) Here is the code:

CREATE TRIGGER [trg_LimitLogin]
ON ALL SERVER
WITH EXECUTE AS 'login with enough permission on server'
FOR LOGON
AS
BEGIN
    DECLARE @LoginName NVARCHAR(255)
    DECLARE @ClientIP NVARCHAR(255)
    -- Get the login name of the user attempting to connect
    SET @LoginName = ORIGINAL_LOGIN()
    -- Get the IP address of the client
    SET @ClientIP = (SELECT top 1 client_net_address
                     FROM sys.dm_exec_connections
                     WHERE session_id = @@SPID)
    -- Restrict the login for the user 'usr_tutunchian'
    IF  @LoginName NOT LIKE '%$'
	   AND (@LoginName = 'That login'
AND @ClientIP != '1.1.1.1 (for posting code)')
BEGIN
        ROLLBACK
    END
	ELSE
	BEGIN
	 RETURN
	 END
END

I've specifically disabled a login on the database server. This is an Azure hosted SQL-as-a-Service database. alter login [********@hotmail.com] disable Here's what the login looks like on the server. As you can see, the red X on the icon indicates that it is in fact disabled. Yet, I'm able to log into the server and access the database for which this user is defined. How is this possible? What then does it mean to disable a login on the database server if it doesn't prevent that account from logging in to the database server? I've tested this behavior with two different accounts, from two different authentication domains. They both exhibit the same behavior. What I am trying to do is have the user defined in the database so that the user can access the database on the read-replica server, yet disable (or otherwise remove) the corresponding login from the primary server so that this user will not be able to access the the primary DB on the primary server. However I am finding that, with the Microsoft Entra (Azure AD) account defined as a user on a particular database, it makes absolutely no difference if there is a corresponding login defined on the server, or if there is no login defined on the server, or if there is a login defined on the server but it is specifically disabled. Is this a bug in how logins for external user accounts are handled? My understanding of the login / user relationship is that first there needs to be a login in good standing on the server and that this is the first thing which is checked when logging into a database server. Is this not correct? **New information:** I am seeing this behavior even with SQL Server users. I can simply create a SQL Server user within a database and then login to access that database WITHOUT creating a server login. This behavior is what we would expect from a contained database. (https://learn.microsoft.com/en-us/sql/relational-databases/databases/contained-databases?view=sql-server-ver16) Yet the database is not contained! So the database is not contained, but connections exhibit all of the behavior we'd expect from containment. Is there partial containment going on here? How would I determine if this is the case? And if this is the case, how would I reconfigure the database so that connections follow the traditional Server login -> Database user model?

We have a SQL 2022 Always On Availabilty Group with Contained databases; a new feature in this version of SQL. The master and msdb databases are now also in the Avalability Group. We like this setup, for example we only have to maintain the logins that exists in the master database of the AG and not on both the SQL servers. Because of this setup, we can make SQL backups on the primairy, secondary server or within Contained the availability group. We tried different options and we could always restore the database and transaction logs if we made a full backup in the Contained Availability group mixed with transaction log backups from the SQL servers. I searched the web but couldn't find any best-practices and known issues that we can encounter if we make the SQL backup in the Contained Availability Group. Has anyone more information or experience about this subject? Regards, Jeroen Bleeker

Using SQL Server **2022** Standard Edition and contained database users (which means no corresponding SQL login at the instance level). I would like to have the contained database users be able to use a linked server, and specifically with the contained user mapped to use a specific username/password on the remote database. When I try to add the mapping using SSMS, I get the error > An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) 'username' is not a valid login or you do not have permission. (Microsoft SQL Server, Error: 15007) My interpretation is that this linked server user mapping feature only works with instance-level logins and is not possible when the local login is a contained database user. However, I cannot find any explicit Microsoft documentation of this limitation. Can anyone confirm my understanding, or am I missing something that would make this possible?

I created a contained availability group but I don't know why the jobs are not synchronized between the nodes. What could be the problem? Shouldn't it do it automatically? ---- In response to the answer I have performed the following: I'm connected on the server that we are now using as Primary (xxxxx) and I've created a test job there. The sync for the dbs is working perfectly, but it is not working for logins and jobs.

for some reason, i am unable to connect with any user that I create. their account status is also unlocked. I want to mention one thing; the pdbs are clone of the main pdb. (as shown by their name below). Only adding this for additional info. //Edit: i was testing, and I fount out the common user is able to authenticate/connect. Don't know why local user is unable to connect/ [oracle@oracle pdb1]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 5 19:44:16 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO 4 PDB2 READ WRITE NO 5 PDB3 READ WRITE NO SQL> ALTER SESSION SET CONTAINER = PDB1; Session altered. SQL> create user test1 identified by test1; User created. SQL> GRANT CREATE SESSION TO test1; Grant succeeded. SQL> connect test1/test1; ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. -------------------------------------------------------- SQL> ALTER SESSION SET CONTAINER = PDB1; Session altered. SQL> select name from v$database; NAME --------- ORCLDB SQL> ALTER SESSION SET CONTAINER = PDB2; Session altered. SQL> select name from v$database; NAME --------- ORCLDB // New Edit - Listener Status -

SQL> !lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 06-SEP-2023 17:14:56

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                06-SEP-2023 17:14:18
Uptime                    0 days 0 hr. 0 min. 38 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.0.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/19.0.0/db_1/admin/orcldb/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "0053f52b9a9509e9e06562aa88e317a6" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "005414da85570a0ae06562aa88e317a6" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "fa038b2801752bdae05562aa88e317a6" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcldb" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb3" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

# Scenario and Initial Question Imagine you sit down at the keyboard of an arbitrary SQL Server host (traditional SQL Server, on a machine; not Azure) that you've never used before. You have administrative credentials, and need to assess the security configuration of one of its databases. - **What query can you run to determine if a user in that database is a contained database user, or a traditional login-based user?** I'm specifically interested in Windows-authenticated users for my real-world project, but I'm generally interested in how this works. So, please elaborate on SQL- vs. Windows-authenticated, if it matters. # Tests I've tried various methods of creating contained and login-based users (e.g. SQL, SSMS UI) and have explored various system views (e.g. sys.database_principals, sys.sysusers) and SSMS property pages, but no matter what I do, I can't figure out how to determine after-the-fact whether the user was created as contained or login-based. One potential method would be to see if a login exists with the same SID as the user. If the user is functioning normally (e.g. able to connect) and there is no corresponding login, then the user must be contained. But, is the converse true - if a login and a user with the same SID exist, does that imply that the user is based on that login? Let's look at some evidence. Firstly, the doc states (*Remarks* section at bottom): > If there is a login in master database with the name name1 and you create a contained database user named name1, when a database name is provided in the connection string, the context of the database user will be picked over login context when connecting to the database. That is, contained database user will take precedence over logins with the same name. The doc doesn't specify whether it applies to SQL-authentication, Windows-authentication, or both. If I'm reading the text correctly, though, we know that in at least some circumstances contained users and logins with the same *name* can coexist. But, names don't make them the same; for example, we can have a login-based user with a different name than its underlying login. I believe that it's the SID that make them the "same". Let's try this then. Start by creating a contained database user:

CREATE USER [CITRA\test];

We can connect to the database as this user, so we know it works. When we look for the corresponding login, as expected, there is none:

SELECT
	dp.name [user_name]
	,sp.name [login_name]
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON
	dp.sid = sp.sid
WHERE
	dp.name = 'CITRA\test'
;

Now, let's create a login based on the same Windows user:

CREATE LOGIN [CITRA\test] FROM WINDOWS;

When we try again to match the user with a login, we find a pair because they have the same SID: What just happened here - do we have two separate things, a login and contained user, with the same names and SIDs, or did we actually *convert* a contained user to a login-based user by virtue of creating the login *after the fact*? Surprisingly (to me) I think it's the latter. If we disable the login, we can no longer connect - even though our user was originally created (and functioning normally) as a contained database user:

ALTER LOGIN [CITRA\test] DISABLE;

Observe that we are specifying the target database name in the connection, as described in the doc excerpt above. To reiterate: > when a database name is provided in the connection string, the context of the database user will be picked over login That's not the way SQL Server is behaving in our test, though. The only thing I can conclude (perhaps from lack of imagination) is that creating the login turned our contained user into a login-based user. # More Fundamental Question The test above begs a more fundamental question than the original: - **What actually *makes* a user contained or login-based?** From what I see in the test above, it's literally the presence or absence of a matching (by SID) login. Furthermore, it appears that we can convert an existing user between contained and login-based simply by creating or dropping the login. At least for a Windows-authenticated user. # Thinking Out Loud If this is correct, I'm guessing that a "contained database user" isn't even really a thing - meaning, there's no "IsContained" flag somewhere that tells us whether a user is contained. Rather, I speculate that it's the behavior of the modern SQL Server *software* that manifests the concept of a contained user by virtue of its behavior at connection time. Specifically, new SQL Server will authenticate a user with a "missing" login - i.e. a contained database user - whereas old versions of the software would not. Maybe I just haven't found it yet, but there doesn't seem to be any metadata that tells SQL Server explicitly that a user is contained; I think it needs to infer it from other parts of the environment (e.g. sys.databases.containment, presence/absence of login). I'm just guessing about this, though, and I'm only considering Windows-authentication (for SQL-authenticated users, passwords would still need to be stored somewhere). If you can clarify the ideas in this last section, please do, but don't let it distract from the more practical questions in the prior sections. Thank you for your help.

# Context We've recently changed our SQL Server security governance to favor users that are: - Contained database users, and; - Based on Windows domain accounts It's working great for new projects. I need to migrate several legacy databases, however, which have two other user types: - Users based on Windows authenticated logins - Users based on SQL Server authenticated logins For practical purposes, I can just recreate the first type; they're for end users who don't own any objects, and whose permissions are all assigned by roles. They're easy to script. # Goal My challenge is migrating the second type. These "app admin" users own all of the objects that the end users read/write. I know I can reassign ownership using ALTER AUTHORIZATION, but they also have a gnarly mess of delicate permissions, partially granted by us and partially by the respective app vendors. Reassigning those permissions to new users is possible, but would require a nontrivial investment in migration scripting, and would invite more risk. Ideally, I'd like to convert the existing **database users based on SQL Server authenticated logins** directly to **contained database users based on Windows domain accounts**. But I can't figure out how to do it. Or if it's even possible. # Attempts So far I've tried: - ALTER USER [based_on_ss_auth_login] WITH LOGIN = [domain\account] I know the WITH LOGIN syntax seems contradictory to my goal, but take my word that it's the appropriate, albeit confusing, syntax for creating a contained user based on a Windows account (see T-SQL doc for CREATE USER and reference section titled *Users based on Windows principals without logins in master*). This attempt yields the error:

Msg 33017, Level 16, State 1, Line 6
Cannot remap a user of one type to a login of a different type. For example, a SQL user must be mapped to a SQL login; it cannot be remapped to a Windows login.

I also tried: - EXECUTE sp_migrate_user_to_contained - Followed by the ALTER USER statement above The first step worked - my user was still SQL Server authenticated, but now at the database level (contained) instead of the instance level (login). Unfortunately, trying to further convert it to use Windows authentication raised an error:

Msg 33016, Level 16, State 1, Line 6
The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.

My question is: - **QUESTION:** Is there any way to convert a user that is based on a SQL Server authenticated login, to a contained database user that is based on a Windows account? Again, I'm looking to directly convert the existing user so I don't need to reassign ownership/permissions/etc. Thank you for any help you can provide.

I have a peculiar issue. I have some partial contained DBs that are being used by a CMS system and whenever I go to take a backup of these DBs they do not compress. I do have the server property database setting for Compress Backup checked. Has anyone else seen this before? I am not able to find much online about this unfortunately. > SQL Server Version: Microsoft SQL Server 2017 (RTM-CU24) (KB5001228) - > 14.0.3391.2 (X64)

The Microsoft documentation states the following about the supportability of SQL Server Containers running on container orchestrators: > Microsoft supports deploying and managing SQL Server containers by > using OpenShift and Kubernetes. > > Starting from SQL Server 2019, you can deploy the SQL Server Big Data > Cluster on Kubernetes. Review the supported Kubernetes platforms in > the SQL Server 2019 Big Data Clusters release notes under the > Supportability section. Does it mean that all kinds of supported SQL Server containers are supported on OpenShift and Kubernetes or is this statement talking only about SQL Server 2019 Big Data Clusters ones as supported?

I googled for the last few days but I got a bit confused and I do not know how to proceed further. Also, I'm not a DBA and I was just faced with this situation for the first time. Using sqlpackage.exe I'm exporting a bacpac from a PRD DB and import it on another server to refresh the lower environments. The original database is contained. After I'm importing the new database, the contained database users and the AAD external ones lose access to the new database. I'm fixing the contained database user by updating its password with

ALTER USER [RandomUser] WITH PASSWORD='randomPassword'

but I am not sure what to do with the AAD users. They have an external user created for them (the aad users are placed inside a group). So far my option is to drop the said user and recreate it

CREATE USER [sthRandom] FROM EXTERNAL PROVIDER

but in order to do this, you have to be connected with an AAD user

Principal 'sthRandom' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.

I have to automate this whole process so I could use a service principal like [this](https://techcommunity.microsoft.com/t5/azure-sql/azure-ad-service-principal-authentication-to-sql-db-code-sample/ba-p/481467) . I'm creating the service principal inside all the databases, but after refresh the user won't exist anymore, or I create it in the PRD database too, but I can not use it to login anymore. What other options do I have? Thanks

What's the difference between these two commands?

CREATE USER abc FROM EXTERNAL PROVIDER
CREATE USER abc FOR  EXTERNAL PROVIDER

I'm stuck in a situation where I need to create a new user for a partially-contained database (SQL Server 2016). The password is short, so I get an error: >Password validation failed. The password does not meet Windows policy requirements because it is too short. When creating a login at the instance level, there is an option to untick 'Enforce password policy', 'Enforce password expiration', and 'User must change password at next login'. There is no such option when creating a user for a partially-contained DB. Is there a way to get around this?

> SQL Server version (output from select @@VERSION): Microsoft SQL Server 2017 (RTM-CU19) (KB4535007) - 14.0.3281.6 (X64) (Build 14393: ) Installed sp_Blitz from Brent Ozar, stored procedure creates successfully. Version info from the code: SELECT @Version = '7.93', @VersionDate = '20200217'; The instance has the 4 system DBs with collation SQL_Latin1_General_CP1_CI_AS and 2 user databases with collation SQL_Latin1_General_CP1_CS_AS When I try to run sp_Blitz I get this: > Msg 468, Level 16, State 9, Procedure sp_Blitz, Line 896 [Batch Start Line 0] Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. Appreciate any assistance in troubleshooting (I'm mainly an Oracle guy who's been handed a SQL Server for review and remembered this as a thing that might be a good start for obvious things to start with).

What are the disadvantages of contained database in Microsoft SQL Server 2017? I want to introduce contained database in my organization. I would like to know what limitations exist. Reasons why I should not implement it would be useful too.

I've recently started working with contained databases and I do not understand if/how contained database users have passwords that expire. MSDN documentation says they do - With a SQL Server login, there's the option box to Enforce Password Expiration but this does not exist (as far as I can tell) for contained database users. These contained database accounts have been around for a while and our domain policy is to reset passwords every 6 months, but we have never had to change these passwords, and the applications are connecting just fine using the same password for the past couple of years. My first question is how do I know that "Password Expiration" is being enforced for contained database users? With SQL Logins there's a is_expiration_checked column in sys.sql_logins. With traditional SQL logins I can use following function to get the time a SQL Server login was changed

LOGINPROPERTY('login','PasswordLastSetTime')

Is there something similar for contained database users?

My team and I are in the process of designing a new application using the microservice architecture approach. Each application is going to to be responsible for releasing changes to its own database using a tool not unlike flyway (https://flywaydb.org) To ensure that this approach works we are looking to use contained databases to help ensure that were not dependent on anything thats external to its database and to help ensure that the app is the only thing making changes to the db structure etc. Other teams in the company have, in the past, been dependent on replication to pull data from our dbs so that they can report on it alongside data from other systems. The requirement for the access to this data in their system is still valid so I am looking at other means of getting the data copied from out system into theirs. They are only looking for information in intervals (such as hourly) rather than a live copy. Is there any way of us being able to provide this in a sensible way, whilst still using contained databases.

While granting access to Azure SQL databases I have been creating Contained Users directly in the SQL database like so:- CREATE USER [email@domain.com] FROM EXTERNAL PROVIDER; As this has been working fine, and users need to add the database name to the connection, I thought azure would know this is now a contained database. However, upon checking, I've found the database is not contained select containment, containment_desc from sys.databases containment / containment_desc 0 / NONE 0 / NONE Should I now convert the database to be contained? Are there any implications for leaving the database in this state? Should I revert back to creating the user at both the server and DB level?

When changing a database to partially contained I am getting the following error: > Cannot resolve the collation conflict between "Latin1_General_CI_AS" > and "Latin1_General_100_CI_AS_KS_WS_SC" in the EXCEPT operation. > > Errors were encountered in the procedure 'RSExecRole.DeleteExtensionModuleDDL' > during compilation of the > object. Either the containment option of the database 'VeeamOne' was > changed, or this object was present in model db and the user tried to > create a new contained database. > ALTER DATABASE statement failed. The containment option of the database 'VeeamOne' could not be altered because compilation errors > were encountered during validation of SQL modules. See previous > errors. > ALTER DATABASE statement failed. (.Net SqlClient Data Provider) The object this is reporting on I think is from SSRS. However the DB I am changing the collation on is a completely separate application. Does anyone have any suggestions on how to resolve this? ========================================================================= OK this is the code for the proc, not sure what about it causes it to no be able to be contained though USE [VeeamOne] GO /****** Object: StoredProcedure [reporter].[DeleteExtensionModuleDDL] Script Date: 02/12/2015 12:06:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [reporter].[DeleteExtensionModuleDDL] @EMID int AS BEGIN SET NOCOUNT ON; declare @Debug bit; set @Debug = 0; declare @Emulate bit; set @Emulate = 0; declare @reportPackDestructorFunctionName nvarchar(max) exec @reportPackDestructorFunctionName = [reporter].GenerateExtensionModuleDestructorName @EMID if exists(select * from sys.objects where (object_id = OBJECT_ID(@reportPackDestructorFunctionName) and type in (N'P', N'PC'))) begin exec @reportPackDestructorFunctionName declare @objectsToDelete as table (Name nvarchar(2048), Type nvarchar(2048)) insert @objectsToDelete exec @reportPackDestructorFunctionName if @Debug = 1 begin select * from @objectsToDelete end declare @TablesToDelete as table(ObjectID int, Name varchar(max)) declare @FunctionsToDelete as Table(Name nvarchar(max)) declare @StoredProceduresToDelete as Table(Name nvarchar(max)) declare @AssembliesToDelete as Table(Name nvarchar(max)) declare @ViewsToDelete as Table(Name nvarchar(max)) insert into @TablesToDelete select object_id(Name), Name from @objectsToDelete where Type = 'Table' insert into @FunctionsToDelete select Name from @objectsToDelete where Type = 'Function' insert into @StoredProceduresToDelete select Name from @objectsToDelete where Type = 'Procedure' union select @reportPackDestructorFunctionName insert into @AssembliesToDelete select Name from @objectsToDelete where Type = 'Assembly' insert into @ViewsToDelete select Name from @objectsToDelete where Type = 'View' declare @DependencyTree as Table(ForeignKeyObjectID int, ForeignKeyObjectName nvarchar(max), ParentTableID int, ParentTableName nvarchar(max), ChildTableID int, ChildTableName nvarchar(max), Generation int) declare @Generation int; set @Generation = 0; insert into @DependencyTree select distinct(fk.object_id) as ForeignKeyObjectID, fk.name as ForeignKeyObjectName, fk.referenced_object_id as ParentTableID, parent.name as ParentTableName, fk.parent_object_id as ChildTableID, child.name as ChildTableName, @Generation from sys.foreign_keys as fk inner join sys.objects as parent on fk.referenced_object_id = parent.object_id inner join sys.objects as child on fk.parent_object_id = child.object_id where fk.referenced_object_id in (select ObjectID from @TablesToDelete) while @@ROWCOUNT > 0 begin set @Generation = @Generation + 1 insert into @DependencyTree select fk.object_id as ForeignKeyObjectID, fk.name as ForeignKeyObjectName, fk.referenced_object_id as ParentTableID, parent.name as ParentTableName, fk.parent_object_id as ChildTableID, child.name as ChildTableName, @Generation from @DependencyTree dt inner join sys.foreign_keys as fk on fk.referenced_object_id = dt.ChildTableID inner join sys.objects as parent on fk.referenced_object_id = parent.object_id inner join sys.objects as child on fk.parent_object_id = child.object_id except select ForeignKeyObjectID, ForeignKeyObjectName, ParentTableID, ParentTableName, ChildTableID, ChildTableName, @Generation from @DependencyTree end declare @clearScript as table(ID int primary key identity (0,1), ScriptText nvarchar(max)) insert into @clearScript select 'alter table [reporter].[' + ChildTableName + '] drop constraint [' + ForeignKeyObjectName + ']' from @DependencyTree where ParentTableName in (select Name from @TablesToDelete) insert into @clearScript select 'drop table [reporter].[' + Name + ']' from @TablesToDelete insert into @clearScript select 'drop function [reporter].[' + Name + ']' from @FunctionsToDelete insert into @clearScript select 'drop procedure [reporter].[' + Name + ']' from @StoredProceduresToDelete insert into @clearScript select 'drop assembly [reporter].[' + Name + ']' from @AssembliesToDelete insert into @clearScript select 'drop view [reporter].[' + Name + ']' from @ViewsToDelete if @Debug = 1 begin select * from @clearScript end declare @str nvarchar(max) declare @ID int; set @ID = 0; declare @MaxID int select @MaxID = MAX(ID) from @clearScript print '' while @ID <= @MaxID begin select @str = ScriptText from @clearScript where ID = @ID if @Emulate = 1 print(@str) else exec sp_executesql @statement = @str set @ID = @ID + 1 end end END

I'm migrating databases from SQL Server 2012 to Server Server 2017, and switching SOME of them to partially contained. I've done SQL Server backups and restores to actually move the databases, and switched them to partially contained. When I then recreate the Windows-based logins at the server/instance level, they are automagically reconnected to the database users - in both the partially contained and non-contained databases. For the users in the partially contained databases, I would like to break the link between the instance-level Windows login and database user, so that the authentication happens at the database level (but it's still a Windows login). I don't want to drop the instance-level Windows login because it is needed for other databases. sp_migrate_user_to_contained is only for SQL Server logins, not Windows-based logins. I could try one of the scripts out there for cloning a user and basically drop the existing user and recreate it, but I'm hoping not to. Is what I'm trying to do not really supported, or maybe just pointless?