sqlserver权限问题，有经验者请进

[seatree]

唉，这个问题我想给1000分，结果只能给100分，现说声抱歉了。
我如何才能控制用户以sqlserver的用户名和密码登陆进来时，只看见sa用户给当前用户授予权限的表
如建立一个用户username:search password:serch;SA应如何授权才能让用户只看见SA用户的表中特定几个表或视图.
请不要想当然，我按照想当然的方法建立登陆名，用户名和密码，授予一个表select的权限，结果在sqlserver中换此用户名和密码登陆进去还是能看见所有的表。

[seatree]

顶一下，有人有这方面经验吗

[zjcxc]

不支持,就是不支持,我觉得与经验无关吧?

[seatree]

前辈，我的意思是问谁知道有哪一个DBMS支持控制“对象名称的浏览权限”的，我正在编一个授权查询系统

[WangZWang]

sql server没有此功能吧..

[zjcxc]

--看这个是否合你用

如何获得SQL SERVER2000数据库指定对象的权限列表？

leimin（原作）


前几天看到有人问是否可以方便的获得SQL SERVER2000指定对象的权限和指定USER的权
限。我写了一个存储过程，可以获得用户和角色的权限。请大家帮忙测试一下。看看是
否还有BUG:-)

IF OBJECTPROPERTY( OBJECT_ID( 'usp_getObjectAuthor' ) , 'IsProcedure' ) =1
DROP PROC usp_getObjectAuthor
GO
/***************************************************************************
*****/
/* Created By : leimin */
/* Created On : 29 May 2004 */
/* Description : This stored procedure returns the object permission which
you */
/* GRANT,DENY and REVOKE.
*/
/***************************************************************************
*****/
Create proc usp_getObjectAuthor
@objectname sysname = null,
@username sysname = null
as
set nocount on
begin
/***************************************************************************
*****/
/* defined the initilization variable */
/***************************************************************************
*****/
Declare @rc int
Declare @rowcount int
Declare @groupid int

Set @rc=0
Set @rowcount=0

/***************************************************************************
*****/
/* Judge the input parameters ,if @objectname is null and @username is
null */
/* then return all objects authorization. */
/***************************************************************************
*****/
if @objectname is null and @username is null
begin
select object_name(a.id) as objectname,
user_name(a.uid) as usename,
case b.issqlrole when 1 then 'Group '
else 'User'
end as Role,
case a.protecttype when 205 then 'Grant'
when 204 then 'Grant'
when 206 then 'Deny'
else 'Revoke'
end as ProtectType,
case a.[action] when 26 then 'REFERENCES'
when 178 then 'CREATE FUNCTION'
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
when 198 then 'CREATE TABLE'
when 203 then 'CREATE DATABASE'
when 207 then 'CREATE VIEW'
when 222 then 'CREATE PROCEDURE'
when 224 then 'EXECUTE'
when 228 then 'BACKUP DATABASE'
when 233 then 'CREATE DEFAULT'
when 235 then 'BACKUP LOG'
when 236 then 'CREATE RULE'
else '0'
end as [Action],
user_name(a.grantor) as Grantor
from sysprotects a inner join sysusers b on a.uid=b.uid
where exists (select 1 from sysobjects
where [name]=object_name(a.id) and xtype <>'S' )
order by object_name(a.id)

select @rowcount=@@rowcount
if @rowcount=0
begin
select @rc=-1
print 'There a no user objects in database!'
return @rc
end
end
/***************************************************************************
*****/
/* Judge the input parameters ,if @objectname is null and @username is not
null */
/* then return all objects authorization where relation @username */
/* if the user belong to a group ,so we must add the group authorization */
/***************************************************************************
*****/
if @rc=0 and @username is not null and @objectname is null
begin
if not exists(select * from sysusers where [uid]=user_id(@username) and
status<>0)
begin
select @rc=-2
print 'The user name is not include in sysusers table.'
return @rc
end

if exists(select 1 from sysmembers where [memberuid]=user_id(@username))
begin
select object_name(a.id) as objectname,
user_name(a.uid) as usename,
case b.issqlrole when 1 then 'Group '
else 'User'
end as Role,
case a.protecttype when 205 then 'Grant'
when 204 then 'Grant'
when 206 then 'Deny'
else 'Revoke'
end as ProtectType,
case a.[action] when 26 then 'REFERENCES'
when 178 then 'CREATE FUNCTION'
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
when 198 then 'CREATE TABLE'
when 203 then 'CREATE DATABASE'
when 207 then 'CREATE VIEW'
when 222 then 'CREATE PROCEDURE'
when 224 then 'EXECUTE'
when 228 then 'BACKUP DATABASE'
when 233 then 'CREATE DEFAULT'
when 235 then 'BACKUP LOG'
when 236 then 'CREATE RULE'
else '0'
end as [Action],
user_name(a.grantor) as Grantor
from sysprotects a inner join sysusers b on a.uid=b.uid
where exists (select 1 from sysobjects
where [name]=object_name(a.id) and xtype <>'S' )
and ( exists (select 1 from sysmembers
where groupuid=a.uid and memberuid=user_id(@username))
or a.uid=user_id(@username))
order by object_name(a.id)

select @rowcount=@@rowcount
if @rowcount=0
begin
select @rc=-3
print @username+' have not any objects authorization.'
return @rc
end
end
else
begin
select object_name(a.id) as objectname,
user_name(a.uid) as usename,
case b.issqlrole when 1 then 'Group '
else 'User'
end as Role,
case a.protecttype when 205 then 'Grant'
when 204 then 'Grant'
when 206 then 'Deny'
else 'Revoke'
end as ProtectType,
case a.[action] when 26 then 'REFERENCES'
when 178 then 'CREATE FUNCTION'
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
when 198 then 'CREATE TABLE'
when 203 then 'CREATE DATABASE'
when 207 then 'CREATE VIEW'
when 222 then 'CREATE PROCEDURE'
when 224 then 'EXECUTE'
when 228 then 'BACKUP DATABASE'
when 233 then 'CREATE DEFAULT'
when 235 then 'BACKUP LOG'
when 236 then 'CREATE RULE'
else '0'
end as [Action],
user_name(a.grantor) as Grantor
from sysprotects a inner join sysusers b on a.uid=b.uid
where exists (select 1 from sysobjects
where [name]=object_name(a.id) and xtype <>'S' )
and a.uid=user_id(@username)
order by object_name(a.id)

select @rowcount=@@rowcount
if @rowcount=0
begin
select @rc=-4
print @username+' have not any objects authorization.'
return @rc
end
end

end

[zjcxc]

/***************************************************************************
*****/
/* Judge the input parameters ,if @objectname is not null and @username is
null */
/* then return one objects authorization */
/***************************************************************************
*****/
if @rc=0 and @objectname is not null and @username is null
begin
if not exists(select * from sysobjects where [id]=object_id(@objectname)
and xtype<>'S')
begin
select @rc=-5
return @rc
end
if @rc=0
begin
select object_name(a.id) as objectname,
user_name(a.uid) as usename,
case b.issqlrole when 1 then 'Group '
else 'User'
end as Role,
case a.protecttype when 205 then 'Grant'
when 204 then 'Grant'
when 206 then 'Deny'
else 'Revoke'
end as ProtectType,
case a.[action] when 26 then 'REFERENCES'
when 178 then 'CREATE FUNCTION'
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
when 198 then 'CREATE TABLE'
when 203 then 'CREATE DATABASE'
when 207 then 'CREATE VIEW'
when 222 then 'CREATE PROCEDURE'
when 224 then 'EXECUTE'
when 228 then 'BACKUP DATABASE'
when 233 then 'CREATE DEFAULT'
when 235 then 'BACKUP LOG'
when 236 then 'CREATE RULE'
else '0'
end as [Action],
user_name(a.grantor) as Grantor
from sysprotects a inner join sysusers b on a.uid=b.uid
where exists (select 1 from sysobjects
where [name]=object_name(a.id) and xtype <>'S' )
and [id]=object_id(@objectname)
order by object_name(a.id)

select @rowcount=@@rowcount
if @rowcount=0
begin
select @rc=-6
print @objectname+' have not grant authorization to any user'
return @rc
end
end
end
/***************************************************************************
*****/
/* Judge the input parameters ,if @objectname is not null and @username is
not null */
/* then return one objects authorization by one user */
/***************************************************************************
*****/
if @rc=0 and @objectname is not null and @username is not null
begin
if not exists(select * from sysobjects where [id]=object_id(@objectname)
and xtype<>'S')
begin
select @rc=-7
print 'The object name is not include in sysobjects table.'
return @rc
end

