Re: Least privilege to execute stored procedures
- From: Mohit K. Gupta <mohitkgupta@xxxxxxx>
- Date: Mon, 10 Nov 2008 11:42:05 -0800
Here is a SQL Script I wrote to do that ... It uses same SQL Statement as
EXEC dbo.sp_addrole @rolename = N'udbr_SPPermissions'
EXEC sp_addrolemember N'udbr_DatabaseRoleName', N'DOMAIN\UserName'
SELECT 'GRANT EXECUTE ON ' + name + ' TO udbr_SPPermissions' AS SQLStatement
WHERE xtype = 'P'
AND ( name LIKE 'sp[_]%'
OR name LIKE 'up[_]%')
ORDER BY name
DECLARE @SQLStatement VARCHAR(512)
DECLARE SPPermissions CURSOR FOR
WHILE @@FETCH_STATUS = 0
PRINT 'Executing: ' + @SQLStatement
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.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
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
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
WHERE type = 'P'
In SQL 2005, you can grant EXECUTE permissions on database or schema
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