Dynamic Login & User Creation

Hello. I'm trying to create a new login and username inside a trigger using variables.

This code works:

create login testUserName with password = 'testPassword'

The problem comes when I try to use variables for the username and password like this.

create login @.username with password = @.password

Does anybody know how to get around this problem?

BTW, the error message it gives is this, but I really doubt that semicolons have anything to do with it. If I literally type my data in the create login call it works fine, but when I use variables it doesn't.

Msg 102, Level 15, State 1, Line 14

Incorrect syntax near '@.username'.

Msg 319, Level 15, State 1, Line 14

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.

SQL Server doesn't allow you to specify variables in most DDL statements. So the only way is to form the CREATE LOGIN statement as a string and execute it using dynamic SQL. See the EXECUTE topic in Books Online for more details on how to execute SQL statements dynamically.|||

This doesn′t work unless you wrap it in dynamic SQL:

DECLARE @.SQLString VARCHAR(400)
SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + WITH PASSWORD ' + @.PASSWORD
EXEC(@.SQLSTRING)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks a lot! Just in case anybody else has this problem, here's some code that does what I wanted.

declare @.USERNAME varchar(50);

declare @.PASSWORD varchar(50);

set @.USERNAME = 'testUserName2';

set @.PASSWORD = 'testPassword';

DECLARE @.SQLString VARCHAR(400)

SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + ' WITH PASSWORD = ' + CHAR(39) + @.PASSWORD + CHAR(39)

EXEC(@.SQLSTRING)

|||

You need to protect the dynamic SQL against SQL injection attacks. So you need to quote the login name which is an identifier otherwise potentially someone could provide a malicious login name which can be used to do attack the database. The password part is hard to protect since it is just a string. So you will have to validate it for certain characters in the front-end.

declare @.USERNAME varchar(50);

declare @.PASSWORD varchar(50);

set @.USERNAME = quotename('testUserName2'); -- Use quotename to form the identifier

set @.PASSWORD = 'testPassword';

DECLARE @.SQLString VARCHAR(400)

SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + ' WITH PASSWORD = ' + CHAR(39) + @.PASSWORD + CHAR(39)

EXEC(@.SQLSTRING)

|||Thanks for the tip. We're currently protecting against it on the front end but I recently heard there are steps I can take in the database for protection, so I'll have to look into that. Thanks.

dynamic Instead of update trigger problem

Hello,

I am trying to use an instead of update trigger to alter a lastupdated field in the table when any of the other fields are modified by an update. I want to do this as I can't rely on the applications updating the date when they alter other data. The trigger looks something like this.

CREATE TRIGGER [noidentitytableinsteadupdate] ON [dbo].[noidentitytable]

INSTEAD OF UPDATE

AS

if(not update(updatedat))

begin

update noidentitytable

set text = inserted.text,

state = 'updated',

updatedat = GetDate()

from noidentitytable

INNER JOIN inserted ON (noidentitytable.id = inserted.id)

end

else

update noidentitytable

set text = inserted.text,

state = 'updated',

updatedat = inserted.updatedat

from noidentitytable

INNER JOIN inserted ON (noidentitytable.id = inserted.id)

The problem I am facing is when you add say another 10 fields to the table, and I want to ONLY do an update to the fields that have been updated by the original statement. In effect I want to check each column using update(column) and include it in the new update statement along with the lastupdate field. Dynamic sql does not have access to the inserted table so how would you do this?

The reason I want to exclude all the unmodified fields is because of existing after triggers that fire based on changes to individual column changes. I tried this in an after trigger originally but as this is done as an additional update this causes multiple fires of other triggers.

If the original update was

update noidentitytable set text = 'hello', state = 'updated',

but there were actually 10 columns in the table then the actual update I want to do is.

update noidentitytable set text = inserted.text, state = 'updated', updatedat = GetDate() from noidentitytable INNER JOIN inserted ON (noidentitytable.id = inserted.id)

