I want that I will allow a user only to select data from any object and only to alter an existing stored procedure or view. That user can not drop and create any stored procedure and view.
I can assign that user db_datareader role, grant view definition but if I grant alter permission, that user can create, alter and drop any stored procedure and view.
Version 2008 R2
The stored procedure has the dependency on the table that was altered.
Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
I would like to search for a particular stored procedure written by a developer. I know the name of the procedure but in which db is it residing in. There are 40 databases in this SQL 2008 instance. I search on the name column in sys.all_objects table and it does not return anything. I end up querying sys.procedures on each database to locate the procedure. Is there a system table/view that I can query to look for a procedure, instead of querying sys.procedures on each database one by one?
View 1 Replies View RelatedI am trying to create a trigger with in a stored procedure. When I execute the stored procedure I am getting the following error :
Msg 2108, Level 15, State 1, Procedure JPDSAD1, Line 1
Cannot create trigger on 'FRIT_MIP003_BOK_BTCH_LG.P62XB1.XB1PDS' as the target is not in the current database.
Here is the code for the stored procedure :
CREATE PROCEDURE [dbo].[InsertTRIGGER](@databaseA varchar(50))
AS
BEGIN
exec ('USE ['+@databaseA+'];')
exec ('CREATE TRIGGER [P62XB1].[JPDSAD1] ON [' + @databaseA + '].[P62XB1].[XB1PDS] ' +
'AFTER DELETE AS ' +
'BEGIN ' +
' INSERT INTO [' + @databaseA + '].[P62XB1].[XL1TDS] SELECT CAST(SYSDATETIME() AS DATETIME2(6)) , ''B'' , ''D'' , IDA_DELETE ' +
' ''0001-01-01 00:00:00.000000'' , '' '' FROM DELETED ' +
'END')
END
I am search for coding criteria I need create a stored procedure with execute as and along with encryption. How can I use the same ? My main motive is to create proc with execute as a user also at the same time I need to encrypt the same from other users seeing the code.
The below query is getting errors:
Create procedure testproc
with execute as 'user' and with encryption
as truncate table some table
I want to alter a stored procedure from within my code base. I'm basically wanting to write an in house app that will update my stored procedures across many databases that we have. Any help would be appreciated. I'm having difficulty finding anything on this topic.
View 3 Replies View Related
Hi all,
I use SQL2005 and I recently noticed this...
When I right click a stored procedure and select modify I get something like this
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xxxxxx]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
xxx xxx xxx'
instead of the usual alter procedure...
I think that this happened after I installed SP2 (which I cannot remove)
Why this is happening and how can I revert it to the old way of altering stored procs?
I have a stored procedure with several insert into statements. On occasion one of the insert into queries doesn't return any data. What is the best way to test for no records then, skip that query?
View 5 Replies View RelatedWe have a legacy database that have hundreds of stored procedures.
The previous programmar uses a string like servername.databasename.dbo.tablename in the stored procedures.
We now have migrated the database to a new server. The old server is either needed to be replaced by the new server name, or remove it.
I don't know why he used servername as part of the fully qualified name, we don't use linked servers. So I think better removing the servername in all the stored procedures.
I know I can do a generate script, and replace the text and then use alter procedure to recreate all the stored procedures. But since hundreds of them, is there a programmatically way to replace them?
Is it possible to check query execution plan of a store procedure from create script (before creating it)?
Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?
I am replicating a stored procedure execution, which builds and executes the following dynamic SQL command:
IF EXISTS (select * from MyDB..sysfiles sf (nolock) where name = 'MyDB_201201')
ALTER DATABASE [MyDB] REMOVE FILE [MyDB_201201]
IF EXISTS (select * from MyDB..sysfilegroups sfg (nolock)
where groupname = 'MyDB_201201' and sfg.groupname not in(
SELECT distinct fg.name file_group_name
[Code] ....
I can run this SP with no errors on both the publisher and the subscriber. However, when I try to replicate the execution of this SP, I get an error in replication monitor:
ALTER DATABASE statement not allowed within multi-statement transaction. (Source: MSSQLServer, Error number: 226)
How can I change my code to workaround this? Perhaps some explicit transactions?
I am trying to call a stored procedure into report builder. The stored procedure has 2 parameters. When I run the report with allowing nulls and blank value in the parameters it works fine, but when I enter a value in a parameter it ignores the where clause I had in the original query(stored procedure) and displays everything.
View 3 Replies View RelatedWe have a process that uses a stored procedure: sp_MysteryProcedure....
The problem I am having is: I am 95% certain the issue lies with this stored procedure, but I cannot find the process that calls this procedure. It could be a SQL Server Job that calls the procedure directly, it could be an SSIS (*.dtsx) process that calls this procedure, or some other random process.
One of the big issues is we have tons of *.dtsx packages that call a bunch of stored procedures, but it doesn't seem a normal Windows Search (Start --> Search) searches these files (or perhaps something else is going on with this search).
So my question is multi-part.....
1). How would one go about finding a rouge process that loads data via a stored procedure if we believe we know the stored procedures name?
2). How do you search *.dtsx files?
I have looked all around and I am having no luck trying to figure out how to alter a stored procedure within an asp.net application.Here is a short snippet of my code, but it keeps erroring out on me.Try myCommand.CommandText = "Using " & DatabaseName & vbNewLine & Me.txtStoredProcedures.Text myCommand.ExecuteNonQuery() myTran.Commit()Catch ex As Exception myTran.Rollback() Response.Write(ex.ToString())End Try The reason for this is because I have to propagate stored procedures across many databases and was hoping to write an application for it.Basically the database name is coming from a loop statement and I just want to keep on going through all the databases that I have chosen and have the stored procedure updated (altered) automatically So i thought the code above was close, but it keeps catching on me. Anybody's help would be greatly appreciated!!!
View 3 Replies View RelatedHi,
I am using SQL 2000( Version: 8.00.000)
I want to ask can I add View in the stored procedure like the following:
Create Procedure sp_createTblLogReasonColorView
@m int,
@y int
AS
BEGIN
ALTER View vw_tblLogReasonColor
AS
Select * From tblLogReasonColor where Year(n_Delivery_Date) = @y AND Month(n_Delivery_Date) = @m
END
Thanks for you reply.
Can the ALTER USER statement be used (without a hack like using EXEC) in a stored procedure? I know that the sp_password system stored procedure can not be. Additionally, it is being deprecated anyway. I guess what is boggling me about my attempts so far relate to the errors I am getting due to the user being specified not being in quotes in the syntax. All of the searching I have done so far have come up lame so far; the only examples I have found about it were in scripts that create other scripts for transferring users and other administrative tasks that would be run from the query window, but not from an application. To be complete as possible, here is an example of a script the returns errors:
ALTER PROC [dbo].[lbxChangePassword]
(
@loginid nvarchar(180),
@oldpassword nvarchar(40),
@newpassword nvarchar(40)
) AS BEGIN
IF @oldpassword = (SELECT password FROM contacts WHERE loginid = @loginid)
BEGIN
BEGIN TRANSACTION
UPDATE contacts
SET password = @newpassword
WHERE loginid = @loginid
ALTER LOGIN @loginid WITH PASSWORD=@newpassword OLD_PASSWORD=@oldpassword
END
ELSE
BEGIN
RAISERROR(N'The password you entered does not match your current password.', 16, 1)
RETURN
END
IF @@ERROR <> 0
BEGIN
RAISERROR(N'There was an error creating your new password.', 16, 1)
RETURN
END
COMMIT TRANSACTION
END
************
This returns:
Msg 102, Level 15, State 1, Procedure lbxChangePassword, Line 15
Incorrect syntax near '@loginid'.
Msg 319, Level 15, State 1, Procedure lbxChangePassword, Line 15
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
************
If ALTER LOGIN isn't how to change the password, then please tell me what the correct practice of changing a password is. I want to use the CURRENT_USER keyword in my queries and want I can't finish setting that up until I have this resolved because users will need to change their own passwords through the application I am developing.
Have a server running 14 production databases, 12 of which are replicated (transactional replication) to a second server for reporting. Distributor on same machine as publishers. All set to 'SyncWithBackup' = true, distribution set to Full recovery mode, log backups every 30 minutes, full backup nightly. This generally runs just fine.
Occasionally, the process 'hangs' indefinitely (has gone 12 hours or more before being caught) and I need to stop the backup job, stop one or more of the log reader agents, and restart everything, and it proceeds just fine. Annoying, but not fatal, and not very often.
This time, no matter what, the backup job hangs when it runs. This is true whether it is the FULL backup or just a Transaction Log backup. It hangs on the stored procedure sp_msrepl_backup_start, at the point where it is attempting to update the table 'MSrepl_backup_lsns'. When it is hung like this, all of the log reader agent jobs are also hung, blocked by this stored proc. I've tried stopping ALL of the log reader agents prior to starting the backup, but the backup process still hangs up at the same spot and never ends.
I can run the select statement in that SP that gathers the data for the databases 'manually' in a query window and it finishes in about 10 seconds. It actually seems to be hung up on the 'UPDATE' statement. When it is hung, I cannot SELECT from MSrepl_backup_lsns unless I append WITH (NOLOCK) to the statement. Nothing else I can find indicates that there is anything else locking that table. DBCC OPENTRAN shows that there is a lock on that table held by that stored proc -- but I can't see any reason why it won't update the table (17 total records) and move on.
As I said, normally this runs just fine. Totally baffled by what may be causing this at this time.
I need to import multiple csv files and load into table and everytime new database has to be created .
I was able to create new databases using stored proc
How do i do a bulk insert for all the files at once to insert into tables .
i want to load all the files at once .
I have create a batch file to execute a stored proc to import data.
When I run it from the server (Remote Desktop) it works fine, but if I share the folder and try to run it from my pc, it doesn't do anything. I don't get an error, it just doesn't do anything. My windows user has admin rights in SQL. Why is it not executing from my PC?
We have more that 500 crystal reports and we would like to find out list of stored procedure used by crystal reports. Can we find out ?
View 4 Replies View RelatedI am looking to created a trigger that inserts records into a log table to show the stored porcedure that has updated a specific column to a specific value in a specific table
At present I have:
CREATE TRIGGER [dbo].[trUpdaterTable]
ON [dbo].[t_account]-- A DB level trigger
FOR UPDATE
--Event we want to capture
AS
IF update (document_status)
[Code] ...
However this doesn't appear to bring back the procedure that triggered the update...
The value the trigger should update on is document_status = 0
DDLProcExecutedByEventDate
NULLNULLLOMBDAadministrator2015-06-25 07:42:01.677
NULLNULLLOMBDA im64602015-06-25 07:51:34.503
NULLNULLLOMBDAadministrator2015-06-25 07:52:01.610
NULLNULLLOMBDAadministrator2015-06-25 08:02:01.417
CREATE TRIGGER [dbo].[trTableupdateaccount] ON [DoesMore].[dbo].[t_account]
[Code] ....
isn't there an automatic log of some sort to check and see what exactly was changed by a given SQL command? A stored proc was ran and I need to figure out what exactly it changed in the underlying table.
View 3 Replies View RelatedI am having a Stored Procedure Or SQL Script to be attached to Job Scheduler. When this Stored procedure executes it generates some output text. I need to store this output to text file when ever this store Procedure (or) SQL Script executed by job Scheduler.
View 9 Replies View RelatedThe developers in our shop have a need to explicitly grant view definition permissions to themselves on stored procedures they create in their development databases. They have dbo level permissions in these databases and although they can explicitly grant view definition permissions to other developers in the same database, they are unable to do so for themselves. When they attempt this, it appears that they are successful but when they check the stored procedure afterwards the permission is not there for themselves.
While this does not cause an issue in development, the intention is for these view definition permissions to be carried forward to the test and production databases where they only have datareader permissions.
When these stored procedures are scripted out by the dba to move to Test and Production the view definition permissions are not scripted out for the developer in question.
Is there a way that a developer with dbo rights in a database can explicitly grant themselves view definition permissions on a stored procedure they create as dbo?
I'm seeing where previous developers have used a single stored procedure for multiple reports, where each report required different columns to be returned. They are structured like this:
CREATE PROCEDURE dbo.GetSomeData (@rptType INT, @customerID INT)
AS
BEGIN
IF @rptType = 1
BEGIN
SELECT LastName, FirstName, MiddleInitial
[Code] ....
As you can see, the output depends on the given report type. I've personally never done this, but that's more because it's the way I learned as opposed to any hard facts to support it.
So what I'm looking for is basically 2-fold.
I'm running SQL server 2000 sp1. I created a stored procedure that (1) drops a table, (2) recreates it with a "select into" statement, (3) alters the table by adding a field, and then (4) updates that field.
The trouble I'm having is that when I execute the stored procedure I get an error stating that I have an "invalid column name" between steps (2) and (3). It seems as though when I drop the table in step (1), the entire procedure wants to re-compile and it can't get past step (4) because the table hasn't been altered yet.
I've noticed a similar problem in editing stored procedures when they refer to tables or fields that don't exist yet because WITHIN the procedure they are created/modified. I'm not able to get a successful syntax check and therefore not able to save my work.
I must be missing something. Any suggestions?
We are running into the following error while changing a column data type from nvarchar (1200) to varchar(8000)
"Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.TBL1 '.'PK_CL_ID' in database 'Client01' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
The statement has been terminated."
Now tried to change the filegrowth of the log file to unlimited
ALTER DATABASE Client01
MODIFY FILE ( NAME = Client01_log, MAXSIZE = unlimited);
The query executes without error but I do not see the auto growth as unrestricted. It's still 2GB
I inherited a system which has an index on a set of columns which allow more than 900 bytes of data in it. We know one of the fields can be shortened to shrink the potential key size below 900 bytes.
The problem is the table is about 120m rows, and the index currently on that column is seeked (sought?) on about 2.5m times a day.
At its simplest, I want to drop the existing index, alter the column to shrink the varchar size, and then rebuild the index on the newly shortened column.
On a smaller, less used table, I might just do this in outside of business hours and call it a day, but I'm concerned that this will take a long time and block a lot of operations.
1) IIRC, shrinking a column, unlike widening it, is much more expensive, even if there are no values which would actually end up trunacted. Is this right?
2) I did a few tests on some other smaller (2+ m) row tables and was still able to select data out of the table. I don't think this covered all the read scenarios, but are there known scenarios which would simply not work during an index build?
3) I haven't yet tried DML operations to the table while it's doing either the column update or an index build. what scenarios would or would not be blocked?
I have created a store procedure, but the requirement is function because by using this function we need to add columns in a table with SSIS.
I have tried to create function, but the error I am facing is select statement can not return data.
CREATE PROCEDURE SP_STAT_CURR
(
@I_NET_AMOUNT NUMERIC(10,3),
@I_DOCUMENT_CURR VARCHAR(3),
@I_TARGET_CURR VARCHAR(3)
[code]....
Hi,I'm tring to call a stored procedure i'v made from a DNN module, via .net control.When I try to execute this sql statement: EXEC my_proc_name 'prm_1', 'prm_2', ... the system displays this error: Could not find stored procedure ''. (including the trailings [".] chars :)I've tried to run the EXEC statement from SqlServerManagement Studio, and seems to works fine, but sometimes it displays the same error. So i've added the dbname and dbowner as prefix to my procedure name in the exec statement and then in SqlSrv ManStudio ALWAYS works, but in dnn it NEVER worked... Why? I think it could be a db permission problem but i'm not able to fix this trouble, since i'm not a db specialist and i don't know which contraint could give this problem. Also i've set to the ASPNET user the execute permissions for my procedure... nothing changes :( Shoud someone could help me? Note that I'm using a SqlDataSource object running the statement with the select() method (and by setting the appropriate SelectCommandType = SqlDataSourceCommandType.StoredProcedure ) and I'm using the 2005 sql server express Thank in advance,(/d
View 3 Replies View Relatedsomeone was alter user databases owner access.
how to find who alter evoke the database user access changes in sql server.
Can I invoke stored procedure stored inside from a user defined table column?
View 5 Replies View Related