Executing on a different database.

Gday guys.

I have a question on a problem I'm unable to solve. Help would be much appreciated.

I'm using SQL2005 at the moment,

Basically what I'm trying to do is Execute a string. The trick is that I want to do it on a different database than the one I'm currently working on.

I get the execution string from the Information_Schema.ROUTINES system table, so they cannot be modified to suit my needs.

Basically, I'm trying to move all UDF's and stored procedures that meet certain criteria to a different database using one script.

I know all about the USE statement, but I can't append it infront of the execution string, because it complains that CREATE or ALTER needs to be the first statement. I also can't use the USE statement before I call EXECUTE, because it complains that a USE statement may not be used within a procedure.

Does anybody know of a way I can execute those strings on a different database?

Although I always suggest not using dynamic SQL its the appropiate way to do some adhoc things:

DECLARE @.SQLText VARCHAR(MAX)
DECLARE @.DatabaseName VARCHAR(MAX)

SET @.DatabaseName = 'Mydatabase'
SET @.SQLTEXT = 'USE ' + @.DatabaseName

SELECT @.SQLTEXT = @.SQLTEXT + ' GO ' + ROUTINE_DEFINTION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE <YourFiltercriteriaHere>

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql

Execute privileges on sp

Hi. I'm trying to test something on a test db I have installed on my pc, but I am unable to process as I'm doing it. So, basically what I want is to give execute privilege on a procedure to a user, so the user can execute this procedure without having the privileges explicity granted on it (what this procedure do is to truncate a table on which the user has no access). As I've read, SQL Server stored procedures privileges runs with the definers permissions, not the one that is actually executing the procedure. So, what I'm doing is this: in query analyzer, logged in as sa, I did

use test

create table t ( a integer )

create procedure can_truncate as
truncate table t

sp_addlogin 'jmartinez',''

sp_grantdbaccess 'jmartinez','jmartinez'

grant execute on can_truncate to jmartinez

Then I went to connect again, as jmartinez and did:

exec can_truncate

and I get

Server: Msg 3704, Level 16, State 1, Procedure can_truncate, Line 2
User does not have permission to perform this operation on table 't'.

So, I wonder what more permissions would user jmartinez need in order to execute this procedure successfully. I hope you all understand what I am trying to achieve.

Thanks!Shake things up a bit, and try:use test
GO
sp_addlogin 'jmartinez',''

sp_grantdbaccess 'jmartinez','jmartinez'
GO
create table t ( a integer )
GO
create procedure can_truncate as
truncate table t
RETURN
GO

grant execute on can_truncate to jmartinez-PatP|||I get the same exact error message using your method. Btw, I am using MSDE, this is what I get when I do SELECT @.@.VERSION:

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)|||Reading through BOL, I found out this:

Permissions
TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.
I guess this mean I cannot transfer TRUNCATE TABLE privilege to a user, but since I am executing a procedure I created on my own ( as sa in this case ), woulnd't it have to run with my privileges instead of the user who is executing it ( in this case, jmartinez ) ?|||You need to run this code after granting rights:

sp_addrolemember 'db_ddladmin', 'jmartinez'|||But then I'm giving the user privileges to basically *destroy* my db..|||Hmm.. TRUNCATE TABLE is an operation that requires high priviledges as long as I know, so you will have to give a user them.

By the way, why does user need to perform this? IMHO, You may create something like and administration panel which will login to database on its own and perform TRUNCATE TABLE instead of user - e.g. he selects a table from the combo box and then presses SUBMIT button, and then script connects as someone special with appropriate role membership and runs TRUNCATE TABLE.|||You might use
Delete
From <TableName>|||You might use
Delete
From <TableName>

The question is about

what this procedure do is to truncate a table on which the user has no access