Or perhaps what I really want to do is get hold of the original SQL and insert text change the datefield.

Can someone enlighted me please?

It would definitely be better to do this in an after trigger. Can't you just add a condition to all the other after triggers to do nothing if the updatedate column is the one being updated?|||

Why would you want to reissue the UPDATE statement again? It takes lot of work to perform an update and you will just make the entire UPDATE statement slower. It seems like you should just create a SP that performs the necessary UPDATE statement, add default on the updatedat column and use DEFAULT keyword in the UPDATE statement. This will be much more scalable, run more efficiently and easy to manage.

|||

I understand that you have to issue the update statement yourself in a instead of trigger, or am I missing a way of committing the original statement first?

I do use the default column value for the date, however setting the date to the default on the update is not much different than setting to GetDate().

what I really wanted was all the values that have changed from the inserted table plus some other default information ie date, user who modified.

|||

Perhaps I should give a different scenario that would require the same solution.

If I was using an instead of update trigger on a view and the underlying table structure changed but to remain backward compatible I left the view definition the same how would you go about ensuring only the fields that the client was updating were updated correctly in the underlying tables?

I would assume this would require some dynamic sql to check the updated columns and then update the tables underneath. Surely you would not update every column even if the value had not changed?

Dynamic If Update() in Trigger - Urgent!

Hi All

I have a question about generating dynmamicly If Update() statement in a
trigger..

in My db, there is a table that holds some column names of an another table.
for example;

Columns Table-A: Col1, Col2, Col3, Col4,Col5

Table-B: Col2, Col5 (The selected columns of Table A)

Then, in the Trigger of Table-A I use;

Select name from syscolumns where id=object_id('Table-A')

fetch next from TableA_Cursor into @.strColName

then, I used a statement like this..

if UPDATE(' + @.strColName + ')

But it gives "incorrect syntax" error..

How can I write this line?

Thanks alot in advance...

--
Message posted via http://www.sqlmonster.comI don't believe it is possible to use IF UPDATE() dynamically, nor is
it necessary. It also doesn't really make much sense to reference IF
UPDATE() in a cursor since the result will be the same for every row.
Anyway you shouldn't use cursors in triggers - they just turn your
set-based update statements into row-based updates, which is bad in
principle from a design poiunt of view and generally performs very
poorly.

If you want your triggers to take account of table structure changes
then generate the trigger code dynamically at DESIGN time rather than
runtime.

If you need a trigger to act on what data has changed then join the
Inserted and Deleted virtual tables and compare the columns. IF UPDATE
doesn't tell you what changed, only which columns were referenced by
the update statement.

If you need more help, please post a fuller description of your problem
including DDL and sample data.

--
David Portas
SQL Server MVP
--

dynamic audit trigger

Hi. I'm trying to create a dynamic audit trigger for a rather large datbase.
the trigger should be able to dynamically read the field names form the
deleted tabel and check each column for updates to store them to a table (i
only want to store fieldnames and values that changed)
the spilified table looks like:
date --the date of the update
field --the updated column
value --the inserted value
I can read column names, from deleted and check for updates by comparing to
inserted. the problem i have is the retrieval of the columns value.
How do i fetch the value of one single column from the deleted table if i
have stored the columns name in a variable as i cannot access deleted via
sp_execute?
this does not work:
select @.fieldName = name from syscolumns where colid=@.field and...
declare @.sql nvarchar(4000);
declare @.params nvarchar(4000);
set @.sql = 'select @.val = cast('+@.fieldname+' as nvarchar(3000)) from
deleted';
set @.params = '@.val nvarchar(3000) OUTPUT';
exec sp_executesql @.sql, @.params, @.val=@.value OUTPUT;
anyone any idea'
thanks so farhow did you check for the updates by comparing with the inserted table.
Can you paste the script for that?|||Hi I think i solved the problem storing the values of deleted to a temporary
table. not quite what i wanted but it works.
here's the script i use anyway
CREATE TRIGGER dbo.mytable_trigger ON dbo... FOR UPDATE
AS
DECLARE @.bit INT,
@.field INT,
@.char INT,
@.fieldName NVARCHAR(50),
@.modificationDate DATETIME,
@.contextId BIGINT,
@.value NVARCHAR(3000),
@.value2 NVARCHAR(3000),
@.maxCols INT
SET @.field = 0;
--the modification date is used to indicate date and time the update occured
SET @.modificationDate = GETDATE();
--DROP THE TEMPORARY TABLE WHICH STORES THE VALUES FROM deleted IF IT EXISTS
IF EXISTS(SELECT id FROM sysobjects WHERE NAME='tmpDel') DROP TABLE tmpDel;
--STORE VALUES FROM updated COLS in a temporary table
SELECT * INTO tmpDel FROM deleted;
--iterate throug all columns of the source table and check the values
against the deleted table to
--find the cols updated
--store the cols to a history table using a stored procedure
WHILE @.field < (SELECT MAX(colid) FROM syscolumns WHERE id = (SELECT id FROM
sysobjects WHERE name = 'mytable')) BEGIN
--DEBUG OUTPUT
--print 'field: '+cast(@.field as nvarchar(50));
--calculate the field id's (for details refer to:
http://msdn2.microsoft.com/de-de/library/ms186329.aspx)
SET @.field=@.field+1;
SET @.bit = (@.field-1)%8+1;
SET @.bit = power(2, @.bit-1);
SET @.char = ((@.field - 1) / 8) + 1
--check if a column has been updated and perform the compare operation here
IF substring(COLUMNS_UPDATED(),@.char, 1) & @.bit > 0 BEGIN
SELECT @.field, name FROM syscolumns WHERE colid=@.field AND id = (SELECT id
FROM sysobjects WHERE name = 'mytable')
-- select the ucrrent fieldName
select @.fieldName = name FROM syscolumns WHERE colid=@.field AND id =
(SELECT id FROM sysobjects WHERE name = 'mytable');
--DEBUG OUTPUT
--print 'fieldname: '+@.fieldName
SELECT @.contextId = id FROM deleted;
--DEBUG OUTPUT
--print 'contextid: "'+cast(@.contextId as nvarchar(10));
--as a variable cannot be used to select a col by its name stored in the
variable
--use dynamic sql here.
--the field to select is written by a variable to an sqlString
--within the sql string a variable called @.val is definded as an output
parameter
--to avoid type problems all selected values are casted to nvarchar
--via sp_executesql the statement is executed and the variable assigned.
DECLARE @.sql NVARCHAR(4000);
DECLARE @.params NVARCHAR(4000);
--select the actual value for the field
SET @.sql = 'select @.val = cast('+@.fieldname+' as nvarchar(3000)) from
mytable';
SET @.params = '@.val nvarchar(3000) OUTPUT';
EXEC sp_executesql @.sql, @.params, @.val=@.value OUTPUT;
--select the fields old value
SET @.sql = 'select @.val = cast('+@.fieldname+' as nvarchar(3000)) from
tmpDel';
SET @.params = '@.val nvarchar(3000) OUTPUT';
EXEC sp_executesql @.sql, @.params, @.val=@.value2 OUTPUT;
--if the values do not match the column has been updated
IF(@.value <> @.value2) BEGIN
--calll stored proc to save values to history
EXEC dbo.HistorySave4Table 'dbo.[mytable-History]', @.modificationDate,
@.contextId, null, @.fieldName, @.value2;
END
end
end
--DROP THE TEMPORARY TABLE WHICH STORES THE VALUES FROM deleted IF IT EXISTS
IF EXISTS(SELECT id from sysobjects where name='tmpDel') drop table tmpDel;
"Omnibuzz" wrote:

> how did you check for the updates by comparing with the inserted table.
> Can you paste the script for that?
>