可以的,测试代码如下,供参考.--新建Login CREATE LOGIN [tlogin] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[CC], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF--打开数据库 USE CC--新建User CREATE USER [tlogin] FOR LOGIN [tlogin]--授权可查看(view)的存储过程(spname)给该User GRANT VIEW DEFINITION ON [dbo].[spname] TO [tlogin]
USE [master] GO CREATE LOGIN [TUser] WITH PASSWORD=N'1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE [Test] GO CREATE USER [TUser] FOR LOGIN [TUser] GOGRANT EXECUTE TO [TUser] GO--測試CREATE PROCEDURE p AS SELECT 1GO EXECUTE AS LOGIN='TUser' go EXEC p
关键是这句: GRANT VIEW DEFINITION ON [dbo].[spname] TO [tlogin]
CREATE LOGIN [tlogin] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[CC], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF--打开数据库
USE CC--新建User
CREATE USER [tlogin] FOR LOGIN [tlogin]--授权可查看(view)的存储过程(spname)给该User
GRANT VIEW DEFINITION ON [dbo].[spname] TO [tlogin]
GO
CREATE LOGIN [TUser] WITH PASSWORD=N'1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Test]
GO
CREATE USER [TUser] FOR LOGIN [TUser]
GOGRANT EXECUTE TO [TUser]
GO--測試CREATE PROCEDURE p
AS
SELECT 1GO
EXECUTE AS LOGIN='TUser'
go
EXEC p
GRANT VIEW DEFINITION ON [dbo].[spname] TO [tlogin]