executing sp

Hi
I have sp in which i run dbcc inputbuffer.
Can I, and how grant permissions to somebody who
is not in sysadmin fixed server role to execute this sp?Hi,
No it is not possible.
DBCC INPUTBUFFER permissions default to members of the Sysadmin fixed server
role only, who can see any SPID. Other users can see any SPID they own.
Permissions are not transferable.
Thanks
Hari
MCDBA
<roman.ilic@.avtenta.si> wrote in message
news:efd1361.0403160208.7a8a3d9b@.posting.google.com...
> Hi
> I have sp in which i run dbcc inputbuffer.
> Can I, and how grant permissions to somebody who
> is not in sysadmin fixed server role to execute this sp?sql

EXECUTE permissions

I need to grant a user execute permissions on 1000 stored procedures. This
there any easy way to do this in SQL 2005?IF its 1000 procs among many procs, then you can do it the old fashioned way
.
execute this
select 'grant exec on ' + name + ' to <user_name>; go' from sysobjects where
type = 'p' and <your other filters here>
copy paste the result set and execute the result set. Hope this helps|||I know the old way, but I was hoping they had made it easier in 2005.
"Omnibuzz" wrote:

> IF its 1000 procs among many procs, then you can do it the old fashioned w
ay.
> execute this
> select 'grant exec on ' + name + ' to <user_name>; go' from sysobjects whe
re
> type = 'p' and <your other filters here>
> copy paste the result set and execute the result set. Hope this helps
>|||Do you have any particular way in mind that you are looking for.
Can you elaborate on the exact requirement.
Like doing it automatically and not have the manual intervention of copying
and pasting the result set. In that case you can use XP_EXECRESULTSET to do
it in one shot. it was available in SQL 2000 too.
--
-Omnibuzz
--
Please post ddls and sample data for your queries and close the thread if
you got the answer for your question.
"Andre" wrote:
> I know the old way, but I was hoping they had made it easier in 2005.
> "Omnibuzz" wrote:
>|||If you grouped them in the same schema, you can GRANT execute at the schema,
or even database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:F6D546EB-AB53-4E08-B5A2-7CE746FF16BF@.microsoft.com...
>I need to grant a user execute permissions on 1000 stored procedures. This
> there any easy way to do this in SQL 2005?|||So in AdventureWorks, how would I grant a user execute permissions on all th
e
HumanResource procedures with click on them individually? You said grant
permissions at the schema level, but I cant seem to make it work. If you
could provide step by step how to do this I would be grateful. Thanks.
"Tibor Karaszi" wrote:

> If you grouped them in the same schema, you can GRANT execute at the schem
a, or even database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andre" <Andre@.discussions.microsoft.com> wrote in message
> news:F6D546EB-AB53-4E08-B5A2-7CE746FF16BF@.microsoft.com...
>
>|||Right-click the schema, properties, permissions, browse the user...
Or execute below:
GRANT EXECUTE ON SCHEMA::schemaname TO username
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:1A9C0F71-9411-4FF5-8365-8C9D6C42B81A@.microsoft.com...
> So in AdventureWorks, how would I grant a user execute permissions on all
the
> HumanResource procedures with click on them individually? You said grant
> permissions at the schema level, but I cant seem to make it work. If you
> could provide step by step how to do this I would be grateful. Thanks.
> "Tibor Karaszi" wrote:
>

Execute permission for all sprocs

I am looking to write a script that will grant a specified user execute
access to all stored procedures, but no other permissions
Any help would be appreciated,
Craig"Craig HB" schrieb:
> I am looking to write a script that will grant a specified user execute
> access to all stored procedures, but no other permissions
> Any help would be appreciated,
> Craig
Just add the name of the user account in the second line and run this:
declare @.user varchar(256)
set @.user = 'The name of the user account'
declare @.sp varchar(256)
declare cu cursor fast_forward for
select [name] from sysobjects where [xtype] = 'P'
open cu
fetch next from cu into @.sp
while @.@.fetch_status = 0
begin
execute('grant execute on [' + @.sp + '] to ' + @.user)
fetch next from cu into @.sp
end
close cu deallocate cu|||sp_grantexec
http://www.sqldbatips.com/showcode.asp?ID=2
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:A903026B-14A6-40F0-9FB0-52906A69026C@.microsoft.com...
>I am looking to write a script that will grant a specified user execute
> access to all stored procedures, but no other permissions
> Any help would be appreciated,
> Craig

EXECUTE on everything.

SQL2000.
Does anybody know if it is possible to effectively grant execute on all
stored procedures within a database, without giving people db_owner
rights?
I'm possibly misreading BOL but there doesn't appear to be the equivalent
of GRANT EXECUTE ON ALL PROCEDURES TO databaseusers
TIA.You can run this sql and then run the output...
SELECT 'GRANT EXECUTE ON ' + name + ' TO someuser' FROM sysobjects WHERE TYPE =3D 'p'
-- Keith
"Stressed" <k@.c.co.uk> wrote in message =news:#HZbvuvcDHA.2404@.TK2MSFTNGP10.phx.gbl...
> SQL2000.
> > Does anybody know if it is possible to effectively grant execute on =all
> stored procedures within a database, without giving people db_owner
> rights?
> > I'm possibly misreading BOL but there doesn't appear to be the =equivalent
> of GRANT EXECUTE ON ALL PROCEDURES TO databaseusers
> > TIA.
> >