Czy przyznać użytkownikowi uprawnienia do wykonywania wszystkich procedur składowanych w bazie danych?

104

Wygenerowałem skrypt ze starej bazy danych, utworzyłem nową bazę danych i zaimportowałem wszystkie dane ze starej bazy. Jak dotąd jednak żaden użytkownik nie ma uprawnień do wykonywania procedur składowanych. Wiem, że mogę użyć

GRANT EXECUTE ON [storedProcName] TO [userName]

Gdyby jednak było to tylko kilka procedur, mam ich około 100, więc jaki jest najłatwiejszy sposób, aby nadać wszystkim dostęp do wykonywania dla określonego użytkownika?

Z góry dziękuję.

sql sql-server sql-server-2000

— Nacięcie
źródło

115

Utwórz rolę, dodaj tę rolę do użytkowników, a następnie możesz przyznać wykonanie wszystkim procedurom za jednym razem dla tej roli.

CREATE ROLE <abc>
GRANT EXECUTE TO <abc>

EDYTUJ
To działa w SQL Server 2005, nie jestem pewien co do wstecznej kompatybilności tej funkcji, jestem pewien, że wszystko później niż 2005 powinno być w porządku.

— Sanjeevakumar Hiremath
źródło

Właśnie wypróbowałem to na SQL Server 2008 Standard (amazon RDS) i zadziałało to cudownie.

— datagod

czy mógłbyś podać przykład? powiedzmy, że muszę przyznać uprawnienia EXECUTE do wszystkich SP dla użytkownika SPExecuter

— Uri Abramson

4

jedyną potrzebną instrukcją jest linia dodająca użytkownika do roli, na przykład: ALTER ROLE [abc] ADD

— MEMBER

GRANT EXEC TO public

— Simon Hughes

27

W rzeczywistości nie musisz tworzyć roli, możesz to zastosować bezpośrednio do użytkownika, np. GRANT EXECUTE TO userName. Myślę, że to wystarczy na pytanie PO.

— Chris Peacock

25

Bez nadmiernego komplikowania problemu, aby udzielić EXECUTE na wybranej bazie danych:

USE [DB]
GRANT EXEC TO [User_Name];

— Bartosz X
źródło

1

zadziałało dla mnie i prawdopodobnie obejmuje wszystkie przyszłe przechowywane procesy (dowiemy się), a nie skrypty, które nazywają każdy przechowywany proces.

— Ken Forslund

19

Jest to rozwiązanie, które oznacza, że po dodaniu nowych procedur składowanych do schematu użytkownicy mogą je wykonywać bez konieczności wywoływania polecenia grant execute na nowej procedurze składowanej:

IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'asp_net')
DROP USER asp_net
GO

IF  EXISTS (SELECT * FROM sys.database_principals 
WHERE name = N'db_execproc' AND type = 'R')
DROP ROLE [db_execproc]
GO

--Create a database role....
CREATE ROLE [db_execproc] AUTHORIZATION [dbo]
GO

--...with EXECUTE permission at the schema level...
GRANT EXECUTE ON SCHEMA::dbo TO db_execproc;
GO

--http://www.patrickkeisler.com/2012/10/grant-execute-permission-on-all-stored.html
--Any stored procedures that are created in the dbo schema can be 
--executed by users who are members of the db_execproc database role

--...add a user e.g. for the NETWORK SERVICE login that asp.net uses
CREATE USER asp_net 
FOR LOGIN [NT AUTHORITY\NETWORK SERVICE] 
WITH DEFAULT_SCHEMA=[dbo]
GO

--...and add them to the roles you need
EXEC sp_addrolemember N'db_execproc', 'asp_net';
EXEC sp_addrolemember N'db_datareader', 'asp_net';
EXEC sp_addrolemember N'db_datawriter', 'asp_net';
GO

Odniesienie: Przyznaj uprawnienie do wykonywania wszystkich procedur składowanych

— Colin
źródło

6

użyj poniższego kodu, zmień odpowiednią nazwę bazy danych i nazwę użytkownika, a następnie weź te dane wyjściowe i wykonaj w SSMS. DLA SQL 2005 POWYŻEJ

USE <database_name> 
select 'GRANT EXECUTE ON ['+name+'] TO [userName]  '  
from sys.objects  
where type ='P' 
and is_ms_shipped = 0

— Hemanshu Trivedi
źródło

1

Aby uwzględnić procedury składowane CLR, należy również uwzględnić typ „PC”.

— Oleh Nechytailo

1

USE [DATABASE]

DECLARE @USERNAME VARCHAR(500)

DECLARE @STRSQL NVARCHAR(MAX)

SET @USERNAME='[USERNAME] '
SET @STRSQL=''

select @STRSQL+=CHAR(13)+'GRANT EXECUTE ON ['+ s.name+'].['+obj.name+'] TO'+@USERNAME+';'
from
    sys.all_objects as obj
inner join
    sys.schemas s ON obj.schema_id = s.schema_id
where obj.type in ('P','V','FK')
AND s.NAME NOT IN ('SYS','INFORMATION_SCHEMA')


EXEC SP_EXECUTESQL @STRSQL

— GCH
źródło