none
请教朋友们一个关于SQL Server用户自定义存储过程授权的问题。 RRS feed

  • 问题

  • 一个存储过程,其中语句包含对本地表的更新、追加等操作,也包含本地跨数据库对表的更新、追加等操作,还包含通过连接服务器对远程SQL SERVER存储过程的调用。请问我是否需要把存储过程中本地所涉及的对象(表、视图)授予UPDATE、INSERT、DELETE权限呢?远程存储过程中所涉及的对象(表、视图)是否也需要授予UPDATE、INSERT、DELETE权限?

    我尝试在远程服务器创建用户user1,并将存储过程TestProc授予user1 EXECUTE权限,在本地创建用户user2,使用user1与user2建立连接服务器,在本地使用EXEC linkservername.dbname.dbo.TestProc调用,提示我TestProc中的某个对象没有权限访问,不知是何原因啊? 谢谢啦~


    never

    2013年10月30日 16:12

答案

  • 检查链接服务器的安全配置

    如果你使用的是指定用户,那么对这个用户授权就好了,与调用者无关

    如果你使用是当前合建上下文,那么调用存储过程的用户需要在远种服务器上授权

    如果你使用的是登陆映射,那么你需要确定调用者映射的远程用户是那个,这个远程用户是否有被授权

    2013年11月1日 8:02

