主页 / dba / 问题 / 340153
Accepted
variable
Asked: 2024-06-12 07:21:56 +0800 CST

授予执行存储过程（全部）是否隐式允许任何关键权限？

  • 772

授予执行存储过程（全部）权限是否隐式允许任何关键权限？例如，通过运行相关存储过程来控制安全性等的权限？

授予执行所有存储过程的权限的方法是：

GRANT EXECUTE TO [user]
sql-server

1 个回答

  1. Best Answer

    你的问题的答案是“否”。

    它记录在 Microsoft 文档中。

    限制和约束

    您不能使用 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
    • 1

相关问题