execresultset does this exist? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

execresultset does this exist?

Author

Topic

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-12 : 00:38:35

does anyone know if this

execresultset

exists, ive created a table with queries using this:

declare @sql nvarchar(4000)
declare @db sysname ; set @db = DB_NAME()
declare @u sysname ; set @u = QUOTENAME('<insert_username>')

set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @u + ''' FROM INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'

but im trying to execute each result in the table

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-12 : 01:20:19

I think this will do what you want:

declare @sql nvarchar(4000)
declare @db sysname ; set @db = DB_NAME()
declare @u sysname ; set @u = QUOTENAME('<insert_username>')

--
-- accumulates a grant script for each routine in a string
--
select @sql = coalesce(@sql, '') + 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO ' + @u + '
' FROM INFORMATION_SCHEMA.ROUTINES WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0

print @sql -- paste the result into QA after you have inspected for correctness

http://www.elsasoft.org

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-12 : 01:26:26

thats just printing them not actually executing each row

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-12 : 01:30:21

i dont want to cut paste results manually, i want it to run each result automatically

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-12 : 01:36:14

How difficult is it for you to replace Print with EXEC ?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 01:39:25

Try this

declare	@routines table (rowid int identity(1,1), routine nvarchar(4000))

insert	@routines
	(
		routine
	)
SELECT	QUOTENAME(ROUTINE_SCHEMA) + '.' + QUOTENAME(ROUTINE_NAME)
FROM	INFORMATION_SCHEMA.ROUTINES
WHERE	OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0

declare	@rowid int,
	@sql nvarchar(4000)

select	@rowid = max(rowid)
from	@routines

while @rowid >= 1
	begin
		select	@sql = 'GRANT EXEC ON ' + routine + ' TO ' + QUOTENAME(@u)
		from	@routines
		where	rowid = @rowid

		print @sql
		exec sp_executesql @sql

		select @rowid = @rowid - 1
	end

How does it feel to have tutors of your own?

Peter Larsson
Helsingborg, Sweden

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-12 : 01:41:43

it feels good i love this forum... why?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 01:41:45

quote:
Originally posted by harsh_athalye

How difficult is it for you to replace Print with EXEC ?

But of course, rnbguy must add a ";" after (or before) each statement for the exec to run

Peter Larsson
Helsingborg, Sweden

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-12 : 01:48:59

deny exec on [dbo].[grants] TO [WebApp];
deny exec on [dbo].[CreateLogin] TO [WebApp];
deny exec on [dbo].[ExecResultSet] TO [WebApp];
Msg 203, Level 16, State 2, Line 10
The name 'deny exec on [dbo].[grants] TO [WebApp];
deny exec on [dbo].[CreateLogin] TO [WebApp];
deny exec on [dbo].[ExecResultSet] TO [WebApp];
' is not a valid identifier.

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-12 : 01:49:36

thats for jezmens way going to try peso's now

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-12 : 01:52:21

okay that way worked now i gotta figure out how to make it do it for all sp in all dbs not just one

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-12 : 01:59:41

trying this:

declare @routines table (rowid int identity(1,1), routine nvarchar(4000))
declare @u sysname ; set @u = QUOTENAME('WebApp')

insert @routines
(
routine
)

exec sp_msforeachdb
"USE ? select name
from sysobjects
where type='P' AND category='0'"

declare @rowid int,
@sql nvarchar(4000)

select @rowid = max(rowid)
from @routines

while @rowid >= 1
begin
select @sql = 'DENY EXEC ON ' + routine + ' TO WebApp'
from @routines
where rowid = @rowid

print @sql
exec sp_executesql @sql

select @rowid = @rowid - 1
end

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-12 : 18:50:49

DENY EXEC ON TW_TNS_reporting TO WebApp
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'TW_TNS_reporting', because it does not exist or you do not have permission.

the thing is that does exist!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 18:55:47

And the other reason mentioned in the error message?

Peter Larsson
Helsingborg, Sweden

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-12 : 19:01:40

well its not setting the permission because it cant find TW_TNS_reporting
im assuming because its looking in the master db, not the correct database.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 19:27:06

[code]create table #routines
(
rowid int identity(1,1),
dbname nvarchar(100),
routine nvarchar(1000)
)

declare @u sysname

select @u = QUOTENAME('WebApp')

insert #routines
(
dbname,
routine
)
exec sp_msforeachdb "select '?', name from ?..sysobjects where type = 'P' AND category = 0"

declare @rowid int,
@sql nvarchar(4000)

select @rowid = max(rowid)
from #routines

while @rowid >= 1
begin
select @sql = 'DENY EXEC ON ' + dbname + '..' + routine + ' TO ' + @u
from #routines
where rowid = @rowid

print @sql
exec sp_executesql @sql

select @rowid = @rowid - 1
end

drop table #routines[/code]

Peter Larsson
Helsingborg, Sweden

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-17 : 19:05:49

thanx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources