项目开发中常用到的SQL语句

DECLARE @i int
DECLARE @name varchar(10)
DECLARE @password varchar(10)
Set @i = 1000

WHILE @i < 1200
BEGIN
Set @i =@i +1
SET @name = RIGHT('0000' + CAST(@i AS varchar(10)),4) 
set @password = @name
select @name

insert into dbo.LocomotiveTeminalBase
(li_ID,t_ID,lt_IDNumber,lt_MiM,lt_FuWQIP,lt_FuWQDKH,lt_CreatedBy)
values('d82575c0-2d21-4c47-a406-7771d7d2c80a','fb5d9a7b-9cd6-4a55-9e90-881706eaf2b9',
@name,@password,'192.168.1.187','2000','9015c234-e185-4e15-96c6-f53426dd6690')

END

--查看状态
  Select DATABASEpRoPERTYEX('soft_LocomotiveRM_DB','IsBrokerEnabled')
  
  --启用broker
ALTER DATABASE soft_LocomotiveRM_DB SET NEW_BROKER WITH ROLLBACK IMMEDIATE
ALTER DATABASE soft_LocomotiveRM_DB SET ENABLE_BROKER

--添加用户
--创建一个新的登录
EXEC sp_addlogin 'test','123456'
--赋予新用户SA(System Administrator)权限,添加服务器角色
EXEC sp_addsrvrolemember 'test','sysadmin'   
--授予对自己





数据库的所有权限         
exec sp_addrolemember N'db_owner', N'test'  

--赋予权限
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO test 


--检查错误 
    USE [master]
    GO
    ALTER DATABASE soft_LocomotiveRM_DB SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO
    ALTER DATABASE soft_LocomotiveRM_DB SET  SINGLE_USER 
    GO
    ALTER DATABASE soft_LocomotiveRM_DB SET ENABLE_BROKER 
    GO
    ALTER DATABASE soft_LocomotiveRM_DB SET  MULTI_USER 
    GO 

use soft_LocomotiveRM_DB
go


--新增用户
exec sp_addlogin 'test'            --添 加登录
exec sp_grantdbaccess N'test'            --使其成为当前数据库的合法用户
exec sp_addrolemember N'db_owner', N'test'      --授予对自己数据库的所有权限


exec sp_revokedbaccess N'test'            --移除对数据库的访问权限
exec sp_droplogin N'test'            --删除登录