全部回复

  • For local objects, don't need grant additional permission if they are in same schema with sp. Yes, have to grant proper permission for remote objects.
    2013年10月30日 16:42
  • 楼上讲的很清楚,本地相同Schema的不用,因为有Ownership Chains,远程的保证创建Linked Server的账户有访问远程对象的权限。


    Please Mark As Answer if it is helpful.

    2013年10月31日 1:20
  • 1.  与存储过程同一个库的对象,只要有调用存储过程的权限,就可以了,不需要单独授权

    2. 不是同一个库的对象(跨数据库),权限跟存储过程的Owner和调用者都可能有关,参考:

    http://msdn.microsoft.com/zh-cn/library/vstudio/bb669059.aspx

    3. 链接服务器,与你配置的链接服务器安全认证方式有关,这个方式决定你需要授权的用户到底是那一个

    2013年10月31日 1:28
  • 比如:

    本地服务器中含用户locaUser,过程locaProc1,并授予locaUser执行权限。

    远程服务器中含用户remoUser,数据库remoDB1,表remoTable1,并未对remoUser授权。

    使用locaUser与remoUser在本地创建连接服务器linkServer。

    locaProc1含命令select * from linkServer.remoDB1.dbo.remoTable1

    在本地使用locaUser执行locaProc1 会提示过程 sp_getschemalock,第 1 行 拒绝了对对象 'remoTable1' (数据库 'remoDB1',架构 'dbo')的 SCHEMA LOCK 权限。

    我想实现在本地无法对远程表直接执行查询,但可通过调用本地存储过程进行远程数据的查询,请问有办法吗?


    never

    2013年11月1日 6:29
  • 检查链接服务器的安全配置

    如果你使用的是指定用户,那么对这个用户授权就好了,与调用者无关

    如果你使用是当前合建上下文,那么调用存储过程的用户需要在远种服务器上授权

    如果你使用的是登陆映射,那么你需要确定调用者映射的远程用户是那个,这个远程用户是否有被授权

    2013年11月1日 8:02
  • 多谢,我采用登陆映射的方式,远程用户对表授权后,执行本地过程访问远程表没有问题,但问题是我同样可以在本地直接执行select * from linkServer.remoDB1.dbo.remoTable1访问远程表的数据,有办法避免吗?


    never

    2013年11月1日 9:31
  • Can't avoid that because you need read permission on remote table to make sp work.
    2013年11月1日 13:49
  • 如果你仅仅是想存储过程中可以访问,直接查询不可以,那么你本地用户(映射到远程的那个),与你调用存储过程的不要用同一个,然后在存在过程中使用 execute as 指定访问链程数据时的用户身份,不与调用者一样
    2013年11月4日 2:34
  • --步骤一:登录本地创建用户
    USE [master]
    GO
    CREATE LOGIN [locaUser] WITH PASSWORD=N'passWord', DEFAULT_DATABASE=[locaDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    CREATE LOGIN [locaAdmin] WITH PASSWORD=N'passWord', DEFAULT_DATABASE=[locaDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    USE [locaDB]
    GO
    CREATE USER [locaUser] FOR LOGIN [locaUser]
    CREATE USER [locaAdmin] FOR LOGIN [locaAdmin]
    EXEC sp_addrolemember N'db_owner', N'locaAdmin'
    GO
    --步骤二:登录远程创建用户
    USE [master]
    GO
    CREATE LOGIN [locaUser] WITH PASSWORD=N'passWord', DEFAULT_DATABASE=[remoDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    CREATE LOGIN [locaAdmin] WITH PASSWORD=N'passWord', DEFAULT_DATABASE=[remoDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    USE [remoDB]
    GO
    CREATE USER [locaUser] FOR LOGIN [locaUser]
    CREATE USER [locaAdmin] FOR LOGIN [locaAdmin]
    EXEC sp_addrolemember N'db_owner', N'locaAdmin'
    GO
    --步骤三:登录本地创建连接服务器
    USE [master]
    GO
    SP_ADDLINKEDSERVER @server = N'linkserver1',
       @srvproduct = N' ',
       @provider = N'SQLOLEDB', 
       @datasrc = N'192.168.1.1'
    GO
    SP_ADDLINKEDSRVLOGIN N'linkserver1', false, N'locaUser', N'locaUser', N'passWord'
    GO
    SP_ADDLINKEDSRVLOGIN N'linkserver1', false, N'locaAdmin', N'locaAdmin', N'passWord'
    GO
    --步骤四:locaAdmin登录本地创建过程访问远程表数据
    USE [locaDB]
    GO
    CREATE PROCEDURE [dbo].[SP_TEST] 
    AS
    EXECUTE AS LOGIN = 'locaAdmin'
    SELECT * FROM linkserver1.remoDB.dbo.table1
    GO
    --步骤五:locaAdmin登录本地对locaUser授权
    GRANT EXECUTE ON [dbo].[SP_TEST] TO [locaUser]
    --步骤六:locaUser登录本地执行SP_TEST
    EXEC SP_TEST --提示链接服务器 "linkserver1" 的 OLE DB 访问接口 "SQLNCLI" 不包含表 ""remoDB"."dbo"."table1""。该表不存在,或者当前用户没有访问该表的权限。

    请问是哪个步骤有问题呢?谢谢


    never

    2013年11月5日 6:34
  • 测试了一下,有点奇怪

    无论是

    CREATE PROCEDURE [dbo].[SP_TEST1234] 
    AS
    EXECUTE AS LOGIN = 'locaUser'
    SELECT * FROM linkserver1.pratice.dbo.UserInfo
    GO

    还是

    GRANT EXECUTE ON SP_TEST1234 TO [locaUser]

    都显示

    拒绝了对对象 'SP_TEST1234' (数据库 'pratice',架构 'dbo')的 EXECUTE 权限。

    除非用sysadmin角色的登录用户来执行


    2013年11月5日 16:53
  • 当我执行了这句的时候:GRANT EXECUTE ON SP_TEST1234 TO [locaUser]

    但是有新的错误

    消息 229,级别 14,状态 71,过程 sp_getschemalock,第 1 行
    拒绝了对对象 'UserInfo' (数据库 'pratice',架构 'dbo')的 SCHEMA LOCK 权限。

    唯有等楼下的高手回答了

    2013年11月5日 17:15
  • 一个存储过程,其中语句包含对本地表的更新、追加等操作,也包含本地跨数据库对表的更新、追加等操作,还包含通过连接服务器对远程SQL SERVER存储过程的调用。请问我是否需要把存储过程中本地所涉及的对象(表、视图)授予UPDATE、INSERT、DELETE权限呢?远程存储过程中所涉及的对象(表、视图)是否也需要授予UPDATE、INSERT、DELETE权限?

    我尝试在远程服务器创建用户user1,并将存储过程TestProc授予user1 EXECUTE权限,在本地创建用户user2,使用user1与user2建立连接服务器,在本地使用EXEC linkservername.dbname.dbo.TestProc调用,提示我TestProc中的某个对象没有权限访问,不知是何原因啊? 谢谢啦~


    Microsoft 脚本中心

    • 已建议为答案 TechNet 7 2013年11月5日 18:22
    2013年11月5日 18:21
  • 一个存储过程,其中语句包含对本地表的更新、追加等操作,也包含本地跨数据库对表的更新、追加等操作,还包含通过连接服务器对远程SQL SERVER存储过程的调用。请问我是否需要把存储过程中本地所涉及的对象(表、视图)授予UPDATE、INSERT、DELETE权限呢?远程存储过程中所涉及的对象(表、视图)是否也需要授予UPDATE、INSERT、DELETE权限?

    我尝试在远程服务器创建用户user1,并将存储过程TestProc授予user1 EXECUTE权限,在本地创建用户user2,使用user1与user2建立连接服务器,在本地使用EXEC linkservername.dbname.dbo.TestProc调用,提示我TestProc中的某个对象没有权限访问,不知是何原因啊? 谢谢啦~


    Microsoft 脚本中心



    Microsoft 脚本中心

    2013年11月5日 18:21
  • 一个存储过程,其中语句包含对本地表的更新、追加等操作,也包含本地跨数据库对表的更新、追加等操作,还包含通过连接服务器对远程SQL SERVER存储过程的调用。请问我是否需要把存储过程中本地所涉及的对象(表、视图)授予UPDATE、INSERT、DELETE权限呢?远程存储过程中所涉及的对象(表、视图)是否也需要授予UPDATE、INSERT、DELETE权限?

    我尝试在远程服务器创建用户user1,并将存储过程TestProc授予user1 EXECUTE权限,在本地创建用户user2,使用user1与user2建立连接服务器,在本地使用EXEC linkservername.dbname.dbo.TestProc调用,提示我TestProc中的某个对象没有权限访问,不知是何原因啊? 谢谢啦~


    Microsoft 脚本中心


    怎麽会建议为答案啊???
    2013年11月6日 1:11
  • 提的问题怎么标记为答案了啊?

    never

    2013年11月6日 1:17