【C#】SQL Server数据库表/视图/存储过程的所有者并非dbo以及修复账号孤立问题│点晴OA管理信息系统查询连接错误对象名无效MicrosoftOLEDBProviderfor错误80040e37tempasp修改

admin

2021年6月5日 10:4 本文热度 4840

：SQL Server数据库表/视图/存储过程的所有者并非dbo或存在账号孤立问题时，错误表现如下：

//获取数据库连接字符串

ConnectionString = "data source=(local);initial catalog=clicksun;user id=sa;pwd=clicksun$193$631;Connection Timeout=30";

//创建连接对象

SqlConn = new SqlConnection(ConnectionString);

//打开数据库，修复账号clicksun孤立问题

try

{

SqlConn.Open();

string ComText = "sp_change_users_login 'Auto_Fix', 'clicksun', NULL, 'clicksun2010'";

SqlCommand Comm = new SqlCommand(ComText, SqlConn);

Comm.ExecuteNonQuery();

SqlConn.Close();

}

catch (Exception) { }

//修复数据库表、视图、存储过程所有者不是dbo问题

try

{

SqlConn.Open();

string sqlMessage = "select (user_name(uid)+'.'+name) as uname from sysobjects where status>=0 and xtype in('U','V','P') and user_name(uid)<>'dbo'";

SqlCommand sCmd = new SqlCommand(sqlMessage, SqlConn);

SqlDataReader sdr = sCmd.ExecuteReader();

string updateDBOwner = "";

while (sdr.Read()) { updateDBOwner += "exec sp_changeobjectowner '" + sdr.GetValue(0).ToString() + "','dbo';"; }

sdr.Close();

SqlConn.Close();

if (updateDBOwner != "")

{

SqlConn.Open();

SqlCommand Comm = new SqlCommand(updateDBOwner, SqlConn);

Comm.ExecuteNonQuery();

SqlConn.Close();

}

catch (Exception) { }

该文章在 2021/6/5 10:42:10 编辑过