VC在SQL Server7中动态增加删除用户

本文主要通过调用SQL Server 的系统存储过程和系统表 在程序中动态增加和删除用户 一 首先用系统管理员帐号登录系统 程序省略 请看源程序 　二 进入用户管理界面 读取数据库 从master sysdatabse中读取所有数据库 void CUser::AddDataBase(){_RecordsetPtr rs_bstr_t btHRESULT hrCString strbt=(_bstr_t) select * from master sysdatabases try{hr=rs CreateInstance (__uuidof(Recordset))ASSERT(SUCCEEDED(hr))//打开数据源hr=rs >Open (bt cn GetInterfacePtr () adOpenDynamic adLockOptimistic adCmdText)ASSERT(SUCCEEDED(hr))rs >MoveFirst()while(!rs >EndOfFile){str=(char*)(_bstr_t)rs >Fields >GetItem( name ) >Valuem_database AddString(str)rs >MoveNext()}rs >Close()m_database SetCurSel( )}catch(__error){}} 读取用户 从master syslogins视图中读取所有非NT用户 void CUser::AddUser(){_RecordsetPtr rs_bstr_t btHRESULT hrCString strbt=(_bstr_t) select * from master syslogins where isntname= try{hr=rs CreateInstance (__uuidof(Recordset))ASSERT(SUCCEEDED(hr))//打开数据源hr=rs >Open (bt cn GetInterfacePtr () adOpenDynamic adLockOptimistic adCmdText)ASSERT(SUCCEEDED(hr))rs >MoveFirst()while(!rs >EndOfFile){str=(char*)(_bstr_t)rs >Fields >GetItem( name ) >Valuem_user_list AddString(str)rs >MoveNext()}rs >Close()m_user_list SetCurSel ( )}catch(__error){}} 读取当前用户有权限的数据库 每个数据库有权限的用户在每个数据的sysusers表中 void CUser::OnSelchangeUserList(){CString strUser strDBm_db_list ResetContent ()m_user_list GetText (m_user_list GetCurSel () strUser)for(int i= i<m_database GetCount ()i++){m_database GetLBText (i strDB)if(HasRight(strUser strDB)){m_db_list AddString (strDB)}}if(m_db_list GetCount ()>){m_db_list SetCurSel ( )}}//查看用户user有没有数据库database的权限BOOL CUser::HasRight(CString user CString database){_RecordsetPtr rs_bstr_t btHRESULT hrBOOL bResult=FALSEbt=(_bstr_t) select * from +(_bstr_t)database+(_bstr_t) sysusers where name= +(_bstr_t)user+(_bstr_t) try{hr=rs CreateInstance (__uuidof(Recordset))ASSERT(SUCCEEDED(hr))//打开数据源hr=rs >Open (bt cn GetInterfacePtr () adOpenDynamic adLockOptimistic adCmdText)ASSERT(SUCCEEDED(hr))rs >MoveFirst()rs >Close()bResult=TRUE}catch(__error){}return bResult} 增加用户及授予权限 增加用户调用存储过程sp_addlogin 授予权限调用存储过程sp_grantdbaccess 基本用法为:sp_addlogin username password sp_grantdbaccess usernamevoid CUser::OnOK(){// TODO: Add extra validation hereUpdateData()CString strDBm_database GetLBText (m_database GetCurSel() strDB)if(m_user IsEmpty () || m_user== sa )returntry{if(m_user_list FindString ( m_user)<){cn >Execute ( sp_addlogin +(_bstr_t)m_user+ +(_bstr_t)m_user+ NULL adExecuteNoRecords)m_user_list AddString (m_user)}else{cn >PutDefaultDatabase ((_bstr_t)strDB)cn >Execute ( sp_grantdbaccess +(_bstr_t)m_user+(_bstr_t) NULL adExecuteNoRecords)m_db_list AddString (strDB)}}catch(__error){AfxMessageBox( 发生错误！ )}//CDialog::OnOK()} 删除用户及权限: 删除用户调用存储过程sp_droplogin 授予权限调用存储过程sp_revokedbaccess 基本用法为:sp_droplogin usernamesp_revokedbaccess usernamevoid CUser::OnBnClickedDel(){UpdateData()m_user TrimRight ()if(m_user IsEmpty () || m_user== sa || m_user_list FindString ( m_user)<){AfxMessageBox( 不能删除! )return}CString strDBm_database GetLBText (m_database GetCurSel () strDB)try{if(m_db_list GetCount ()<){//删除用户cn >Execute ( exec sp_droplogin +(_bstr_t)m_user+ NULL adExecuteNoRecords)m_user_list DeleteString (m_user_list FindString ( m_user))}else{//删除权限if(m_db_list FindString ( strDB)>= ){cn >PutDefaultDatabase ((_bstr_t)strDB)cn >Execute ( exec sp_revokedbaccess +(_bstr_t)m_user+ NULL adExecuteNoRecords)m_db_list DeleteString (m_db_list FindString ( strDB))}}}catch(__error){AfxMessageBox( 发生错误! )}} lishixinzhi/Article/program/SQLServer/201311/21990

使用动态SQL啊。

SET @sql = 'SELECT * FROM TableX WHERE 1 = 1'

IF @Code <>''

SET @sql = @sql + char(13) + char(10) + 'AND Code = @Code'

EXEC(@sql)

如何使用动态SQL往ddlb中添加数据

: insert into 表(字段列表) values(对应各字段的值列表) 如: insert into tb_name(field1, field2) values('aa','bb')

---