ansi__database.sql

扬州升瑞售楼系统2000扬州升瑞售楼系统2000扬州升瑞售楼系统2000扬州升瑞售楼系统2000扬州升瑞售楼系统2000

/****** Object:  Database 授权管理    Script Date: 2002-01-30 16:23:42 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'授权管理')
	DROP DATABASE [授权管理]
GO

CREATE DATABASE [授权管理]  ON (NAME = N'授权管理_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\授权管理_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'授权管理_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\data\授权管理_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
GO

exec sp_dboption N'授权管理', N'autoclose', N'true'
GO

exec sp_dboption N'授权管理', N'bulkcopy', N'false'
GO

exec sp_dboption N'授权管理', N'trunc. log', N'true'
GO

exec sp_dboption N'授权管理', N'torn page detection', N'true'
GO

exec sp_dboption N'授权管理', N'read only', N'false'
GO

exec sp_dboption N'授权管理', N'dbo use', N'false'
GO

exec sp_dboption N'授权管理', N'single', N'false'
GO

exec sp_dboption N'授权管理', N'autoshrink', N'true'
GO

exec sp_dboption N'授权管理', N'ANSI null default', N'false'
GO

exec sp_dboption N'授权管理', N'recursive triggers', N'false'
GO

exec sp_dboption N'授权管理', N'ANSI nulls', N'false'
GO

exec sp_dboption N'授权管理', N'concat null yields null', N'false'
GO

exec sp_dboption N'授权管理', N'cursor close on commit', N'false'
GO

exec sp_dboption N'授权管理', N'default to local cursor', N'false'
GO

exec sp_dboption N'授权管理', N'quoted identifier', N'false'
GO

exec sp_dboption N'授权管理', N'ANSI warnings', N'false'
GO

exec sp_dboption N'授权管理', N'auto create statistics', N'true'
GO

exec sp_dboption N'授权管理', N'auto update statistics', N'true'
GO

use [授权管理]
GO

/****** Object:  Stored Procedure dbo.procChangeMemberRoles    Script Date: 2002-01-30 16:24:00 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procChangeMemberRoles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procChangeMemberRoles]
GO

/****** Object:  Stored Procedure dbo.procDeleteMember_GivePower    Script Date: 2002-01-30 16:24:00 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procDeleteMember_GivePower]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procDeleteMember_GivePower]
GO

/****** Object:  Stored Procedure dbo.procInsertMember_GivePower    Script Date: 2002-01-30 16:24:00 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procInsertMember_GivePower]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procInsertMember_GivePower]
GO

/****** Object:  Stored Procedure dbo.procUpdateMember_GivePower    Script Date: 2002-01-30 16:24:00 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[procUpdateMember_GivePower]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[procUpdateMember_GivePower]
GO

/****** Object:  Table [dbo].[CoMember]    Script Date: 2002-01-30 16:24:00 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CoMember]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CoMember]
GO

/****** Object:  Table [dbo].[UserRoles]    Script Date: 2002-01-30 16:24:00 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UserRoles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[UserRoles]
GO

/****** Object:  User dbo    Script Date: 2002-01-30 16:23:43 ******/
/****** Object:  Table [dbo].[CoMember]    Script Date: 2002-01-30 16:24:02 ******/
CREATE TABLE [dbo].[CoMember] (
	[姓名] [char] (20) NOT NULL ,
	[性别] [char] (2) NULL ,
	[家庭电话] [char] (20) NULL ,
	[手机号码] [char] (12) NULL ,
	[联系地址] [char] (50) NULL ,
	[工作时间] [datetime] NULL ,
	[照片] [image] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object:  Table [dbo].[UserRoles]    Script Date: 2002-01-30 16:24:07 ******/
CREATE TABLE [dbo].[UserRoles] (
	[用户名] [char] (20) NOT NULL ,
	[密码] [char] (10) NULL ,
	[财务管理] [char] (10) NULL ,
	[房源信息管理] [char] (10) NULL ,
	[分析查询管理] [char] (10) NULL ,
	[价格管理] [char] (10) NULL ,
	[物业管理] [char] (10) NULL ,
	[项目展示] [char] (10) NULL ,
	[销售管理] [char] (10) NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CoMember] WITH NOCHECK ADD 
	CONSTRAINT [PK_CoMember] PRIMARY KEY  CLUSTERED 
	(
		[姓名]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[UserRoles] WITH NOCHECK ADD 
	CONSTRAINT [PK_UserRoles] PRIMARY KEY  CLUSTERED 
	(
		[用户名]
	)  ON [PRIMARY] 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.procChangeMemberRoles    Script Date: 2002-01-30 16:24:08 ******/
CREATE proc procChangeMemberRoles
            @MemberName varchar(20),
            @NumOne int,
            @MemberPower varchar(10),
            @AddOrSub varchar(10), 
            @Succey varchar(10) output   --成功的修改了权限后成功返回显示的数据
AS 

--先看看是加权，还是减权
if @AddOrSub='Add'
 begin 
          Declare @NOorYes int,@Power int
            --先查找在权限表中有没有这个用户
            select @NOorYes=count(*) from userRoles where  用户名=@MemberName
                  if @NOorYes=0      --没有这个用户，加上一个新用户  
                             begin      
                             insert into userRoles(用户名,财务管理,房源信息管理,分析查询管理,价格管理,物业管理,项目展示,销售管理) values(@MemberName,'','','','','','','')
                             end

                  
                  --查找有没有这样的权限        

                                                                  

if @NumOne=1     select @Power=count(*) from userRoles where 用户名=@MemberName and 财务管理<>''
if @NumOne=2     select @Power=count(*) from userRoles where 用户名=@MemberName and 房源信息管理<>''
if @NumOne=3     select @Power=count(*) from userRoles where 用户名=@MemberName and 分析查询管理<>''
if @NumOne=4     select @Power=count(*) from userRoles where 用户名=@MemberName and 价格管理<>''
if @NumOne=5     select @Power=count(*) from userRoles where 用户名=@MemberName and 物业管理<>''
if @NumOne=6     select @Power=count(*) from userRoles where 用户名=@MemberName and 项目展示<>''
if @NumOne=7     select @Power=count(*) from userRoles where 用户名=@MemberName and 销售管理<>''

                 -- select @Power=count(*) from userRoles where 用户名=@MemberName and @MemberPowerName=@MemberPower
                      if @Power=0 --没有相对的权限,加上权限
                          begin

     if @NumOne=1         update userRoles set 财务管理=@MemberPower where 用户名=@MemberName 
     if @NumOne=2         update userRoles set 房源信息管理=@MemberPower where 用户名=@MemberName 
     if @NumOne=3         update userRoles set 分析查询管理=@MemberPower where 用户名=@MemberName 
     if @NumOne=4         update userRoles set 价格管理=@MemberPower where 用户名=@MemberName  
     if @NumOne=5         update userRoles set 物业管理=@MemberPower where 用户名=@MemberName 
     if @NumOne=6         update userRoles set 项目展示=@MemberPower where 用户名=@MemberName