SqlServer 认证之包含数据库

sys.sp_configure N‘contained database authentication‘, N‘1‘ GO RECONFIGURE WITH OVERRIDE GO

 

　

   新建或修改数据库的包含类型为部份。

   

Create Database DBName Containment=partial

Alter DataBase DBName set Containment=partial

当修改数据库时，还需要将映射到 SQL Server 登录名的数据库用户转换为具有密码的包含数据库用户。

使用系统存储过程：

sp_migrate_user_to_contained [ @username = ] N‘user‘ ,   
    [ @rename = ] { N‘copy_login_name‘ | N‘keep_name‘ } ,   
    [ @disablelogin = ] { N‘disable_login‘ | N‘do_not_disable_login‘ }  

参考代码：

DECLARE @username sysname ;  
DECLARE user_cursor CURSOR  
    FOR   
        SELECT dp.name   
        FROM sys.database_principals AS dp  
        JOIN sys.server_principals AS sp   
        ON dp.sid = sp.sid  
        WHERE dp.authentication_type = 1 AND sp.is_disabled = 0;  
OPEN user_cursor  
FETCH NEXT FROM user_cursor INTO @username  
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
        EXECUTE sp_migrate_user_to_contained   
        @username = @username,  
        @rename = N‘keep_name‘,  
        @disablelogin = N‘disable_login‘;  
    FETCH NEXT FROM user_cursor INTO @username  
    END  
CLOSE user_cursor ;  
DEALLOCATE user_cursor ;  

参考网址

https://msdn.microsoft.com/zh-cn/library/ff929275.aspx

 连接时指定数据库名。即连接串中，需要指定initial catalog 或Database

 

  

SqlServer 认证之包含数据库

标签：