if not exists(select * from sysusers where [uid]=user_id(@username) and
status<>0)
begin
select @rc=-8
print 'The user name is not include in sysusers table.'
return @rc
end

if exists(select 1 from sysmembers where [memberuid]=user_id(@username))
begin
select object_name(a.id) as objectname,
user_name(a.uid) as usename,
case b.issqlrole when 1 then 'Group '
else 'User'
end as Role,
case a.protecttype when 205 then 'Grant'
when 204 then 'Grant'
when 206 then 'Deny'
else 'Revoke'
end as ProtectType,
case a.[action] when 26 then 'REFERENCES'
when 178 then 'CREATE FUNCTION'
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
when 198 then 'CREATE TABLE'
when 203 then 'CREATE DATABASE'
when 207 then 'CREATE VIEW'
when 222 then 'CREATE PROCEDURE'
when 224 then 'EXECUTE'
when 228 then 'BACKUP DATABASE'
when 233 then 'CREATE DEFAULT'
when 235 then 'BACKUP LOG'
when 236 then 'CREATE RULE'
else '0'
end as [Action],
user_name(a.grantor) as Grantor
from sysprotects a inner join sysusers b on a.uid=b.uid
where exists (select 1 from sysobjects
where [name]=object_name(a.[id]) and xtype <>'S' )
and (exists (select 1 from sysmembers
where groupuid=a.uid and memberuid=user_id(@username))
or a.uid=user_id(@username))
and [id]=object_id(@objectname)
order by object_name(a.id)

select @rowcount=@@rowcount
if @rowcount=0
begin
select @rc=-9
print @username+' have not any objects authorization.'
return @rc
end
end
else
begin
select object_name(a.id) as objectname,
user_name(a.uid) as usename,
case b.issqlrole when 1 then 'Group '
else 'User'
end as Role,
case a.protecttype when 205 then 'Grant'
when 204 then 'Grant'
when 206 then 'Deny'
else 'Revoke'
end as ProtectType,
case a.[action] when 26 then 'REFERENCES'
when 178 then 'CREATE FUNCTION'
when 193 then 'SELECT'
when 195 then 'INSERT'
when 196 then 'DELETE'
when 197 then 'UPDATE'
when 198 then 'CREATE TABLE'
when 203 then 'CREATE DATABASE'
when 207 then 'CREATE VIEW'
when 222 then 'CREATE PROCEDURE'
when 224 then 'EXECUTE'
when 228 then 'BACKUP DATABASE'
when 233 then 'CREATE DEFAULT'
when 235 then 'BACKUP LOG'
when 236 then 'CREATE RULE'
else '0'
end as [Action],
user_name(a.grantor) as Grantor
from sysprotects a inner join sysusers b on a.uid=b.uid
where exists (select 1 from sysobjects
where [name]=object_name(a.[id]) and xtype <>'S' )
and a.uid=user_id(@username)
and [id]=object_id(@objectname)
order by object_name(a.id)

select @rowcount=@@rowcount
if @rowcount=0
begin
select @rc=-10
print @username+' have not any objects authorization.'
return @rc
end
end
end
end
go
exec usp_getObjectAuthor

[seatree]

好复杂，要研究一下

[zjcxc]

对象名称的浏览权限是不可限制的

你只可以限制用户不能访问

[zjcxc]

你用你创建的用户访问没有分配权限的表试试,肯定不可以访问.

[seatree]

我听说oracle就可以这样，但sqlserver不行吗

[andy81391394]

收了

[yecheng2008]

好复杂，要研究一下!
seatree(海树):你找到解决方法了吗？
我现在也在为个这个郁闷了？
想了好多办法都不行：（（
如你找到好的办法请告知：
[e-mail]

顶一下

[qq3080]

關注！！！！
如你找到好的办法请告知：
[e-mail]
先謝了！

[yecheng2008]

關注！！！！
如你找到好的办法请告知：
58239469
[e-mail]
先謝了！

[Liroyal]

应该是很难实现的。
在数据库角色里，对sysobjects有SELECT权限，注意，这也是一个表。
那么被赋予这个角色的用户，继承了对sysobjects的SELECT权限，所以全部对象都是可视的。
把数据库角色对sysobjects的SELECT权限去掉会有什么结果呢？哈，结果全部对象都不可视，不管该用户对对象有什么权限都一样。

[ninghao37]

我也想要一份

[ninghao37]

[e-mail]