http://msdn.microsoft.com/zh-cn/library/ms173724.aspxDENY ALL 拒绝 ALL 不会拒绝所有可能的权限。拒绝 ALL 等同于拒绝适用于指定对象的所有 ANSI-92 权限。对于不同权限,ALL 的含义有所不同: 标量函数权限:EXECUTE、REFERENCES。 表值函数权限:DELETE、INSERT、REFERENCES、SELECT、UPDATE。 存储过程权限:EXECUTE。 表权限:DELETE、INSERT、REFERENCES、SELECT、UPDATE。 视图权限:DELETE、INSERT、REFERENCES、SELECT、UPDATE。
2. deny view definition on proc_damnworld to role_damnworldsample:use master; goif db_id('damnworld') is not null drop database damnworld; go if exists(select * from sys.server_principals where name = 'login_damnworld' ) drop login login_damnworld; goif exists(select * from sys.database_principals where name = 'user_damnworld' ) drop user user_damnworld; goif exists(select * from sys.database_principals where name = 'role_damnworld' ) drop role role_damnworld; goif exists(select * from sys.schemas where name = 'schema_damnworld' ) drop schema schema_damnworld; goif object_id('table_damnworld') is not null drop table table_damnworld; goif object_id('table_damnworld1') is not null drop table table_damnworld1; goif object_id('view_damnworld') is not null drop view view_damnworld; goif object_id('proc_damnworld') is not null drop proc proc_damnworld; go create database damnworld; gouse damnworld; gocreate schema schema_damnworld authorization user_damnworld gocreate login login_damnworld with password = 'f_u' go
create user user_damnworld for login login_damnworld with default_schema = schema_damnworld gocreate role role_damnworld goexec sp_addrolemember 'role_damnworld', 'user_damnworld' go create table table_damnworld (c varchar(100)); gocreate table table_damnworld1 (c varchar(100)); goinsert table_damnworld select 'damn records' go --select * from table_damnworldcreate view view_damnworld as select * from table_damnworld gocreate proc proc_damnworld as select * from proc_damnworld gogrant all on table_damnworld1 to role_damnworld--table deny view definition on table_damnworld1 to role_damnworld grant create table to role_damnworld; grant create table to role_damnworld with grant option--view grant select on table_damnworld to role_damnworld--proc grant view definition on proc_damnworld to role_damnworld revoke view definition on proc_damnworld to role_damnworldgo
cjzm83: 你的语句是哪个版本的数据库的?我用的SQL SERVER 2000,通不过啊,可惜。在SQL2000下有办法吗?
exec sp_addlogin 'username','password','DB1'
exec sp_adduser 'username'
grant select on TB1 to username
grant select on TB2 to username分配两个表的查询权限
GO
http://msdn.microsoft.com/zh-cn/express/ms178569.aspx
deny view definition on proc_damnworld to role_damnworldsample:use master;
goif db_id('damnworld') is not null
drop database damnworld;
go
if exists(select * from sys.server_principals where name = 'login_damnworld' )
drop login login_damnworld;
goif exists(select * from sys.database_principals where name = 'user_damnworld' )
drop user user_damnworld;
goif exists(select * from sys.database_principals where name = 'role_damnworld' )
drop role role_damnworld;
goif exists(select * from sys.schemas where name = 'schema_damnworld' )
drop schema schema_damnworld;
goif object_id('table_damnworld') is not null
drop table table_damnworld;
goif object_id('table_damnworld1') is not null
drop table table_damnworld1;
goif object_id('view_damnworld') is not null
drop view view_damnworld;
goif object_id('proc_damnworld') is not null
drop proc proc_damnworld;
go
create database damnworld;
gouse damnworld;
gocreate schema schema_damnworld authorization user_damnworld
gocreate login login_damnworld with password = 'f_u'
go
create user user_damnworld for login login_damnworld with default_schema = schema_damnworld
gocreate role role_damnworld
goexec sp_addrolemember 'role_damnworld', 'user_damnworld'
go create table table_damnworld (c varchar(100));
gocreate table table_damnworld1 (c varchar(100));
goinsert table_damnworld select 'damn records'
go
--select * from table_damnworldcreate view view_damnworld
as
select * from table_damnworld
gocreate proc proc_damnworld
as
select * from proc_damnworld
gogrant all on table_damnworld1 to role_damnworld--table
deny view definition on table_damnworld1 to role_damnworld
grant create table to role_damnworld;
grant create table to role_damnworld with grant option--view
grant select on table_damnworld to role_damnworld--proc
grant view definition on proc_damnworld to role_damnworld
revoke view definition on proc_damnworld to role_damnworldgo
你的语句是哪个版本的数据库的?我用的SQL SERVER 2000,通不过啊,可惜。在SQL2000下有办法吗?
http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/9efb1c1b-92f8-444b-aac0-4f9b0766e272/
结贴了。