Re: Least privilege to execute stored procedures



Here is a SQL Script I wrote to do that ... It uses same SQL Statement as
Erland Suggested.

EXEC dbo.sp_addrole @rolename = N'udbr_SPPermissions'
GO
EXEC sp_addrolemember N'udbr_DatabaseRoleName', N'DOMAIN\UserName'
GO

SELECT 'GRANT EXECUTE ON ' + name + ' TO udbr_SPPermissions' AS SQLStatement
INTO #NewPermissions
FROM sysobjects
WHERE xtype = 'P'
AND ( name LIKE 'sp[_]%'
OR name LIKE 'up[_]%')
ORDER BY name
GO

DECLARE @SQLStatement VARCHAR(512)

DECLARE SPPermissions CURSOR FOR

SELECT SQLStatement
FROM #NewPermissions

OPEN SPPermissions

FETCH NEXT
FROM SPPermissions
INTO @SQLStatement

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT 'Executing: ' + @SQLStatement

EXEC (@SQLStatement)

FETCH NEXT
FROM SPPermissions
INTO @SQLStatement

END

CLOSE SPPermissions
DEALLOCATE SPPermissions
GO

First it creates the role, then it gives user access to the role. Then
Grants permissions to the role to execute all the SP. :).

For DDL opeartions (depending on how much), I tend to use Context Switching.

Thanks.
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/


"zz12" wrote:

In general is it better to create a role and assign the execute permissions
on the stored procedures and then assign the user to that role or assign
each stored procedure like you described below? Nothing major as of this
moment I think but just wondering.

Thanks for all of the replies here guys, much appreciated.


"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns9B505EA5753EYazorman@xxxxxxxxxxxx
zz12 (IDontLikeSpam@xxxxxxxxxxx) writes:
Hmmm...actually it looks like granting them the 'db_owner' role seems to
be the only way they can execute the ddl stored procedures.

Not the only. Maybe the quickest, but definitely not the best. After all,
it's better to grant them permission for each and every procedure. Which
can be achieved with:

SELECT 'GRANT EXECUTE ON ' + quotename(name) + TO someuser
FROM sysobjects
WHERE type = 'P'

In SQL 2005, you can grant EXECUTE permissions on database or schema
level.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx




.



Relevant Pages

  • Re: basic question
    ... Currently we have are doing calculations via stored procedures ... in which case *I* would like to have them run on the SQL server ... procs are doing. ...
    (microsoft.public.dotnet.general)
  • Re: Frage zu View-Tabellen
    ... eigenes Element in SQL. ... Erweiterungen sind indizierte Sichten (SQL Server 2000), ... Performance und vom Speicherverbrauch ein Unterschied, ob ich eine Anfrage auf eine View-Tabelle oder eine Stored Procedure schicke? ... Bei Stored Procedures wird der kompilierte Ausführungsplan im Cache gespeichert, der bei Zugriffen auf die View in der Regel neu erstellt wird. ...
    (microsoft.public.de.sqlserver)
  • Re: Stored Procedure vs direct execute SQL
    ... another of dynamic sql (otherwise SQL Server can't take advantage of ... You seem to be saying that SQL Server can use ... To say that stored procedures are "far ... > select listingId, listingName from Property ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Cannot open SQL 2000 default database
    ... 1> EXEC sp_default 'mylogin', ... I have to type EXIT or type ctrl-C to get out of the SQL editor. ... error as I did before the error, as though I didn't type the command prompt ... I assume here that you run OSQL on the SQL Server machine itself, ...
    (microsoft.public.sqlserver.connect)
  • Re: DB Architecture Questions (for joe celko)
    ... much more flexibility and scalability. ... Although in the reality I live in, stored procedures ... SQL Server, this means that you will have a lot of round trips for data ...
    (microsoft.public.sqlserver.programming)