none
SQL2000 获取某用户有权限访问的数据库 RRS feed

  • 问题

  • 类似于查询分析器,连接以后只显示当前登录用户有权限访问的数据库,其他的都不显示。
    用SQL要如何实现?
    2009年9月27日 7:57

答案

  • 写成存储过程供应用程序调用,希望对搜索到这篇帖子的朋友有帮助:
    create proc sp_EnumDatabases
    as
    begin
     create table ##tt1(dbname varchar(500))
      exec sp_msforeachdb "insert into ##tt1 select '?' dbname "
     select * from ##tt1
     DROP TABLE ##tt1
    end
    2009年9月28日 15:10

全部回复

  • 很简单,

    遍历服务器内的每个数据库,执行
    select * from sysusers where name ='you db user name'

    检查roles字段(这个有点麻烦,sql把不同角色保存在一个字段内)

    不过如果能查到这个用户至少说明这个用户具有这个数据库的权限,哪怕只是public权限。

    然后吧这个数据库例举出来即可


    这个是2000,其他版本没去研究roles字段怎么存储。有兴趣的自己去看看,环境限制。

    有更好的办法么?
    family as water
    2009年9月27日 12:19
  • Try sp_helpuser.
    2009年9月27日 18:30
  • Try sp_helpuser.

    还是不行哎
    2009年9月28日 1:09
  • What you really try to do?
    2009年9月28日 1:37
  • 在做一个模仿SQL2000查询分析器的工具.虽然可以用ODBC API枚举到我想要的结果,但是我觉得在数据库里肯定记录每个登录用户所能访问的数据库.我说的用户不是指DBO那样的用户,而是指登录服务器时输入的用户名,如sa
    2009年9月28日 3:44
  • 服务器上肯定记录了这些信息,而且应该在master这样的系统表.
    如果用SQL能做到,那肯定比ODBC API好.
    2009年9月28日 3:47
  • 服务器上肯定记录了这些信息,而且应该在master这样的系统表.
    如果用SQL能做到,那肯定比ODBC API好.

    这个不是一个简单的问题。但是也不是不可能。

    打开企业管理器 查看某Login的属性时,打开事件探查器即可知道MS是用什么SQL提取出来的。
    反正一切皆SQL

    其实也没什么特别,就是依次在每个数据库中分别读取,整个过程得几十条SQL,最后再汇总。

    select * from dbname.dbo.sysusers

    或 
    exec sp_helprolemember N'db_owner'等等
    hello
    2009年9月28日 10:21
  • 楼主,您的需求可以实现滴,以下请参考:
    1. 通过SQL自带的存储过程:master.dbo.sp_helplogins 'Login'
    2. 在sysdatabases中找到所有数据库,通过遍历这些数据库,在sysusers表里找对应的User。权限记录在用户DB的sysmembers和syspermissions表里

    这里要注意下Login和User两个不同的概念
    1. Login保存在master.dbo.syslogins, User保存在各个DB的sysusers表。
    2. Login和User通过SID字段关联。(因此:A, Login和User可以是不同的名字; B, Login和User名字即使相同,如果SID不一样,这个Login也没有该User的权限)
    3. Login和User可通过sp_change_users_login进行关联。

    以上,如有问题可参考SQL Server手册,或者继续提出来~

    2009年9月28日 13:15
  • 谢谢你的帮助.
    经过多次尝试,发现最简单的办法是:
    sp_msforeachdb "Select '?'"
    2009年9月28日 14:43
  • 写成存储过程供应用程序调用,希望对搜索到这篇帖子的朋友有帮助:
    create proc sp_EnumDatabases
    as
    begin
     create table ##tt1(dbname varchar(500))
      exec sp_msforeachdb "insert into ##tt1 select '?' dbname "
     select * from ##tt1
     DROP TABLE ##tt1
    end
    2009年9月28日 15:10
  • 确实是个好办法
    不过只能查询当前的登陆有权限的DB
    如果是在Web页面通过诸如sa之类帐号去查询显示每个Login有权限的DB,需再配合其他方法。
    2009年9月29日 0:41
  • 非常感觉你的帮助,我会按照你的提示继续寻找好的办法.
    2009年9月29日 1:21
  • 这个神奇的存储过程倒是简单。
    仔细看看的话
    sp_helptext sp_msforeachdb
    sp_helptext sp_MSforeach_worker
    其实也不简单,一大堆的sql,而且也并没有怎么判断,只是按游标依次在每个数据库执行吧。没有结果的就丢弃了
    hello
    2009年9月29日 7:34
  • 又发现一件很神奇的事情。
    用ODBC API获取数据库列表时只需要提供服务器地址,用户名,密码,但是不需要端口号(连接默认端口不是1433的服务器)即可得到正确的数据库列表,没有端口也就是说还未真正登录到服务器即可得到数据库列表,看来ODBC API获取数据库列表的方式不是用SQL的,太神奇了,不登录也能枚举到!
    2009年10月1日 10:04
  • 又发现一件很神奇的事情。
    用ODBC API获取数据库列表时只需要提供服务器地址,用户名,密码,但是不需要端口号(连接默认端口不是1433的服务器)即可得到正确的数据库列表,没有端口也就是说还未真正登录到服务器即可得到数据库列表,看来ODBC API获取数据库列表的方式不是用SQL的,太神奇了,不登录也能枚举到!
    LZ啊,我感觉您的这个结论推导的有些武断呢。
    不显示声明非1433端口号而得到数据库列表,并不表示没有登陆。
    ODBC只是连接驱动而已,数据肯定还是放在数据库里的。

    我记得曾经遇到个问题是不加非1433端口号无法访问数据库的问题,后来是开了SQL Server Browser服务就可以了。
    请参考http://technet.microsoft.com/zh-cn/library/ms345288.aspx

    另外,我刚才做了测试,将sql server的端口进行了修改,可以通过机器名或"."作为名称用Administrator直接登陆,但用127.0.0.1和IP地址是不行的。
    所以,您的测试结果最好再仔细看看。
    2009年10月1日 14:05
  • Don't need port number if connect with named pipes protocol.
    2009年10月1日 14:44
  • 又发现一件很神奇的事情。
    用ODBC API获取数据库列表时只需要提供服务器地址,用户名,密码,但是不需要端口号(连接默认端口不是1433的服务器)即可得到正确的数据库列表,没有端口也就是说还未真正登录到服务器即可得到数据库列表,看来ODBC API获取数据库列表的方式不是用SQL的,太神奇了,不登录也能枚举到!
    LZ啊,我感觉您的这个结论推导的有些武断呢。
    不显示声明非1433端口号而得到数据库列表,并不表示没有登陆。
    ODBC只是连接驱动而已,数据肯定还是放在数据库里的。

    我记得曾经遇到个问题是不加非1433端口号无法访问数据库的问题,后来是开了SQL Server Browser服务就可以了。
    请参考http://technet.microsoft.com/zh-cn/library/ms345288.aspx

    另外,我刚才做了测试,将sql server的端口进行了修改,可以通过机器名或"."作为名称用Administrator直接登陆,但用127.0.0.1和IP地址是不行的。
    所以,您的测试结果最好再仔细看看。
    本机可以不需要端口号.

    我测试用的服务器是指定端口号为9090的客户的服务器,没有命名管道,用的API是SQLBrowseConnect,相信SQL Server Browser也是用它枚举数据库的.SQL Server Browser(或者说SQLBrowseConnect API)为什么不需要指定端口号即可获得呢?

    如果通过TCP/IP方式,没有端口号是肯定建立不了TCP/IP连接的,所以也连接不到数据库服务器,更别说取数据.
    所以SQL Server Browser可能并非通过TCP/IP方式访问的数据库服务器.
    而Rmiao说到命名管道,对命名管道这个名词不是很了解.我尝试用命名管道方式连接服务器,结果连接不上(因为服务器未配置命名管道方连接).但是SQLBrowseConnect却还是能枚举到数据库.
    很奇怪.莫非SQL SERVER还有秘密通道可以由SQLBrowseConnect自由访问?
    2009年10月1日 16:08
  • 可惜找不到Oralce服务器,如果用Oracle的ODBC驱动能枚举到数据库,那就应该是驱动在做遂,与数据库无关.
    可能服务器的数据库信息还以另一种方式记录在系统的某个文件中吧,比如说Windows下Sql server的数据库基本信息可能记录在某个配置文件中,驱动程序间通信时只是交换那些文件内的信息,甚至驱动程序之间是通过另一个端口通信的.懒得去监控了,睡觉~
    2009年10月1日 16:20
  • 又发现一件很神奇的事情。
    用ODBC API获取数据库列表时只需要提供服务器地址,用户名,密码,但是不需要端口号(连接默认端口不是1433的服务器)即可得到正确的数据库列表,没有端口也就是说还未真正登录到服务器即可得到数据库列表,看来ODBC API获取数据库列表的方式不是用SQL的,太神奇了,不登录也能枚举到!
    LZ啊,我感觉您的这个结论推导的有些武断呢。
    不显示声明非1433端口号而得到数据库列表,并不表示没有登陆。
    ODBC只是连接驱动而已,数据肯定还是放在数据库里的。

    我记得曾经遇到个问题是不加非1433端口号无法访问数据库的问题,后来是开了SQL Server Browser服务就可以了。
    请参考http://technet.microsoft.com/zh-cn/library/ms345288.aspx

    另外,我刚才做了测试,将sql server的端口进行了修改,可以通过机器名或"."作为名称用Administrator直接登陆,但用127.0.0.1和IP地址是不行的。
    所以,您的测试结果最好再仔细看看。
    本机可以不需要端口号.

    我测试用的服务器是指定端口号为9090的客户的服务器,没有命名管道,用的API是SQLBrowseConnect,相信SQL Server Browser也是用它枚举数据库的.SQL Server Browser(或者说SQLBrowseConnect API)为什么不需要指定端口号即可获得呢?

    如果通过TCP/IP方式,没有端口号是肯定建立不了TCP/IP连接的,所以也连接不到数据库服务器,更别说取数据.
    所以SQL Server Browser可能并非通过TCP/IP方式访问的数据库服务器.
    而Rmiao说到命名管道,对命名管道这个名词不是很了解.我尝试用命名管道方式连接服务器,结果连接不上(因为服务器未配置命名管道方连接).但是SQLBrowseConnect却还是能枚举到数据库.
    很奇怪.莫非SQL SERVER还有秘密通道可以由SQLBrowseConnect自由访问?
    Sql browser service uses udp port 1434, you have to open it on network to let client get sql port from sql browser serice.
    2009年10月2日 0:25