Bulk Granting Permissions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Bulk Granting Permissions

Author

Topic

timgaunt
Posting Yak Master

115 Posts

Posted - 2006-09-15 : 13:00:22

Ok, the situation:

In the sites we develop we generally have a naming convention and only allow users access to the stored procedures (this is via a web front). As we add new SProcs I want to add the permissions (circa 10 Sprocs at a time) so I wrote/found this code to simplify the process:


DECLARE @username varchar(100)
SET @username = 'Username'

SELECT 
	'GRANT EXECUTE ON ' + name + ' TO ' + @username
FROM 
	sysobjects
WHERE 
	xtype = 'p'
  AND
	LEFT(name, 4) = 'xyz_'

Which is fine, I was just wondering whether I could completely automate it, I've tried this:


DECLARE @username varchar(100), @name varchar(100)
SET @username = ''

DECLARE cEvent CURSOR FAST_FORWARD
FOR SELECT 
	name
FROM 
	sysobjects
WHERE 
	xtype = 'p'
  AND
	LEFT(name, 4) = 'xyz_'

OPEN cEvent
FETCH cEvent INTO @name

	WHILE @@Fetch_Status = 0
	BEGIN
		GRANT EXECUTE ON @name TO @username
		FETCH cEvent INTO @name
	END

CLOSE cEvent
DEALLOCATE cEvent

But it just complains about @name in GRANT EXECUTE ON @name TO @username.

I know I perhaps shouldn't do it like this but is it possible?

Thanks,

Tim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-09-15 : 13:04:28

Here's one that I wrote. Modify it to your liking:


CREATE       PROC dbo.isp_Grant_Permissions
AS

SET NOCOUNT ON

DECLARE @objName varchar(80)
DECLARE @objType char(2)
DECLARE grant_perms_on_sps CURSOR FOR	
SELECT name, type
FROM SYSOBJECTS 
WHERE 	
	(	
		(type = 'P' AND name LIKE 'usp[_]%')
		OR
		(type = 'FN' AND name LIKE 'udf[_]%')
		OR
		(type = 'TF' AND name LIKE 'udf[_]%')
		OR
		(type = 'U')
		OR
		(type = 'V' AND name LIKE 'v[_]%')
	)
	AND 
		uid = 1
	AND
		status > -1

OPEN grant_perms_on_sps
FETCH NEXT FROM grant_perms_on_sps 
INTO @objName, @objType

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @objType = 'P' OR @objType = 'FN'
		EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO GT_Apps')

	IF @objType = 'TF'
		EXEC ('GRANT SELECT ON dbo.' + @objName + ' TO GT_Apps')

	FETCH NEXT FROM grant_perms_on_sps 
	INTO @objName, @objType
END

CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps

GO

Tara Kizer

timgaunt
Posting Yak Master

115 Posts

Posted - 2006-09-15 : 13:57:33

Fantastic, thanks Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources