none
SQL2008找不到默认架构的存储过程! RRS feed

  • 问题

  • 本地环境: XPsp3  + MSSQL2008 + IIS 5.1

    我把虚拟主机上SQL2005的数据库生成SQL脚本,导入本地SQL2008。

    导入之后:数据库TEST建有架构名为TEST,用户SA的默认数据库和默认架构也都是TEST,但是在SSMS运行

    exec [sp_name]		--提示“找不到存储过程”
    exec TEST.[sp_name]	--这样运行可以

    但是主机上的SQL2005服务器可以直接运行 exec [sp_name]

    是本地的SQL2008 哪里没设置好吗? 而且要是导入SQL2000,是不是也不行,貌似2000不支持“架构”呢?

    折腾好久也没找到原因。今天虚拟主机就要到期了……求各位大侠支招  抓狂@_@




    • 已编辑 Wving5 2014年9月29日 17:42
    2014年9月29日 17:39

答案

  • Usually sa is mapped to dbo in user db with default schema set to dbo, that's why you have to specify schema when call sp. If db user has TEST as default schema, it can reference any object in TEST schema with object name only.
    • 已标记为答案 Wving5 2014年9月30日 5:57
    2014年9月29日 18:57
  • 用普通用户测试, 发现 sysadmin 成员无法认到默认架构,不知道 sa 的原因是否是因为这个(sa 是 sysadmin 角色成员)

    -- 创建一个普通 Login
    CREATE LOGIN _test WITH PASSWORD='123', CHECK_POLICY = OFF
    GO
    
    -- 创建用户并设置默认架构为 TEST
    CREATE USER _test WITH DEFAULT_SCHEMA = TEST
    GO
    -- 授予执行存储过程的权限
    GRANT EXECUTE ON SCHEMA::TEST TO _test
    GO
    
    -- 使用 TEST 身份执行
    EXECUTE AS LOGIN = '_test'
    GO
    EXEC sp_name	-- 这里面是成功的
    GO
    REVERT
    GO
    
    -- 将 TEST 加入管理员角色
    EXEC sp_addsrvrolemember _test, sysadmin
    GO
    EXECUTE AS LOGIN = '_test'
    GO
    EXEC sp_name
    /*-- 这下不成功了,出现错误
    消息 2812,级别 16,状态 62,第 25 行
    Could not find stored procedure 'sp_name'.
    --*/
    GO
    REVERT
    GO
    
    DROP USER _test
    DROP LOGIN _test;
    

    • 已标记为答案 Wving5 2014年9月30日 5:57
    2014年9月30日 1:43
  • 最后这个,感觉是 BUG了, CREATE USER 关联的 LOGIN 不能是 sa, 但可以改为 sa, 当然, sa 是 sysadmin 成员,按照第2个的测试验证,是认不到默认架构的

    -- 创建一个普通 Login
    CREATE LOGIN _test WITH PASSWORD='123', CHECK_POLICY = OFF
    GO
    
    -- 创建用户并设置默认架构为 TEST
    CREATE USER _test WITH DEFAULT_SCHEMA = TEST
    GO
    ALTER USER _test WITH LOGIN = sa;
    GO
    -- 授予执行存储过程的权限
    GRANT EXECUTE ON SCHEMA::TEST TO _test
    GO
    
    -- 使用 TEST 身份执行
    EXECUTE AS LOGIN = 'sa'
    GO
    EXEC sp_name	-- 这里面是成功的
    GO
    REVERT
    GO
    
    DROP USER _test
    DROP LOGIN _test;
    

    • 已标记为答案 Wving5 2014年9月30日 6:45
    2014年9月30日 1:46

