December 2, 2004 at 2:00 am
Hi.
Is there a way to grant permission to all the objects owned by a particular user, without having to grant permission on a per object level? Example UserX is the owner of 30 Stored Procedures and wants to grant exec permission to UserY on all of them. Rather than having to grant exec privilege on each one (one by one) is there a way to grant exec on all of them?
Thanks
dullah
December 2, 2004 at 2:41 am
Grant permissions to a role rather than a user and then assign users to the role. A user is more likely to leave than a role is to become obsolete.
USE MyDatabase
GO
DECLARE @sProcName SysName
SET @sProcName = ''
WHILE @sProcName IS NOT NULL
BEGIN
SELECT @sProcName = MIN(Name)
FROM SysObjects
WHERE Type='P' AND
Name > @sProcName AND
Name LIKE 'usp%'
PRINT @sProcName
IF @sProcName IS NOT NULL
EXEC('GRANT EXEC ON ' + @sProcName + ' TO MYROLE')
END
December 29, 2004 at 2:53 am
Thanx David
dullah
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply