您不能使用 SQL Server Management Studio 授予系统过程或系统函数的权限。请改用 GRANT 对象权限。
您可以使用以下代码进行测试。我在 SQL 2022 CU13 中进行了测试。
--create a test database
USE [master];
GO
CREATE DATABASE [test]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\test.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\test_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
WITH LEDGER = OFF;
GO
--create two logins
USE [master]
GO
CREATE LOGIN [testgrantexecute] WITH PASSWORD=N'testgrantexecute', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
CREATE LOGIN [testgrantexecute1] WITH PASSWORD=N'testgrantexecute1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
--create user from the two logins
USE [test]
GO
CREATE USER [testgrantexecute] FOR LOGIN [testgrantexecute];
GO
CREATE USER [testgrantexecute1] FOR LOGIN [testgrantexecute1];
GO
--create a stored procedure
CREATE TABLE [dbo].[test](
[a] [int] IDENTITY(1,1) NOT NULL, [b] [int] NULL, [c] [int] NULL,
PRIMARY KEY CLUSTERED
(
[a] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx] ON [dbo].[test]
(
[c] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE proc [dbo].[sp1] @a int
as
BEGIN
select * from dbo.test where c=@a order by b
END
GO
--Grant execute on all to user testgrantexecute
GRANT EXECUTE TO [testgrantexecute];
GO
--Execute as user testgrantexecute
EXECUTE AS USER ='testgrantexecute'
--can execute user create stored procedure
EXEC test.dbo.sp1 @a=100;
GO
--cannot execute system stored procedure
EXEC sp_addrolemember 'db_owner', 'testgrantexecute1';
GO
REVERT;
--cleanup
USE [master]
GO
DROP DATABASE [test]
GO
你的问题的答案是“否”。
它记录在 Microsoft 文档中。
您可以使用以下代码进行测试。我在 SQL 2022 CU13 中进行了测试。