全部回复

  • Usually sa is mapped to dbo in user db with default schema set to dbo, that's why you have to specify schema when call sp. If db user has TEST as default schema, it can reference any object in TEST schema with object name only.
    • 已标记为答案 Wving5 2014年9月30日 5:57
    2014年9月29日 18:57
  • 没有 2008 的环境,在 2008R2 的环境下测试, sa 是无法映射为数据库用户的(dbo除外,但dbo无法修改默认架构, 如果 2008 是同样的情况,那么不知道楼主是如何修改默认架构的)

    -- sa 为 dbo 的情况下, 无法修改 dbo 的默认架构
    ALTER USER dbo WITH DEFAULT_SCHEMA = TEST
    /*-- 错误信息
    消息 15150,级别 16,状态 1,第 30 行
    Cannot alter the user 'dbo'.
    --*/
    GO
    -- sa 不是 dbo 的情况下,无法创建与 sa 关联的 USER
    CREATE USER sa FOR LOGIN sa
    /*-- 错误信息
    消息 15405,级别 16,状态 1,第 36 行
    Cannot use the special principal 'sa'.
    --*/
    GO
    -- 尝试使用非 sa 的 USER 关联 sa, 也是不成功的
    CREATE USER sa_test FOR LOGIN sa
    /*-- 错误信息
    消息 15405,级别 16,状态 1,第 42 行
    Cannot use the special principal 'sa'.
    --*/
    GO
    

    2014年9月30日 1:37
  • 用普通用户测试, 发现 sysadmin 成员无法认到默认架构,不知道 sa 的原因是否是因为这个(sa 是 sysadmin 角色成员)

    -- 创建一个普通 Login
    CREATE LOGIN _test WITH PASSWORD='123', CHECK_POLICY = OFF
    GO
    
    -- 创建用户并设置默认架构为 TEST
    CREATE USER _test WITH DEFAULT_SCHEMA = TEST
    GO
    -- 授予执行存储过程的权限
    GRANT EXECUTE ON SCHEMA::TEST TO _test
    GO
    
    -- 使用 TEST 身份执行
    EXECUTE AS LOGIN = '_test'
    GO
    EXEC sp_name	-- 这里面是成功的
    GO
    REVERT
    GO
    
    -- 将 TEST 加入管理员角色
    EXEC sp_addsrvrolemember _test, sysadmin
    GO
    EXECUTE AS LOGIN = '_test'
    GO
    EXEC sp_name
    /*-- 这下不成功了,出现错误
    消息 2812,级别 16,状态 62,第 25 行
    Could not find stored procedure 'sp_name'.
    --*/
    GO
    REVERT
    GO
    
    DROP USER _test
    DROP LOGIN _test;
    

    • 已标记为答案 Wving5 2014年9月30日 5:57
    2014年9月30日 1:43
  • 最后这个,感觉是 BUG了, CREATE USER 关联的 LOGIN 不能是 sa, 但可以改为 sa, 当然, sa 是 sysadmin 成员,按照第2个的测试验证,是认不到默认架构的

    -- 创建一个普通 Login
    CREATE LOGIN _test WITH PASSWORD='123', CHECK_POLICY = OFF
    GO
    
    -- 创建用户并设置默认架构为 TEST
    CREATE USER _test WITH DEFAULT_SCHEMA = TEST
    GO
    ALTER USER _test WITH LOGIN = sa;
    GO
    -- 授予执行存储过程的权限
    GRANT EXECUTE ON SCHEMA::TEST TO _test
    GO
    
    -- 使用 TEST 身份执行
    EXECUTE AS LOGIN = 'sa'
    GO
    EXEC sp_name	-- 这里面是成功的
    GO
    REVERT
    GO
    
    DROP USER _test
    DROP LOGIN _test;
    

    • 已标记为答案 Wving5 2014年9月30日 6:45
    2014年9月30日 1:46
  • 额 我错了  图省事拿SA举个栗子的……没想到这么严谨
    2014年9月30日 5:56
  • 非常感谢! 真的是这样  你研究的很深入 

    看到有说法是:如果服务器角色是sysadmin,就会对应用户dbo  //这个优先级貌似很高

    我还准备把原架构的sp都导入到dbo里面了… 压根就没往这块想…囧



    • 已编辑 Wving5 2014年9月30日 6:53
    2014年9月30日 6:50