100分求一应用程序权限设置例子!!!!!

[anyejingling]

[e-mail]

[wangrenda]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_user_group_group]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[user_group] DROP CONSTRAINT FK_user_group_group
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[user_operation]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[user_operation]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[user_operation_D]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[user_operation_D]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Operation_D]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Operation_D]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PopedomD]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PopedomD]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PopedomM]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PopedomM]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[group]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[group]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[operation_M]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[operation_M]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[user_group]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[user_group]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[users]
GO

CREATE TABLE [dbo].[Operation_D] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[groupID] [int] NULL ,
[opt_MasterID] [int] NULL ,
[opt_DetialID] [int] NULL ,
[opt_Detial] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PopedomD] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[MasterID] [int] NOT NULL ,
[DetialTitle] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PopedomM] (
[MasterID] [int] IDENTITY (1, 1) NOT NULL ,
[title] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[group] (
[grp_id] [int] IDENTITY (1, 1) NOT NULL ,
[grp_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[grp_info] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[operation_M] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[group_ID] [int] NULL ,
[opt_masterID] [int] NULL ,
[opt_master] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[user_group] (
[ug_id] [int] IDENTITY (1, 1) NOT NULL ,
[ug_user] [int] NULL ,
[ug_group] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[users] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[user_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[user_grade] [smallint] NULL
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.user_operation
AS
SELECT dbo.users.user_name, dbo.operation_M.opt_masterID,
dbo.operation_M.opt_master
FROM dbo.user_group INNER JOIN
dbo.users ON dbo.user_group.ug_id = dbo.users.ID RIGHT OUTER JOIN
dbo.operation_M INNER JOIN
dbo.[group] ON dbo.operation_M.group_ID = dbo.[group].grp_id ON
dbo.user_group.ug_group = dbo.[group].grp_id

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.user_operation_D
AS
SELECT dbo.users.user_name, dbo.Operation_D.opt_DetialID,
dbo.Operation_D.opt_Detial
FROM dbo.Operation_D INNER JOIN
dbo.[group] ON dbo.Operation_D.groupID = dbo.[group].grp_id LEFT OUTER JOIN
dbo.user_group INNER JOIN
dbo.users ON dbo.user_group.ug_id = dbo.users.ID ON
dbo.[group].grp_id = dbo.user_group.ug_group

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[wangrenda]

里面有两个视图,使用其可查找到相应用户权限

[因为想念才会寂寞]

mark up