Grant Exec permissions to all SPROCS 2 1

[mtorpy]

I think this is for SQL Admin forum......

I am trying to write a script to grant EXEC permissions on all non-system stored procedures on all DB's to a specific user 'QATest'

The below code will do this successfully but I need a way of writing it so it will run on ALL databases. Avoiding writing the same code for all db's on the system.

Any help?

---------------
declare @sql nvarchar(4000)
declare @db sysname ; set @db = 'Test32' --Change DB NAME
declare @u sysname ; set @u = QUOTENAME('QATest')

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'

PRINT 'Adding EXEC Stored Procedure permissions to ' + @u + ' on data base ' + @db

exec master.dbo.xp_execresultset @sql,@db

 

[Catadmin]

I don't usually say this, but use a cursor.

You'll want to play with your code. You can pull up the Procs from SysObjects where XType = P and Category <> 2. Then just use the naming convention (you'll have to fiddle with making your code string dynamic sql & executing that variable) @DBName.dbo.SysObjects.

Declare @DBName varchar(300);

declare DBCur CURSOR for Select Name from master.dbo.sysdatabases

Open DBCur

Fetch Next from DBCur into @DBName

While @@Fetch_Status = 0

BEGIN

... Insert your code here, setting @db to equal @DBName


END

Close DBCur

Deallocate DBCur

I'm not going to do the middle part for you. I'll leave that for you to have fun with. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mtorpy]

OK what about something like this??? it is not working so it might not be possible?? Mr.Denny any help??

EXEC sp_MSforeachdb
@command1 = 'IF ''?'' NOT IN(''master'', ''model'',''tempdb'',''msdb'')
IF EXISTS (select * from ?..sysusers where name = ''QATest'' and issqluser = 1)
begin

declare @sql nvarchar(4000)

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

declare @db sysname
set @db = ?

exec ?.dbo.xp_execresultset @sql,@db
end'

 

[mtorpy]

OK I still can not get this to run and its driving me crazy, can I have two EXEC statments in the script and still have it work?

 

[Catadmin]

In my experience, Exec scripts in QA usually have to be separated by a GO statement.

I'm not quite understanding your script here. Is this inside a cursor or a job?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mtorpy]

OK I GOT IT....

EXEC sp_MSforeachdb
@command1 = 'IF ''?'' NOT IN(''master'', ''model'',''tempdb'',''msdb'')
begin
declare @u sysname
set @u = QUOTENAME(''QATest'')

declare @sql nvarchar(4000)
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''


PRINT ''Adding [EXEC] permissions to all nonsystem stored procedures to user '' + @u + '' on database '' + ''[?]''
exec master.dbo.xp_execresultset @sql,''?''

end'