SELECT sysobjects.name FROM sysobjects WHERE sysobjects.xtype = 'U'
不错,可以查询系统表sysobjects
谢谢,可是查询结果中有表“dtproperties” 。 如何体现用户A或者B?
假设我现在以用户A登陆数据库,想要返回用户A所能看到的所有的表。该如何写SQL语句?
SELECT sysobjects.name FROM sysobjects WHERE sysobjects.xtype = 'U' and uid = (select uid from sysusers where name = @username);
数据库中有用户fj,用fj登陆也可以看到一些表,但用以下sql语句却不能返回任何表名 SELECT sysobjects.name FROM sysobjects WHERE sysobjects.xtype = 'U' and uid = (select uid from sysusers where name = 'fj');
/*选出该用户创建的表*/ SELECT sysobjects.name FROM sysobjects WHERE sysobjects.xtype = 'U' and uid = (select uid from sysusers where name = @username); /*选出用户具有Select,Update,Insert,delete权限的表*/ SELECT sysobjects.name FROM sysobjects WHERE sysobjects.xtype = 'U' and id = (select id from sysprotects where action in (193,195,196,197) and uid = (uid from sysusers where name = @username));
/*选出该用户创建的表*/ SELECT sysobjects.name FROM sysobjects WHERE sysobjects.xtype = 'U' and uid = (select uid from sysusers where name = @username); /*选出用户具有Select,Update,Insert,delete权限的表*/ SELECT sysobjects.name FROM sysobjects WHERE sysobjects.xtype = 'U' and id = (select id from sysprotects where action in (193,195,196,197) and uid = (uid from sysusers where name = @username));
SELECT sysobjects.name FROM sysobjects WHERE sysobjects.xtype = 'U'
WHERE sysobjects.xtype = 'U'
如何体现用户A或者B?
WHERE sysobjects.xtype = 'U' and uid = (select uid from sysusers where name = @username);
SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.xtype = 'U' and uid = (select uid from sysusers where name = 'fj');
SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.xtype = 'U' and uid = (select uid from sysusers where name = @username);
/*选出用户具有Select,Update,Insert,delete权限的表*/
SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.xtype = 'U' and id = (select id from sysprotects
where action in (193,195,196,197) and uid = (uid from sysusers where name = @username));
SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.xtype = 'U' and uid = (select uid from sysusers where name = @username);
/*选出用户具有Select,Update,Insert,delete权限的表*/
SELECT sysobjects.name FROM sysobjects
WHERE sysobjects.xtype = 'U' and id = (select id from sysprotects
where action in (193,195,196,197) and uid = (uid from sysusers where name = @username));