The results that fn_get_sql() returns.
-
Hi!
I use fn_get_sql() to figure out what sql command caused a table lock. Sometimes the output of this function looks like this:
select c.*, sp.sales_person, sales_person_name = sp.name from SUPPLIER c (NOLOCK) left join CUSTOMER cu (nolock) on c.customer = cu.customer left join SALES_PERSON sp (nolock) on cu.sales_person = sp.sales_person where active = 1 and c
This can be easily understood, but sometimes (in most cases) its output looks like this:
create procedure dbo.CARGO$CoordinateWRK @pinvoice int, @changer int, @result int OUTPUT, @cargo_ex int = null, @take_id_from_invoice int = 0 as set nocount on create table #unconditional_changes (cargo int) if exists(select * from CARG
or like this:
create trigger dbo.CARGO_LOT_WRK$WUpdate on dbo.CARGO_LOT_WRK for Update as if @@ROWCOUNT = 0 return declare @id int, @state char(1), @old_state char(1), @occur datetime, @class varchar(30), @object varchar(255) select @clas
I cant figure out how to interpret these results. Where do all these triggers and SPs come from? In my understanding there is no reason for creation of stored procedures and triggers every moment...
Thanks.
-
Looks to me like a (3rd party) app is adding a bit of overhead to your SQL Server, perhaps necessarily, perhaps unnecessarily. Does the login/user under which it's running have DDL privileges?
Cheers,
- Mark
Cheers,
- Mark -
The application is an executable file that accesses databases with the rights of the user logged on to the application. Users run this application from a shared folder on other then MS SQL 2000 machine. I can hardly imagine that this application may have "DDL privileges" (means that it must have rights to run DDLs?).
-
No.... DDL not DLL.
DDL is Data Definition Language (the creation, dropping and altering of database objects, as you've shown in your last 2 examples) and can be assigned to a user/role by either granting individual database rights, or by adding it to the db_ddladmin role.
You need to check the database users and roles to see if any have unusual privileges.
Cheers,
- Mark
Cheers,
- Mark -
quote:
You need to check the database users and roles to see if any have unusual privileges.select distinct action from dbo.sysprotects (nolock)
===========================================================
action
198
195
26
197
196
193
224
(7 row(s) affected)
From BOL:
===========================
198 = CREATE TABLE /* seems unusual */
195 = INSERT
26 = REFERENCES
197 = UPDATE
196 = DELETE
193 = SELECT
224 = EXECUTE
select object_name(id) as obj_name,user_name(uid) as user_id,action from dbo.sysprotects (nolock)
where action=198
=========================================
obj_name, user_id, action
NULL, public, 198
(1 row(s) affected)
So I should revoke the right "Create table" from public role, right?
-
From the looks of it, the answer is YES.
BUT, is this database one that was created by a 3rd party and used by its app? If so, then this creation of objects is part of its functionality and shouldn't be fiddled with. Best that you revoke any rights under your standard change control conditions.
Cheers,
- Mark
Cheers,
- Mark -
The development guys say that the right to create table for public is most probably "by mistake" and that they don't know what kind of functionality of the application may cause trigger and procedure creation. /* perhaps because some people, who know this, left */
Is there a way I can use to point, what application functionality needs trigger and procedure creation?
-
This is what I figured out: this behaviour. The text like CREATE ... is simply the text of the stored procedure or trigger that is executed.
/* just to look in the properties of the stored procedure or text of a trigger */
-
Side attraction but noticed the 'SELECT *'
-
quote:
Side attraction but noticed the 'SELECT *'Did not quite understood. You mean lines like this:
select c.*, sp.sales_person, sales_person_name = sp.name from SUPPLIER c (NOLOCK) left join CUSTOMER cu (nolock) on c.customer = cu.customer left join SALES_PERSON sp (nolock) on cu.sales_person = sp.sales_person where active = 1 and c?
Pobably these are queries from QA or something like that.
-
The answer to the reason why you sometimes get the 'create proc' bits in the sql can be found in an article by Kalen Delaney in the September 2003 issue of SQL Server Magazine
fn_get_sql returns the full SQL for the command being executed.
The sysprocesses table also provides 2 columns, namely, start_stmt and end_stmt. These columns give you the offset within the text column of the currently executing statements.
This is the code from Kalen's article that will return the actual statements:-
select substring(text,(@start+2)/2,
case @end when -1 then (datalength(text)) else (@end-@start +2)/2 END)
FROM ::fn_get_sql(@h)
where @start and @end are the stmt_start and stmt_end values from sysprocesses
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply