quote:

---

Originally posted by lancepr

Do you have th code for that looping cursor?
I need to do a similar task

---

quote:

---

Originally posted by Mits

hi SQLServerDBA_Dan
i want to do exactly what you are doing thru your cursor. if possible could you please give me that code

Mits

---

----------------------------------------------------------------------------------------------------
-- OBJECT NAME		: isp_Grant_Permissions
--
-- AUTHOR		: Tara Duggan
-- DATE			: June 12, 2002
--
-- INPUTS		: None
-- OUTPUTS		: None
--
-- DEPENDENCIES		: None
--
-- APPLICATION(s)	: GT
--
-- DESCRIPTION		: This stored proc is used to grant permissions
--			  on all stored procedures that start with usp_,
--			  all functions that start with udf_,
--			  and all views that start with v_.
--                          
-- EXAMPLES (optional)	: EXEC isp_Grant_Permissions
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- MM/DD/YYYY - (Name)
-- (Description)
----------------------------------------------------------------------------------------------------

CREATE  PROC 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 NOT IN ('TF', 'T', 'V')
	BEGIN
		EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO GT_Apps')
	END

	ELSE
	BEGIN
		EXEC ('GRANT SELECT ON dbo.' + @objName + ' TO GT_Apps')	
	END

	FETCH NEXT FROM grant_perms_on_sps 
	INTO @objName, @objType
END

CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps

GO

DECLARE @GRANTWHAT as varchar(100),
	@GRANTEDROLE as varchar(50)
SET @GRANTWHAT = 'SELECT, INSERT, UPDATE, DELETE'
SET @GRANTEDROLE = 'rl_ACCOUNTING'

SELECT 'GRANT ' + @GRANTWHAT + ' ON ' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + ' TO ' + @GRANTEDROLE
from INFORMATION_SCHEMA.tables 
where TABLE_TYPE = 'BASE TABLE'

declare addpermis_cursor cursor
for
select name from <DATABASE>..sysobjects
where type = 'U' and name not like 'sys%' and name not like 'dt%' -- Selects all Objects in the DB that are user tables

open addpermis_cursor

declare @name varchar(40),
	@numofrows varchar(4)
select @numofrows = 0
fetch next from addpermis_cursor into @name  	-- Opens 1st variable into the @Name var from the cursor
declare @CMD varchar(255), 			-- String used to hold the SQL Query and Command Line
	@cmdstatus int  			-- Variable for XP_Cmdshell return status (-1 = fail or 0 = success)
while @@fetch_status = 0
 begin
   select @CMD = 'grant execute on ' + @name + ' to db_data_insertupdate'  	-- SQL Grant statement to grant permissions
   select @CMD = 'isql -E -S<SERVERNAME> -d<DATABASE> -Q"' + @CMD + '"'  -- SQL O/S Command line. -E Trusted Authentication | -S = Server | -d = DB Name | -Q = Query to run
   exec @CMDstatus = master..xp_cmdshell @CMD, no_output
   fetch next from addpermis_cursor into @name
   select @numofrows = @numofrows + 1
 end
if @CMDstatus = 0 print 'Addpermis_cursor has granted ' + @numofrows + ' objects!'
CLOSE addpermis_cursor
DEALLOCATE addpermis_cursor

quote:

---

Originally posted by tduggan

You can use my stored proc:

 code, code, blah, blah..

Just change the role name and tweak the object names if you don't have a strict naming convention like we do. Modifying the select in the cursor declaration will allow you to get the objects that you care about.

Tara

---

quote:

---

Originally posted by tduggan

I had to put it into a loop (I haven't changed it to a WHILE statement yet) so that I could schedule it on the dev server. Developers forget to add permissions when they create their objects, so I decided to just schedule a job to do it for them. It was much easier in my case. Other people use templates for the creation of their objects. If I could get them to use it, I wouldn't need to do this.

Tara

---

quote:

---

Originally posted by tduggan

So you grant INSERT/UPDATE/DELETE on tables? Does that mean you don't use stored procedures or your stored procedures use dynamic sql? Just curious of course.

Tara

---