Re: user defined functions in sql server 2000 - Database Discussion Boards

md_refay22-Jun-06 1:38

22-Jun-06 1:38

i work in hr sqlserver database
in employees table i have password column so every one have permission to open database can see all the passwords i wnat toedit the column password so who open employyes table see symblos in password column not the real password

ma_refay

Re: password?!!!

Colin Angus Mackay22-Jun-06 2:00

Colin Angus Mackay

22-Jun-06 2:00

Do you mean you want to encrypt the passwords? Do you want to change the permissions so that only valid users can access the passwords?

Changing it so that it just displays symbols is not going to happen in SQL Server - Its function is to serve data, not to define how it is displayed. That is up to the applications that access the database. Perhaps you want to change them instead.

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Re: password?!!!

r.stropek22-Jun-06 3:13

r.stropek

22-Jun-06 3:13

Hi!

Check my blog at http://www.cubido.at/Blog/tabid/176/EntryID/34/Default.aspx. There I show how you can encrypt and decrypt the password column with SQL2005.

If you do not have SQL2005 but 2000 you do not have these smart encryption functions built into the DB. You would have to implement encryption yourself (e. g. using .NET's encryption functions). In fact for a password it would be enough to calculate a (salted) hash (see http://en.wikipedia.org/wiki/Salt_%28cryptography%29 for details) -> no encryption needed.

A completly unsecure solution that just displays the password in binary instead of cleartext is to change the data type of the password column to varbinary. A SELECT will then just show hex values. Again, this is NOT encryption. Everyone how is able to write SQL can write cast(password as varchar) and sees the password. However, sometimes it is enough to just changing the display format of the password to binary.

Regards,
Rainer.

Rainer Stropek
Visit my blog at http://www.cubido.at/rainers

Re: password?!!!

Colin Angus Mackay22-Jun-06 5:53

Colin Angus Mackay

22-Jun-06 5:53

r.stropek wrote:
However, sometimes it is enough to just changing the display format of the password to binary.

This is not a display format - it is a storage format. The database knows nothing about how to display the data, that is the function of the application that is connected to the database.

Re: password?!!!

r.stropek24-Jun-06 23:50

r.stropek

24-Jun-06 23:50

You're right, this is also a storage format. However, as far as I understood the question the application is Microsoft's enterprise manager or management studio -> you do not have the possiblitiy to change the display format without changing the storage format. If the original question did not regard to pre-built standard software but to individually developed software I aggree to you.

Regards,
Rainer.

Rainer Stropek
Visit my blog at http://www.cubido.at/rainers

user defined functions in sql server 2000

Swaroop Roy22-Jun-06 1:35

Swaroop Roy

22-Jun-06 1:35

how to build and execute a query in user defined functions?

Swaroop.

Re: user defined functions in sql server 2000

Colin Angus Mackay22-Jun-06 1:58

Colin Angus Mackay

22-Jun-06 1:58

Swaroop Roy wrote:
how to build and execute a query in user defined functions?

With such a vague question I can only really point you to the documentation:

* User Defined Functions[^]
* CREATE FUNCTION[^]

Does this help?

Re: user defined functions in sql server 2000

r.stropek22-Jun-06 3:03

r.stropek

22-Jun-06 3:03

Hi!

It is not supported to build and execute a dynamic query in user defined functions. UDFs have to be deterministic -> no exec( @MyVar ) allowed.

Rainer.

Rainer Stropek
Visit my blog at http://www.cubido.at/rainers

Sound?!

md_refay22-Jun-06 1:31

md_refay

22-Jun-06 1:31

i have sqlserver data base and i want to store sounds in the database and reterive these sound how?!!!!!!!!!!!!!!

ma_refay

Re: Sound?!

Colin Angus Mackay22-Jun-06 1:55

Colin Angus Mackay

22-Jun-06 1:55

Jeez - Use your imagination! I told you recently how to insert a graphic. And I told you that you convert it to a byte array in order to insert it in the database. The principle with a sound (or a word document, or a PDF, or a video, or an infinite number of other things) is exactly the same, as is the SQL needed. So long as you have a byte array of something you can store it in SQL Server in an image column. Just as I said already.

If you have some sound object and you need that in a byte array then that is no longer a SQL question and should be directed at the appropriate forum.

Setting Password to the Database

meetbinu200321-Jun-06 23:36

meetbinu2003

21-Jun-06 23:36

hai

I want to protect my database from anyone other than i. How I can set a password to my single Database. This is in Intranet. I have sql server username and Password(ie Sa and some pwd).
Pls help me

Re: Setting Password to the Database

Colin Angus Mackay22-Jun-06 2:04

Colin Angus Mackay

22-Jun-06 2:04

In that case ensure that your account is the only login that is granted access to that datbase.

get list of active connections on a database

flippydeflippydebop21-Jun-06 23:32

flippydeflippydebop

21-Jun-06 23:32

does anyone know using SQL server 2000 a select statement or system table to look up the active connections to a database

Thanks in advance.

Re: get list of active connections on a database

Eric Dahlvang22-Jun-06 7:07

Eric Dahlvang

22-Jun-06 7:07

sp_who

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Adding Auto-Increment ID in both parent and child table

@myg@l21-Jun-06 9:48

@myg@l

21-Jun-06 9:48

Hie,

I tried out samples in MSDN regarding how to enable autoincrement of an ID through Stored Procedure, it works. Now I'm having doubts where I wanted to insert the ID which is auto-incremented in parent table when a new record is added, to be inserted in the child table.
I just make an illustration for example, StaffAccount Table (Parent Table)having StaffID as primary key, it is auto-incremented using stored procedures-succesfully completed, now the StaffID is the foreign key in Login Table(Child Table), I want the StaffID to be added automaticallly in child table once a new record has been inserted in parent table.

How is this possible?

Thanks in advance for helping in reading this.
=)

best regards amygal

Re: Adding Auto-Increment ID in both parent and child table

r.stropek21-Jun-06 21:41

r.stropek

21-Jun-06 21:41

Hi!

Your stored proc could look like this:

create procedure AddNewPerson
  @Name varchar(50)
as
  set nocount on

  insert into StaffAccountTable ( StaffName ) values ( @Name )
  insert into Login ( StaffID, ... ) values ( @@identity, ... )
go

Hope this helps.

Regards,
Rainer

Rainer Stropek
Visit my blog at http://www.cubido.at/rainers

Re: Adding Auto-Increment ID in both parent and child table

@myg@l22-Jun-06 5:32

@myg@l

22-Jun-06 5:32

Hi Rainer,
thanks for the reply
but it seemed having some prob:
I had tried the stored procedure u had specify, but is generate error says that no null value can be inserted into the Login Table. Alright, I had set the StaffID in child table of Login AS Not Allow Null, I guess tis is the prob, but yet if i changed the setting to Set as Allow Null, the result the same- IT does not insert the auto-generated StaffID in (Staff Record)Parent Record to the (Login)child table.
Here is my Procedure

Create PROCEDURE InsertStaffAccount
@StaffName varchar(20),
@Gender char(10),
@Address varchar(50),
@Position varchar(20),
@Identity int OUT

AS
SET nocount On
INSERT INTO StaffAccount(StaffName,Gender,Address,Position) VALUES (@StaffName,@Gender,@Address,@Position)
INSERT INTO StaffLogin(StaffID,Password) VALUES (@@Identity,@Identity)
SET @Identity = SCOPE_IDENTITY()
go

any Idea??

Re: Adding Auto-Increment ID in both parent and child table

Eric Dahlvang23-Jun-06 3:55

Eric Dahlvang

23-Jun-06 3:55

It looks to me like the @Identity output parameter is the problem. It is NULL at the time that you insert into StaffLogin.

Are you trying to put a password into the StaffLogin table?

What value do you want the password to be?

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: Adding Auto-Increment ID in both parent and child table

@myg@l25-Jun-06 22:15

@myg@l

25-Jun-06 22:15

yes, the password is one of the field in staffLogin table.
My intention is Once a new Staff Record is added, StaffID will be autogenerated and saved the new record in Staff Table.

In the same time, I wish the StaffLogin Table will be populated with the new StaffID as autogenerated in Staff Table and together with the password. Of course, since it is a new staff record, the Stafflogin table does not have the value of staffID and the password yet, it is added as a new record only in the same time as New Staff Record is added in the Staff Account. How is this possible?

The value of the password for the initial add record purpose is similar as the staffID which is auto-generated in the Staff Table, then i will add code to allow user to change the vaue of password on their first login.How is this possible?

thanks again
best regard
amy

Re: Adding Auto-Increment ID in both parent and child table

Eric Dahlvang26-Jun-06 5:59

Eric Dahlvang

26-Jun-06 5:59

CREATE PROCEDURE InsertStaffAccount
@StaffName VARCHAR(20),
@Gender CHAR(10),
@Address VARCHAR(50),
@Position VARCHAR(20),
@Identity INT OUT
AS
SET NOCOUNT ON

INSERT INTO StaffAccount(StaffName,Gender,Address,Position) VALUES (@StaffName,@Gender,@Address,@Position)
SET @Identity = SCOPE_IDENTITY()
INSERT INTO StaffLogin(StaffID,Password) VALUES (@Identity ,str(@Identity))
GO

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: Adding Auto-Increment ID in both parent and child table

@myg@l28-Jun-06 3:57

@myg@l

28-Jun-06 3:57

Hie EricDV

Thanks alot for the help. It really workos. The auto-generated staffID does populate the Login Table. But there is one weird thing, why the paswword seemed to have extra space infront of the StaffID? Eg. *****10011 * represent the space why is it so?
how to eliminate the extra space in front of the password??

thanks

best regard
amygal

Re: Adding Auto-Increment ID in both parent and child table

Eric Dahlvang28-Jun-06 4:16

Eric Dahlvang

28-Jun-06 4:16

LTRIM(STR(@Identity))

You're welcome. Isn't this fun?

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: Adding Auto-Increment ID in both parent and child table

@myg@l28-Jun-06 5:18

@myg@l

28-Jun-06 5:18

hey EricDV
It's really cool, =) juz a simple line of code. very bravo of u
Everything working fine now exept there is still some small msg box pop up.
A msgbox pop up with " Procedure or function 'InsertStaffAccount expect parameter '@staffName' which was not supplied. Is that a bug?
how to make it dissapear as evryhign seemed to work fine now

thanks again
very kind and helpful of u
=)

best regards
amygal

Re: Adding Auto-Increment ID in both parent and child table

Eric Dahlvang28-Jun-06 5:30

Eric Dahlvang

28-Jun-06 5:30

@myg@l wrote:
A msgbox pop up with " Procedure or function 'InsertStaffAccount expect parameter '@staffName' which was not supplied. Is that a bug?

Yes. a few posts back, you defined your stored proc with these parameters:
@StaffName varchar(20),
@Gender char(10),
@Address varchar(50),
@Position varchar(20),
@Identity int OUT

You need to supply them when you call the procedure.

----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: Adding Auto-Increment ID in both parent and child table

@myg@l28-Jun-06 5:46

@myg@l

28-Jun-06 5:46

hie again
=)
yea i had added the stored procedure with the parameters above, but i cant get u bout to supply them when i call the procedure
any example??

*_*
amygal

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.