December 8, 2011 at 8:51 am
Long time ago I wrote a sp to get all the sp in database and assign them execution privilege to certain role.
Couldn't find the sp now. Before rewrite it, I am just wondering if SQL has the function to let DBA do this kind of job?
December 8, 2011 at 8:57 am
I would loop through the system tables
something like this
SELECT
'GRANT EXECUTE ON '+
obj.name +
' TO RoleName;'
FROM
sysobjects obj
WHERE
xtype = 'p'
Returns a single column, then its just copy and paste into the query window and execute
December 8, 2011 at 9:00 am
It sounds like you were looping through every stored proc name;
if the role is going to have EXECUTE priviledges on all procs, without exception, i think you can simply do this:
CREATE ROLE MyRole
GRANT EXECUTE TO MyRole
that way any new procs, or procs that are dropped and recreated automatically fall under the roles EXECUTE ability..
Only if execute is being limited to specific objects would you want to loop thru them all.
--edit: anthony gave a fine example on how to do it for all procs--
Lowell
December 8, 2011 at 9:06 am
you could just do grant execute on database::databasename to rolename if its going to be for all procs in that database forgot about that
edit
ah that works too Lowell, didnt know that thought you had to be somewhat specific.
December 8, 2011 at 9:25 am
Lowell (12/8/2011)
It sounds like you were looping through every stored proc name;if the role is going to have EXECUTE priviledges on all procs, without exception, i think you can simply do this:
CREATE ROLE MyRole
GRANT EXECUTE TO MyRole
Really? I just did this before I saw your post, I don't think it is working although it should, it ran with no exception, but privilege was not granted.
Here is my sp:
--this sp will go through all the sp and fn and grant exec to PergamumUser
Alter proc spBulkGrantPrivilege
AS
declare @name varchar(100)
declare @RowNum int
declare @sql varchar(255)
create table #t
(
[Name] varchar(100)
)
declare c cursor for
SELECT name
FROM sys.procedures
where [name] like 'sp%'
UNION
SELECT name
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%' order by [name]
OPEN c
FETCH NEXT FROM c
INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
print @sql
set @sql = 'Use Pergamum; GRANT EXECUTE ON ' + @name + ' TO PergamumUser;'
exec (@sql)
FETCH NEXT FROM c
INTO @name
END
CLOSE c
DEALLOCATE c
Works for majority of the sp and fn but throws out some exception:
Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.
Can't see there is any difference for the unsuccessful objects.
December 8, 2011 at 9:32 am
you wont be able to see anything in the securables section unless you search for a type of database.
i just did the same as Lowell and looked at my test user, securables was blank, so i searched for database and it was granted execute on the db
December 8, 2011 at 9:35 am
I dislike global grants, so while Lowell's solution is the simplest, I prefer that you specifically grant to each sproc/function, as Anthony showed.
December 8, 2011 at 9:37 am
halifaxdal (12/8/2011)
Works for majority of the sp and fn but throws out some exception:Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.
Can't see there is any difference for the unsuccessful objects.
I just tried to do this on a CLR function and got the same error, so looks like you cant assign permissons directly on CLR functions
December 8, 2011 at 9:39 am
how did you test that? did you grant anything to, say a public or other role the user might be in;
this worked perfectly fine for me:
--Create the Roles used to control permissions
CREATE ROLE MyRole
--grant the role desired permissions; in this case, noting more than touchy the procs
GRANT EXECUTE TO MyRole
--create a user for testing
CREATE USER ClarkKent WITHOUT LOGIN;
--add the user to the role
EXEC sp_addrolemember 'MyRole', 'ClarkKent'
--change from superman to the testuser:
EXECUTE AS USER='ClarkKent'
--am i still superman, or am I Clark Kent Now?
SELECT USER_NAME() AS sUserName
--ok try and touch some tables.
select * from sys.tables
--i see no tables
select * from sys.objects
--whoa! i see a bunch of procs and functions!
EXEC pr_ScriptRoles
--change back into superman
REVERT; --sa or some admin on the server, right?
Lowell
December 8, 2011 at 9:43 am
Sorry Lowell is that to myself or to Halifaxdal?
December 8, 2011 at 9:45 am
anthony.green (12/8/2011)
Sorry Lowell is that to myself or to Halifaxdal?
wow you guys posted a lot of stuff while i tested my script;
when i thought i was posting, there were only 4 posts total.i was refering to halifax's issue where the OP said it didn't work for granting/limiting permissions.
Lowell
December 8, 2011 at 11:39 am
anthony.green (12/8/2011)
halifaxdal (12/8/2011)
Works for majority of the sp and fn but throws out some exception:Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.
Can't see there is any difference for the unsuccessful objects.
I just tried to do this on a CLR function and got the same error, so looks like you cant assign permissons directly on CLR functions
No, actually the code works fine, you just need to make a little check on the object type: if it's sp, then EXECUTE; if it's fn, then it's SELECT.
Here is a modified version, I add a parameter for the @Role so others come across this post can use it for their own purpose:
--this sp will go through all the sp and fn and grant exec or select to @Role
alter proc spBulkGrantPrivilege (@Role varchar(20))
AS
declare @name varchar(100)
declare @type varchar(2)
declare @sql varchar(1000)
create table #t
(
[Name] varchar(100),
[Type] varchar(2)
)
declare c cursor for
SELECT name, 'sp'
FROM sys.procedures
where [name] like 'sp%'
UNION
SELECT name, 'fn'
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%' order by [name]
OPEN c
FETCH NEXT FROM c
INTO @name, @type
WHILE @@FETCH_STATUS = 0
BEGIN
if (@type = 'sp')
set @sql = 'if not exists(SELECT * FROM sys.database_permissions p ' +
' INNER JOIN sys.all_objects o ON p.major_id = o.[object_id] AND o.[name] = ' + '''' + @name + '''' +
' INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id AND dp.[name] = ' + '''' + @Role + '''' + ')' +
' GRANT EXECUTE ON ' + @name + ' TO ' + @Role
else
set @sql = 'if not exists(SELECT * FROM sys.database_permissions p ' +
' INNER JOIN sys.all_objects o ON p.major_id = o.[object_id] AND o.[name] = ' + '''' + @name + '''' +
' INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id AND dp.[name] = ' + '''' + @Role + '''' + ')' +
' GRANT SELECT ON ' + @name + ' TO ' + @Role
exec (@sql)
FETCH NEXT FROM c
INTO @name, @type
END
CLOSE c
DEALLOCATE c
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply