Use
Master
Declare @username sysname, @userpassword nvarchar ( 50 )
Set @username = N ' aa ' -- 要创建的登录(用户)名称
Set @userpassword = ' bb ' -- 要创建的登录(用户)密码
If Exists ( Select * From Master.dbo.syslogins Where loginname = @username )
Begin -- 删除登录(用户)
Declare @lcStr nvarchar ( 4000 )
Declare cTmp Cursor Local For
Select N ' use [ ' + Replace (Name,N ' ] ' ,N ' ]] ' ) + N ' ]
If Exists(Select * From sysusers Where islogin=1 And Name=@username)
Exec sp_revokedbaccess @name_in_db = @username '
From Master.dbo.sysdatabases
Open cTmp
Fetch cTmp Into @lcStr
While @@fetch_status = 0
Begin
Exec sp_executesql @lcStr ,N ' @username sysname ' , @username
Fetch cTmp Into @lcStr
End
Close cTmp
deallocate cTmp
Exec sp_droplogin @loginame = @username
End
-- 创建登录(用户)
Declare @logindb nvarchar ( 132 ), @loginlang nvarchar ( 132 ) Select @logindb = N ' master ' , @loginlang = N ' 简体中文 '
If @logindb Is Null Or Not Exists ( Select * From Master.dbo.sysdatabases Where Name = @logindb )
Select @logindb = N ' master '
If @loginlang Is Null Or ( Not Exists ( Select * From Master.dbo.syslanguages Where Name = @loginlang ) And @loginlang <> N ' us_english ' )
Select @loginlang = @@Language
Exec sp_addlogin @username , @userpassword , @logindb , @loginlang
Exec sp_addsrvrolemember @username , sysadmin
Exec sp_addsrvrolemember @username , securityadmin
Exec sp_addsrvrolemember @username , serveradmin
Exec sp_addsrvrolemember @username , setupadmin
Exec sp_addsrvrolemember @username , processadmin
Exec sp_addsrvrolemember @username , diskadmin
Exec sp_addsrvrolemember @username , dbcreator
Exec sp_addsrvrolemember @username , bulkadmin
If Not Exists ( Select * From dbo.sysusers Where Name = @username And uid < 16382 )
Exec sp_grantdbaccess @username , @username
Exec sp_addrolemember N ' db_owner ' , @username
Go
Declare @username sysname, @userpassword nvarchar ( 50 )
Set @username = N ' aa ' -- 要创建的登录(用户)名称
Set @userpassword = ' bb ' -- 要创建的登录(用户)密码
If Exists ( Select * From Master.dbo.syslogins Where loginname = @username )
Begin -- 删除登录(用户)
Declare @lcStr nvarchar ( 4000 )
Declare cTmp Cursor Local For
Select N ' use [ ' + Replace (Name,N ' ] ' ,N ' ]] ' ) + N ' ]
If Exists(Select * From sysusers Where islogin=1 And Name=@username)
Exec sp_revokedbaccess @name_in_db = @username '
From Master.dbo.sysdatabases
Open cTmp
Fetch cTmp Into @lcStr
While @@fetch_status = 0
Begin
Exec sp_executesql @lcStr ,N ' @username sysname ' , @username
Fetch cTmp Into @lcStr
End
Close cTmp
deallocate cTmp
Exec sp_droplogin @loginame = @username
End
-- 创建登录(用户)
Declare @logindb nvarchar ( 132 ), @loginlang nvarchar ( 132 ) Select @logindb = N ' master ' , @loginlang = N ' 简体中文 '
If @logindb Is Null Or Not Exists ( Select * From Master.dbo.sysdatabases Where Name = @logindb )
Select @logindb = N ' master '
If @loginlang Is Null Or ( Not Exists ( Select * From Master.dbo.syslanguages Where Name = @loginlang ) And @loginlang <> N ' us_english ' )
Select @loginlang = @@Language
Exec sp_addlogin @username , @userpassword , @logindb , @loginlang
Exec sp_addsrvrolemember @username , sysadmin
Exec sp_addsrvrolemember @username , securityadmin
Exec sp_addsrvrolemember @username , serveradmin
Exec sp_addsrvrolemember @username , setupadmin
Exec sp_addsrvrolemember @username , processadmin
Exec sp_addsrvrolemember @username , diskadmin
Exec sp_addsrvrolemember @username , dbcreator
Exec sp_addsrvrolemember @username , bulkadmin
If Not Exists ( Select * From dbo.sysusers Where Name = @username And uid < 16382 )
Exec sp_grantdbaccess @username , @username
Exec sp_addrolemember N ' db_owner ' , @username
Go