Not Able To Create Hash Table Inside Stored Proc Through Execute Sp_executesql @strQuery
Aug 21, 2007
Hello,
i need to create temporary table inside SP.
i having one string variable @strQuery which contain dynamic query inside SP.
i am executing that trhough execute sp_executesql @strQuery once query build.
now instead of select query , i want to creat hash table.
so i wrote :
set @strQuery = "Select * into #tmp_tbl from table_name..."
when i tried to execute it through
execute sp_executesql @strQuery , its giving error 'Invalid object name '#tmp_tbl'
If i removed Hash then it works fine. even for double Hash also its work fine.
but i want hash table only as i want that table local to that user.
Even direct execution of select statement without @strQuery works fine. but i want to execute @strQuery through execute sp_executesql @strQuery only as query is dynamic .
please guide me how to do this?
its very urgent for me.
thanks in advance.
View 4 Replies
ADVERTISEMENT
Mar 18, 2014
I am a SysAdmin on a test/dev SQL instance. Other non-sysadmin users (developers) need the ability to execute DBCC commands like the following:
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
OR
DBCC FREEPROCCACHE
I tried creating a store proc in a user database and granting those non-sysadmin users EXECUTE permission on it as so:
CREATE PROC spFreeSystemCache
WITH EXECUTE AS 'sa'
AS
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL
GO
When I try to create this proc, I get the following error:
Msg 102, Level 15, State 1, Procedure spFreeSystemCache, Line 2
Incorrect syntax near 'sa'.
Ok, so I can't EXECUTE AS sa...
View 5 Replies
View Related
Jun 16, 2008
I have the following code which works, but every time i try to put a procedure around it to pass in the 3 variables I set in this code manually it bombs on me.
~SQL~
USE [myDb]
-- variable declarations
DECLARE @sql nvarchar(max)
DECLARE @params nvarchar(max)
DECLARE@tableName nvarchar(50)
DECLARE@personId nvarchar(36)
DECLARE@encId nvarchar(36)
DECLARE@prov_reviewOUT nvarchar(1)
DECLARE@prov_opened_templateOUT nvarchar(1)
--TEST:: add input values
set @tableName = 'master_im_'
set @personId = '9B9192AF-97E1-4FD6-8279-362E4944ECAC'
set @encId = '63570609-529B-4488-BBBF-E039A8F10D92'
-- build SQL String for dynamic SQL query
SELECT @sql = N'SELECT @prov_reviewRET = provider_review, @prov_opened_templateRET = prov_opened_template
FROM dbo.' + quotename(@tableName) + ' WHERE person_id = @personId AND enc_id = @encId'
-- build parameters for sp_executesql
SELECT @params = N'@personId nvarchar(36), ' +
N'@encId nvarchar(36), ' +
N'@prov_reviewRET nvarchar(1) OUTPUT, ' +
N'@prov_opened_templateRET nvarchar(1) OUTPUT '
-- execute dynamic sql string
EXEC sp_executesql
@sql,
@params,
@personId,
@encId,@prov_reviewRET = @prov_reviewOUT OUTPUT,
@prov_opened_templateRET = @prov_opened_templateOUT OUTPUT
select @prov_reviewOUT, @prov_opened_templateOUT
~endSQL~
works fine every time...
but when i do this:
~sql~
create procedure svsp_test
@tableName nvarchar(50),
@personId nvarchar(36),
@encId nvarchar(36),
@prov_reviewOUT nvarchar(1) OUTPUT,
@prov_opened_templateOUT nvarchar(1) OUTPUT
AS
-- variable declarations
DECLARE @sql nvarchar(max)
DECLARE @params nvarchar(max)
-- build SQL String for dynamic SQL query
SELECT @sql = N'SELECT @prov_reviewRET = provider_review, @prov_opened_templateRET = prov_opened_template
FROM dbo.' + quotename(@tableName) + ' WHERE person_id = @personId AND enc_id = @encId'
-- build parameters for sp_executesql
SELECT @params = N'@personId nvarchar(36), ' +
N'@encId nvarchar(36), ' +
N'@prov_reviewRET nvarchar(1) OUTPUT, ' +
N'@prov_opened_templateRET nvarchar(1) OUTPUT '
-- execute dynamic sql string
EXEC sp_executesql
@sql,
@params,
@personId,
@encId,
@prov_reviewRET = @prov_reviewOUT OUTPUT,
@prov_opened_templateRET = @prov_opened_templateOUT OUTPUT
~endsql~
and then run the proc like this:
declare @prov_reviewOUT nvarchar(1), @prov_opened_templateOUT nvarchar(1)
exec svsp_test 'master_im_', '9B9192AF-97E1-4FD6-8279-362E4944ECAC', '63570609-529B-4488-BBBF-E039A8F10D92', null, null
select @prov_reviewOUT, @prov_opened_templateOUT
I always get null returned. If i put a select statement into the stored proc create code as the last line, it shows the correct values, but trying to read them from the sp in the create procedure statement always returns null. Can someone help me figure out what I'm doing incorrectly please?
Thanks!
View 3 Replies
View Related
Jul 23, 2005
Greetings All, currentley there is a heated discussion in my place ofwork over which method is better/more efficient for simple selects.Background:1.) Simple Application that uses sql server for backened db.2.) The application is only inserting and selecting data from the db.3.) The developers want to use sp_executesql for simple selects andthe dba's want to use a stored proc.[color=blue]>From my reading it seems that sp_executesql has a bit of overhead with[/color]it and it is not as efficient as stored procs.I would appreciate anyone's input on which would be better for simplerepetitive inserts to the db: Stored Proc, or sp_executesql?Regards, TFD.
View 3 Replies
View Related
Sep 17, 2007
I am writing a SQL 2000 stored procedure which uses an €˜EXEC @sqlString€™ statement. The @sqlString is generated at runtime. I want to give as few permissions as possible and currently allow users to access the database tables using only the stored procedures provided. However, with €˜Exec€™ I discover that I need to grant permissions on the actual tables to the users or groups. I would like to avoid this. I would also prefer not having to maintain a separate user with table level permissions and hardcoding the stored procedure with these details.
Is there anyway for me to dynamically generate the required SQL statement within my stored procedure and let SQL know that this stored procedure is allowed to select whatever tables it wants to without having to define permissions on the tables?
View 1 Replies
View Related
May 25, 2007
Q: how to use VDI from SQL2k5 inside of CLR stored proc writen in C#?
In SQL2K i've wrote C++ extended stored proc using headers from VDI SDK.
any simple samples for C# and CLR exists?
thanks.
View 3 Replies
View Related
Jun 15, 2006
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies
View Related
Jul 23, 2005
Dear Techies,I making one stored procedure, which does some operation based on aninterface hash (#) table ---- name #mydata.This stored has two section of code (seperated by parameter value 0and 1)But hash table #mydata (same name) number/name of columns changes asper call 0 or 1.e.g.when call for 0, ----> Pass 2 columns as company_cd and section_cd ininterface hash (#) table ---- name #mydata.when call for 1, ----> Pass 3 columns as Section_cd, line_cd andsubline_cd in interface hash (#) table ---- name #mydata.As a result, none of the case (0 or 1) is running properly, It givesproblem.When I execute procedure for 0 by passing #mydata with two columns---> it gives problem in 1 section codeAnd When I execute procedure for 1 by passing #mydata with threecolumns ---> it gives problem in 0 section codePlease suggest !!! If anybody have faced the same problem or have anyidea about this case.(I think passing hash table with 3 column as col1,col2,col3 can servethe purpose, but this may cause rework in my case, so looking foralternate solution)Thanks in Advance,T.S.Negi
View 1 Replies
View Related
Oct 14, 2007
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies
View Related
Sep 7, 2006
Hi to all,Probably I'm just doing something stupid, but I would like you to tellme that (if it is so), and point the solution.There ist the thing:I' having a sp, where I call other sp inside.The only problem is, the name of this inside sp is builded variously,and executed over sp_executesql:create pprocedure major_sp@prm_outer_1 varchar(1),@prm_outer_2 varchar(2)assome codingset @nvar_stmtStr = N'exec @int_exRetCode = test_sp_' + @prm_outer_1 +@prm_outer_2set @nvar_stmtStr = @nvar_stmtStr + ' @prm_1, @prm_2, @prm_3, @prm_4output'set @nvar_prmStr = N'@prm_1nvarchar(128), ' +N'@prm_2nvarchar(128), ' +N'@prm_3nvarchar(4000), ' +N'@int_exRetCodeint output, ' +N'@prm_4varchar(64) output'exec sp_executesql @nvar_stmtStr,@nvar_prmStr,@prm_1,@prm_2,@prm_3,@int_exRetCode = @int_exRetCode output,@prm_4 = @prm_4 outputNow the issue is, I've transactions inside test_sp_11 lets say wherethe 11 is @prm_outer_1 + @prm_outer_2.These procedures are existing inside database, but are called dynamiclydepending of the parameters.The problem is, when I call the specified sp directly, the rollbacktransaction is working without any problem.Inside this procedures test_sp_xx, is a call of another sp (lets sayinside_sp).There is a transaction included.When it is called over major_sp, then the rollback is not performedbecause of error:Server: Msg 6401, Level 16, State 1, Procedure inside_sp, Line 54Cannot roll back transactio_bubu. No transaction or savepoint of thatname was found.The funniest way is, if there is no error inside, the commit is workingwithout any problem!The question is majory (because I'm almost sure, that this is anissue): is it possible, to have atransaction inside dynamicly called sp over sp_executesql?If ok to do that?Thank's in advanceMatik
View 1 Replies
View Related
Jul 13, 2004
ok I have a stored procedure in my MS-SQL Server database.
It looks something like this.....
CREATE PROCEDURE updatePCPartsList
(
@Descriptionvarchar(255),
@ManCodevarchar(255),
@ProdCodevarchar(255),
@Pricedecimal(6,2),
@Commentsvarchar(255)
)
AS
declare @IDFound bigint
declare @LastChangedDate datetime
select @LastChangedDate = GetDate()
select @IDFound = PK_ID from PCPartsList where ProdCode = @ProdCode
if @IDFound > 0
begin
update PCPartsList set Description = @Description, ManCode = @ManCode, ProdCode = @ProdCode, Price = @Price, Comments = @Comments, LastChanged = @LastChangedDate where PK_ID = @IDFound
end
else
insert into PCPartsList (Description, ManCode, ProdCode, Price, Comments, LastChanged) values(@Description, @ManCode, @ProdCode, @Price, @Comments, @LastChangedDate)
GO
It executes fine so I know i've done that much right....
But what i'd like to know is how I can then return a value - specifically @LastDateChanged variable
I think this is a case of i've done the hard part but i'm stuck on the simple part - but i'm very slowly dragging my way through learning SQL.
Someone help?
View 3 Replies
View Related
Jul 7, 2006
is there a way to execute SSIS Package through stored proceedure.
Or any other method of executing the SSIS Package command line in stored proceedure
Thanks,
jas
View 10 Replies
View Related
Apr 19, 2007
is is posible to execute a stored proc (with parameters) from an OLE DB Destination ??
reason we are trying this is cos
our current setup is an OLE DB Command doing the first database update and then passing over to an OLE DB Destination that does the second update. There is error handling coming off the OLE DB Command to a Script component that passes to an OLE DB Destination.
we are having a problem getting the error reporting working from the OLE DB Command - the updates work fine - but not getting any updates of the error database when there is an error.
have got the error reporting fine on the OLE DB Destination.
if we can execute the stored proc from the OLE DB Destination, we will then do both updates via one stored procedure executed by the OLE DB Destination.
thx
m
n.b. think the updating of the error logs from the OLE DB Command used to work - but cant get it to work now ??!!!!??
View 4 Replies
View Related
Aug 4, 2004
Hi All,
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
Create PROC dbo.GrantPermission
@user1 varchar(50)
as
Grant create table to @user1
go
Grant create view to @user1
go
Grant create Procedure to @user1
Go
Thanks Guys.
View 14 Replies
View Related
Nov 1, 2005
Hi, I have a table containing SQl statements. I need to extract the statements and execute them through stored procedure(have any better ideas?)
Table Test
Id Description
1Insert into test(Id,Name) Values (1,'Ron')
2Update Test Set Name = 'Robert' where Id = 1
3Delete from Test where Id = 1
In my stored procedure, i want to execute the above statements in the order they were inserted into the table. Can Someone shed some light on how to execute multiple sql statements in a stored procedure. Thanks
Reo
View 2 Replies
View Related
Jun 2, 2006
I think I'm being a bit thick, but I just cannot figure out the proper syntax to call a stored proc on a SQL named instance I have. I've tried many variations, but here is the basic format of what I'm trying:
EXEC ServerInstance.DB.dbo.usp_fm_proc 1
It seems it doesn't like the as I get an error "Incorrect syntax near 'Instance'.
What am I missing here?
View 3 Replies
View Related
Aug 6, 2007
My Package Exist in
C:WROXWROXinexcel_database package exist
OR sql server agent ->job-> excel_database package exist
now i want to excute the from master database how please send the
stored procedure code
Regards
koti
View 1 Replies
View Related
Feb 28, 2005
I am having problem with 'TOP @pageSize'. It doesn't work, but if I replace it by 'TOP 5' or 'TOP 6' etc., then the stored procedure runs without errors.
Can someone please tell me how I could use @pageSize here so that it dynamically determines the 'n' of 'TOP n' ?
ALTER PROCEDURE dbo.spGetNextPageRecords
(
@pageSize int,
@previousMaxId int
)
AS
/* SET NOCOUNT ON */
SELECT Top @pageSize ProductId, ProductName
FROM Products
WHERE (ProductID > @previousMaxId) order by ProductId
RETURN
View 4 Replies
View Related
Sep 18, 2007
Hi all,
I have a problem while i create a proxy account in SQL Sever 2005.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.
Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message
Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.
so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....
So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.
If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....
Thanks
Raja.V
View 1 Replies
View Related
Apr 8, 2008
I am trying to test a stored proc and I can't execute the stored proc when I use a Guid. Has anyboyd had the same problem. I right click in VS 2008 on the Stored proc click Execute and fill in the variables one of them is a Guid and no records are returned.
Here is my guid, I have tried '{0a5c25fa-3aac-4abc-8d07-6d76ab46c9b2}', {0a5c25fa-3aac-4abc-8d07-6d76ab46c9b2} and "0a5c25fa-3aac-4abc-8d07-6d76ab46c9b2" no success. This stored proc might not work but I have had the same issue with other stored procs when I wanted to test them by using execute and using a Guid.
Testing with an int works fine when I try to Select the records by UserID which is a Guid no cigar. I have notice this behavier before is there a way around this problem, thanks newbie
View 3 Replies
View Related
Nov 29, 2005
I use the context connection for the "normal" work in the CLR procedure.
View 7 Replies
View Related
Mar 23, 2006
I was wondering if it is possible to create a stored procedure to create databases. The only parameter would need to be the name of the database. Do I have to use a bunch of EXEC statements, or is there a better way?
I've created basic stored procs before, but never one for something like this.
Thanks.
View 3 Replies
View Related
Mar 12, 2008
Hi!
I want to know wether SQL Server 2005 (that I use) has a built in support for creating MD5 hashes and if it has then can someone give me an example how to use it.
I need to create hash of image field and then compare it to another one that has been sent from application. The hash from my app is sent as string so it would be great if the hash result would be returned as 32 character string
The Software is wrintten in visual basic .net 2005 and it has a built in MD5 hash function so I have taken care of that part.
P.S. If SQL Server 2005 doesn support MD5 maybe someone know other hash alghoritms that Server does support (maybe one that also is supperoted by VB.NET 2005).
View 6 Replies
View Related
Nov 5, 2015
Can I invoke stored procedure stored inside from a user defined table column?
View 5 Replies
View Related
Dec 7, 2004
Hello SQL Server programming gurus:
I am trying to create a trigger that fires after a user logon and logoff and does the following:
creates a new user
deletes data from old temp table
Then I need to create a stored procedure that executes dynamically to
drop old users
remove the old user account access
We are running SQL Server 2000.
Since I am new to T-SQL programming could anyone help point me in the right direction? Can I write dynamic SQL in a trigger to do these things?
View 1 Replies
View Related
Apr 5, 2006
My simple CLR Stored procedure is as below:
[Microsoft.SqlServer.Server.SqlProcedure]
public static int MyParallelStoredProc(string name1, string name2)
{
Thread t = null;
Worker wth = null;
int parallel = 2;
Object[] obj = new object [parallel];
SqlPipe p;
p = SqlContext.Pipe;
for (int i = 0; i < parallel; i++)
{
if (i == 0)
wth = new Worker(name1);
else
wth = new Worker(name2);
t = new Thread(new System.Threading.ThreadStart(wth.WorkerProc));
t.Name = "Thread -" + i.ToString() + ":";
t.Start();
p.Send(t.Name + ":Started");
obj[ i] = t;
}
for (int i = 0; i < parallel; i++)
{
t = (System.Threading.Thread)obj[ i];
t.Join();
p.Send(t.Name + ":Finished");
}
return 0;
}
The worker class implementing Thread Proc:
public class Worker
{
private string Name;
public Worker(string name)
{
SqlPipe p;
p = SqlContext.Pipe;
Name = name;
p.Send("In Constructor:" + Name);
}
public void WorkerProc()
{
SqlPipe p;
p = SqlContext.Pipe;
for (int i = 0; i < 10; i++)
p.Send(i.ToString()+":"+Name);
}
}
The assembly is registered with UNSAFE permission set.
CREATE ASSEMBLY
ThreadTest
FROM
'C:\ThreadTestinDebugThreadTest.dll'
WITH
permission_set = unsafe;
GO
CREATE PROC ParallelStoredProc
@Name1 NVARCHAR(1024),
@Name2 NVARCHAR(1024)
AS
EXTERNAL NAME ThreadTest.[MyTest.ThreadTest].MyParallelStoredProc
When I invoke the the stored procedure from T-SQL script as below,
EXEC ParallelStoredProc @Name1, @Name2
the thread class constructor gets called; but the 'WorkerProc' does not execute ?
Whether an UNSAFE assembly is allowed to spawn threads
inside SQL Server ?
View 8 Replies
View Related
Dec 20, 2006
please, in simple words, what is difference between :sp_executesqlandEXECUTEin sql2005?
View 3 Replies
View Related
Jan 11, 2000
Hi... Everybody,
I am new to using SQL Server and I present to you the following problem that I am facing:
Can I use the 'EXECUTE' or 'EXECUTE sp_executesql' in a SELECT query that assigns a value to a declared variable ?
To be more specific:
I have the following set of SQL Statements that do not seem to work:
------------------------------------------------------------------------------
DECLARE @CustomerID char(6)
DECLARE @OfficeID char(3)
DECLARE @DestinationAccountNo char(7)
DECLARE @TableName char(30)
SET @TableName = 'Users'
SET @CustomerID = '001001'
SET @OfficeID = '001'
SET @DestinationAccountNo = '0001011'
DECLARE @ExecuteString nvarchar(500)
DECLARE @CurDestPatIDChar char(2)
SET @ExecuteString = N'SELECT @CurDestPatIDChar = RTRIM(CAST(Max(CAST([UserID] AS decimal(2, 0))) AS char(2))) '
SET @ExecuteString = RTRIM(@ExecuteString) + N' FROM ' + RTRIM(@TableName)
SET @ExecuteString = RTRIM(@ExecuteString) + N' WHERE [CustID] = ''' + RTRIM(@CustomerID) + N''' AND [OfficeID] = ''' + RTRIM(@OfficeID) + N''' AND [AccNo] = ''' + RTRIM(@DestinationAccountNo) + N''''
EXECUTE SP_EXECUTESQL @ExecuteString
PRINT @CurDestPatIDChar
------------------------------------------------------------------------------
When I run this in the Query Ananlyzer I get the following error:
Server: Msg 137, Level 15, State 1, Line 0
Must declare the variable '@CurDestPatIDChar'.
The above set of statements do not seems to work with EXECUTE either.
Where as if I run the following query with the same variable declarations as above:
-----------------------------------------------------------------------------
SET @ExecuteString = N'SELECT @CurDestPatIDChar1 = RTRIM(CAST(Max(CAST([PatientID] AS decimal(2, 0))) AS char(2))) '
SET @ExecuteString = RTRIM(@ExecuteString) + N' FROM ' + RTRIM(@TableName)
SET @ExecuteString = RTRIM(@ExecuteString) + N' WHERE [CustID] = ''' + RTRIM(@CustomerID) + N''' AND [OfficeID] = ''' + RTRIM(@OfficeID) + N''' AND [AccNo] = ''' + RTRIM(@DestinationAccountNo) + N''''
EXECUTE SP_EXECUTESQL @ExecuteString, N'@CurDestPatIDChar1 char(2)', @CurDestPatIDChar1 = @CurDestPatIDChar
PRINT @CurDestPatIDChar
-----------------------------------------------------------------------------
I donot get any error messages but the variable '@CurDestPatIDChar' is not initialized.
The problem seems to be that the execute statement interprets any variable assignments (here it is '@CurDestPatIDChar', defined as part of the execute string in quotes) as local to the execute statement.
I shall be grateful if you can provide me with a solution for this,
BR,
Sudhakar
View 2 Replies
View Related
May 13, 2002
This is what I am trying:
set @cntsql = 'select count(' + @dimky + ') as dimcnt from ' + @dimtb + ' where ' + @dimky +' is not null'
set @dimcnt = execute sp_executesql @cntsql
What I want to do is return the count from the dynamically selected database. If I type it in like:
set @dimcnt = (select count('ptky') as dimcnt from pttable where ptky is not null)
it works.... can anyone help... this code is in the middle of a cursor.
View 2 Replies
View Related
Jun 3, 2006
Hi,
How can I check return value from
EXECUTE sp_executesql(SQLStr)
Thanks,
View 1 Replies
View Related
Jun 3, 2006
Hi,
How can I check return value from
EXECUTE sp_executesql(SQLString)
Thanks,
View 4 Replies
View Related
Mar 26, 2008
Hi There
Ok i have a piece of test ddl sql that is written to a varchar(max) column. Entered by a user.
GO's and missing semi colons seem to break it, if you try to execute it with EXEC or sp_executesql, however the sql can be executed on Management Studio as is, so how can i execute it as is successfully?
In a nutshell i retreive the DDL sql from the column into a nvarchar(max) variable called @SQL , and i need to execute it.
I have tried:
EXEC(@SQL) and sp_executesql @SQL, both return the error , incorrect syntax near 'GO'.
The problem is obviously i have to have the go statements, in order to create some fo the ddl objects correctly. But EXEC and sp_executesql do not like that. I also found that semi colons are required after every statement.
The sql is as follows:
--===============================================================================
--DDL script
CREATE LOGIN TEST_LOGIN WITH PASSWORD = 'Whatever!@(!';
CREATE USER TEST_USER FROM LOGIN TEST_LOGIN;
CREATE TABLE TEST_TABLE (Column1 int NULL);
GRANT INSERT ON TEST_TABLE TO TEST_USER;
CREATE CLUSTERED INDEX TEST_INDEX ON TEST_TABLE (Column1);
GO
CREATE PROCEDURE TEST_PROCEDURES
AS
SELECT GETDATE();
GO
CREATE VIEW TEST_VIEW
AS
SELECT * FROM TEST_TABLE;
GO
--ALTER DDL
ALTER VIEW TEST_VIEW
AS
SELECT GETDATE() AS 'DATE';
GO
ALTER PROCEDURE TEST_PROCEDURES
AS
SELECT * FROM TEST_TABLE;
GO
--DROP DDL
DROP INDEX TEST_TABLE.TEST_INDEX;
DROP TABLE TEST_TABLE;
DROP VIEW TEST_VIEW;
DROP USER TEST_USER;
DROP LOGIN TEST_LOGIN;
DROP PROCEDURE TEST_PROCEDURES;
--===============================================================================
View 5 Replies
View Related
Aug 27, 2002
In our development and test environments the developers need to create and execute stored procedures as dbo without having any other dbo permissions. If I place them in db_owner, they have too many permissions. Is there a way to address this situation?
I'm also curious how other companies address the subject of creating stored procedures in development and test environments. If I give developers create and execute permission in a database, all objects would be created as JohnDoe.storedprocedurename instead of dbo.storedprocedurename. Any help in this area is appreciated.
Dave
View 1 Replies
View Related
