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 DBCC on linked server

Hi,

How do you execute 'DBCC' statements or 'EXEC ...' against a linked server (SQL 2000 sp4) ?

Regards,

A.E

EXEC [linkedserver].master.dbo.sp_executesql N'DBCC USEROPTIONS'

|||

Thanks Mark, your suggestion works fine but now I have another problem when I do the following for example:

INSERT [sometable] EXEC [LinkedServer].pubs.dbo.sp_executesql N'EXEC sp_helpfile'

Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
The transaction active in this session has been committed or aborted by another session.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver]Distributed transaction error]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].

I followed the instructions in article 839279 but still get the error, any ideas?. I don't need DTC transaction support, is there a way to get the results without involving MSDTC ?

Regards,

A.E

|||

If you've tried everything in

http://support.microsoft.com/kb/839279

then there's not a lot more I can add.

BTW, the last time I saw this, it was fixed simply by specifying
SET XACT_ABORT ON before the EXEC.

EXECUTE AS clause help

I am having trouble utilizing the EXECUTE AS clause. Here is the simple statement:

Execute as user = 'Common.com\bsmith'
--DBCC log(Actg, Type=2)
DBCC SQLPERF(LOGSPACE)

bsmith is the system administrator that has rights to both DBCC in connection with SQLPERF and the following statement:

DBCC log(Makeup, Type=2)

where the database is Makeup. My login (Common.com\dchrist)
does not have permission to do anything connected with the log file. The goal of this SQL is to allow dchrist to run items connected with Actg log files ONLY. I am logging into the server (EVR-NON-DB) as dchrist with my password.

Is there any way to do this or am I way off base. I have read several postings that explain EXECUTE AS and thought I understood how to use it. When I run:

Execute as user = 'Common.com\bsmith'
--DBCC log(Actg, Type=2)
DBCC SQLPERF(LOGSPACE)

I get the following error message:

Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal "Common.com\bsmith" does not exist, this type of principal cannot be impersonated, or you do not have permission.

If anyone has any idea what I should do next I would really appreciate it. Thanks for all help in advance.

A couple of guesses. First, you probably need to use EXECUTE AS LOGIN =

Then look up "GRANT Server Principal Permissions". You probably have to give your login rights to IMPERSONATE the other login.

|||It sounds to me like you're referring to a LOGIN, not a USER. Try using 'LOGIN' instead of 'USER', or else work out which user maps to the login. A user is how a login accesses a database. Your login might access one database as dbo, another as bsmith, and a third as something else. Once you've got that sorted, let us know what the next error message says.