Create table with Encrypted passwords

Hello,
I need to create one table where i want to add records
with users and corresponding passwords but these passwords
must appear encrypted.
How can i do this? and if it is possible how can i decrypt
these passwords.
I need to do it but i cant put in risk the security of my
databases. Is it possible?
Best regards
This link will give you an overview of column level encryption:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c14201c45211$ab44f000$a101280a@.phx.gbl...
Hello,
I need to create one table where i want to add records
with users and corresponding passwords but these passwords
must appear encrypted.
How can i do this? and if it is possible how can i decrypt
these passwords.
I need to do it but i cant put in risk the security of my
databases. Is it possible?
Best regards
|||Hi,
The PWDENCRYPT and PWDCOMPARE functions are used to encrypt and compare DATA
passwords are not visible in anywhere in the database.
FYI, PWDENCRYPT and PWDCOMPARE are undocumented functions , so it can change
in future versions.
Sample code to create table and encypt the password column and usage:-
Create table users ( userid int identity (1,1) not null,
pswd varbinary (128))
-- INSERTING ENCRYPED value
-- hard coded string should be replace
-- by a text box value from screen
Insert into users values (PWDENCRYPT ('hari prasad'))
declare @.pwd varbinary(128) , @.chk tinyint
-- the dencryption phase
select @.pwd=pswd from users where userid = 1
-- comparing : 1 is success, 0 is not
select @.chk=PWDCOMPARE ('hari prasad',@.pwd)
if @.chk ! = 1
Print 'Wrong Password Entered! Try Again'
else
Print 'Login Successfully'
Do a check inside application , if the value returned is "1" allow to
login.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c14201c45211$ab44f000$a101280a@.phx.gbl...
> Hello,
> I need to create one table where i want to add records
> with users and corresponding passwords but these passwords
> must appear encrypted.
> How can i do this? and if it is possible how can i decrypt
> these passwords.
> I need to do it but i cant put in risk the security of my
> databases. Is it possible?
> Best regards
|||Thanks Narayana
Best regards

>--Original Message--
>This link will give you an overview of column level
encryption:
>http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c14201c45211$ab44f000$a101280a@.phx.gbl...
>Hello,
>I need to create one table where i want to add records
>with users and corresponding passwords but these passwords
>must appear encrypted.
>How can i do this? and if it is possible how can i decrypt
>these passwords.
>I need to do it but i cant put in risk the security of my
>databases. Is it possible?
>Best regards
>
>.
>

Create table with Encrypted passwords

Hello,
I need to create one table where i want to add records
with users and corresponding passwords but these passwords
must appear encrypted.
How can i do this? and if it is possible how can i decrypt
these passwords.
I need to do it but i cant put in risk the security of my
databases. Is it possible?
Best regardsThis link will give you an overview of column level encryption:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c14201c45211$ab44f000$a101280a@.phx.gbl...
Hello,
I need to create one table where i want to add records
with users and corresponding passwords but these passwords
must appear encrypted.
How can i do this? and if it is possible how can i decrypt
these passwords.
I need to do it but i cant put in risk the security of my
databases. Is it possible?
Best regards|||Hi,
The PWDENCRYPT and PWDCOMPARE functions are used to encrypt and compare DATA
passwords are not visible in anywhere in the database.
FYI, PWDENCRYPT and PWDCOMPARE are undocumented functions , so it can change
in future versions.
Sample code to create table and encypt the password column and usage:-
Create table users ( userid int identity (1,1) not null,
pswd varbinary (128))
-- INSERTING ENCRYPED value
-- hard coded string should be replace
-- by a text box value from screen
Insert into users values (PWDENCRYPT ('hari prasad'))
declare @.pwd varbinary(128) , @.chk tinyint
-- the dencryption phase
select @.pwd=pswd from users where userid = 1
-- comparing : 1 is success, 0 is not
select @.chk=PWDCOMPARE ('hari prasad',@.pwd)
if @.chk ! = 1
Print 'Wrong Password Entered! Try Again'
else
Print 'Login Successfully'
Do a check inside application , if the value returned is "1" allow to
login.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c14201c45211$ab44f000$a101280a@.phx.gbl...
> Hello,
> I need to create one table where i want to add records
> with users and corresponding passwords but these passwords
> must appear encrypted.
> How can i do this? and if it is possible how can i decrypt
> these passwords.
> I need to do it but i cant put in risk the security of my
> databases. Is it possible?
> Best regards|||Thanks Narayana
Best regards
>--Original Message--
>This link will give you an overview of column level
encryption:
>http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1c14201c45211$ab44f000$a101280a@.phx.gbl...
>Hello,
>I need to create one table where i want to add records
>with users and corresponding passwords but these passwords
>must appear encrypted.
>How can i do this? and if it is possible how can i decrypt
>these passwords.
>I need to do it but i cant put in risk the security of my
>databases. Is it possible?
>Best regards
>
>.
>

Create table with Encrypted passwords

Hello,
I need to create one table where i want to add records
with users and corresponding passwords but these passwords
must appear encrypted.
How can i do this? and if it is possible how can i decrypt
these passwords.
I need to do it but i cant put in risk the security of my
databases. Is it possible?
Best regardsThis link will give you an overview of column level encryption:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c14201c45211$ab44f000$a101280a@.phx
.gbl...
Hello,
I need to create one table where i want to add records
with users and corresponding passwords but these passwords
must appear encrypted.
How can i do this? and if it is possible how can i decrypt
these passwords.
I need to do it but i cant put in risk the security of my
databases. Is it possible?
Best regards|||Hi,
The PWDENCRYPT and PWDCOMPARE functions are used to encrypt and compare DATA
passwords are not visible in anywhere in the database.
FYI, PWDENCRYPT and PWDCOMPARE are undocumented functions , so it can change
in future versions.
Sample code to create table and encypt the password column and usage:-
Create table users ( userid int identity (1,1) not null,
pswd varbinary (128))
-- INSERTING ENCRYPED value
-- hard coded string should be replace
-- by a text box value from screen
Insert into users values (PWDENCRYPT ('hari prasad'))
declare @.pwd varbinary(128) , @.chk tinyint
-- the dencryption phase
select @.pwd=pswd from users where userid = 1
-- comparing : 1 is success, 0 is not
select @.chk=PWDCOMPARE ('hari prasad',@.pwd)
if @.chk ! = 1
Print 'Wrong Password Entered! Try Again'
else
Print 'Login Successfully'
Do a check inside application , if the value returned is "1" allow to
login.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:1c14201c45211$ab44f000$a101280a@.phx
.gbl...
> Hello,
> I need to create one table where i want to add records
> with users and corresponding passwords but these passwords
> must appear encrypted.
> How can i do this? and if it is possible how can i decrypt
> these passwords.
> I need to do it but i cant put in risk the security of my
> databases. Is it possible?
> Best regards|||Thanks Narayana
Best regards

>--Original Message--
>This link will give you an overview of column level
encryption:
>http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"CC&JM" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1c14201c45211$ab44f000$a101280a@.phx
.gbl...
>Hello,
>I need to create one table where i want to add records
>with users and corresponding passwords but these passwords
>must appear encrypted.
>How can i do this? and if it is possible how can i decrypt
>these passwords.
>I need to do it but i cant put in risk the security of my
>databases. Is it possible?
>Best regards
>
>.
>

Create table + index + primary

for MS SQL 2000
how can I do this in one time (into the CREATE TABLE)

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL ,
[Name] [nvarchar] (100) NULL,
[Serial] [nvarchar] (100) NULL,
) ON [PRIMARY]

ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[id_Users]
) ON [PRIMARY]

CREATE UNIQUE INDEX [IX_Users] ON [Users]([Serial]) ON [PRIMARY]

and that one

CREATE TABLE [dbo].[UsersExtra] (
[id_Users] [int] NOT NULL
) ON [PRIMARY]

ALTER TABLE [dbo].[UsersExtra] ADD
CONSTRAINT [FK_UsersExtra_Users] FOREIGN KEY
(
[id_Users]
) REFERENCES [Users] (
[id_Users]
) ON DELETE CASCADE

thank youi am getting an error

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL PRIMARY KEY clustered,
[Name] [nvarchar] (100) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[UsersExtra] (
[id_UsersExtra] [int] NOT NULL REFERENCES [Users].[id_Users] ON DELETE CASCADE
) ON [PRIMARY]

Msg 1767, Level 16, State 0, Line 50
Foreign key 'FK__Users__id_Co__05D9AC15' references invalid table 'Users.id_Users'.
Msg 1750, Level 16, State 0, Line 50|||you are trying to reference a non-existing table, 'Users.id_Users'|||but I have created it just before

CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL PRIMARY KEY clustered,
[Name] [nvarchar] (100) NULL
) ON [PRIMARY]|||To get back on the first question ...
You can't create a table and an index in one single statement, except when that index is the PK ... so more than one statement is required to get the job done. You can however combine the create and alter table statements into one. See BOL 'create table'.

Gr,
Yveau|||Add Go and try it again|||but I have created it just beforeyes, you did

however, that is not the source of your problem

you said REFERENCES [Users].[id_Users]

this is invalid syntax, because it is trying to reference a table called "id_Users" belonging to user called "Users"

for the correct syntax, please see the manual

:)

create systemtables?

Is there any way to create a systemtable?
I need to hide a table of my own from regular users. This table is not
temporary.
Kind regards,
Costi StanFor SQL Server 2000, It won't necessarily keep users from
being able to find out the table exists. You could use an
undocumented, unsupported method using the system stored
procedure sp_MS_marksystemobject. Not sure why you would as
you could also just create a table with a name that has no
meaning to the users and prevent access using built in
security methods. But something like:
CREATE TABLE UserSystem
(SomeID int primary key not null,
SomeCol varchar(25))
GO
EXEC sp_MS_marksystemobject 'UserSystem'
GO
-Sue
On Tue, 29 Aug 2006 17:24:28 +0300, "Costi Stan"
<stancosti@.yahoo.com> wrote:
>Is there any way to create a systemtable?
>I need to hide a table of my own from regular users. This table is not
>temporary.
>Kind regards,
>Costi Stan
>

create systemtables?

Is there any way to create a systemtable?
I need to hide a table of my own from regular users. This table is not
temporary.
Kind regards,
Costi StanFor SQL Server 2000, It won't necessarily keep users from
being able to find out the table exists. You could use an
undocumented, unsupported method using the system stored
procedure sp_MS_marksystemobject. Not sure why you would as
you could also just create a table with a name that has no
meaning to the users and prevent access using built in
security methods. But something like:
CREATE TABLE UserSystem
(SomeID int primary key not null,
SomeCol varchar(25))
GO
EXEC sp_MS_marksystemobject 'UserSystem'
GO
-Sue
On Tue, 29 Aug 2006 17:24:28 +0300, "Costi Stan"
<stancosti@.yahoo.com> wrote:

>Is there any way to create a systemtable?
>I need to hide a table of my own from regular users. This table is not
>temporary.
>Kind regards,
>Costi Stan
>

create login alias DBO

Hi:

When I set up databases for my users, I create them a user and alias it as the DBO. They can then use this user in their code.

The problem is that if a user is aliased as a DBO, they can change the size of their database, create jobs, backup their database, set up replication (from their own sql server), etc...

Is there a different way to create their login (with minimal administrative effort) to give them access to create objects in their database, without allowing them access to these other tasks?

Thanksyou can use the database role'db_ddladmin'. This role is allowed to create, modify and drop all database objects, but cannot issue security-related commands (grant, ...).|||Originally posted by jora
you can use the database role'db_ddladmin'. This role is allowed to create, modify and drop all database objects, but cannot issue security-related commands (grant, ...).

And all objects will belong to this user: like tom.newtable. It needs to be careful - there is possibility to have couple tables with the same name but with different owners.

BOL:For example, if user Andrew is a member of the sysadmin fixed server role and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The table belongs to Andrew because he did not qualify the table as dbo.T1.|||I agree about the owner of the objects. As I understood the mail though (and I could be wrong offcourse) tommy only used the dbo user so the login was able to create objects. Thefore I see no problem, the user can still use his login in his/her applications.|||Thanks to all of you for your input. I love this forum, everyone is so helpful!

Ok, moving forward, may be able to use that type of login. However, I can see where it would mess up object owners.

For now, I will start by denying users to certain functions I want to lock out. For example, DENY BACKUP loginname.

Does anyone know how to deny a user from creating a job? I can not find it in BOL.|||I just remove the guest user from msdb, myself. So long as no one needs to save DTS packages there, it should work out ok.|||MCrowley, you rock! Great idea, I actually have denied access to create DTS packages on the server. One question, however, if I remove the quest user from the MSDB, will users still be able to DTS from their OWN machine? For example, they schedule a package on their own machine that pulls down updates, or whatever. Also exporting tables from their own database?

I'm going to test now.

Thanks!|||I removed the Guest login from the MSDB database, and the user (aliased as DBO of their own database), and still change the size of their database, log, and can still backup their database. It throws errors, that it can't write to the MSDB database, and the user is not a "user" in the MSDB, but it still works. It does, however, disallow them from creating jobs on the server!

Any other ideas on how to deny users changing the size of their database and backing it up? I tried:
USE DBNAME
GO
DENY BACKUP DATABASE TO LoginName
But since the login is aliased as a DBO, I get the error:
There is no such user or group LoginName.|||I just ran a test. You can take the users out of the db_owner role and put them in the db_ddladmin and db_securityadmin roles. You can even create your own special "db_owner" role that includes these for ease of administration. This will grant them the ability to create tables as dbo, but as Snail pointed out they will more than likely create tables in their own schemas only. The developers are going to have to be trained to specify the owner while creating objects. As this is possible when you add the user to the db_owner role, anyway, there should be only a few problems to start that gradually go away.
Hope this helps you in your problem.|||MCrowley, thanks for the info, I will experiement with that moving forward. I do have a bunch of databases that are already in place, and I can't make a drastic change like that right now.

Remember that these users are aliased as the DBO, they are not the DBO, sa is the DBO. I need to figure out how to stop users from backing up, restoring, and changing the size of their db.

Thanks|||In the MSDB I denied the public role access to the sp_add_job, and this made it so they can not add jobs to the server! Great!

Now, I just have to figure out how to prevent them from backing up and changing the size (or recovery model) or their database.|||Preventing them from changing the recovery model should be easy enough.
revoke exec on sp_dboption from public

This may have some uninteded consequences, as Enterprise Manager most likely uses this procedure to display information as well as change it. If your users are using EM for all their DBAdmin needs, then they will be getting all sorts of errors when the look for database properties. This may not be a problem for you,a s you are trying to restrict this stuff, anyway.

In order to prevent the backing up of databases/transaction logs, you are going to have to take these guys out of db_owner. I do not know of any other way of restricting that functionality (which, naturally does not mean a way doesn't exist...).

Create from template database?

I have a database that needs to be created over and over (with dozens of pre-populated tables & procedures) for various users of a software application. These databases must remain distinct. Currently, the table/procedure data is maintained in Create and Insert scripts (approx 15,000 lines total) that we run as needed, but maintaining these scripts in unwieldy at best.

Ideally, I would like to create each new database from a template database. This way, we could forget the scripts entirely, and make future modifications directly to the template, and have them automatically bubble into all future databases created from it.

I know this is how the "model" system database works, and think it is great! Make some change in model, and every new database is created with that change. Perfect, except that we cannot guarantee that our software application will be the only one using some instance of SQL (SS2K5), or that our databases will be the only ones inheriting from model.

So, instead of having EVERY new database inherit from model, is there a way to specify that only certain databases inherit from 'model' while others inherit from 'custom_model'? Is there a way to specify a database to use some template in the create process (maybe similar to in postgresql)? Ideas? Thanks, -Matthew_53

Unfortunately, there is no 'conditional' way to use multiple [Model] databases.

However, you can easily create your 'template' database, and when you need another database derived from that template. In the object explorer of SSMS, right-click on the template database and select [Tasks], then select [Copy Database...]. Follow the steps throught the Copy Database Wizard.

|||Right, but is there a way to do this programatically, from within our application, whenever we need to generate a 'new' database?|||Programmatically, you could DETACH the template database, then xcopy it with a new db name, and then re-ATTACH both of them. (Actually, unless you need to continually 'tweak' the template database, it doesn't even need to be attached. Just copy the *.mdf file to a new name and ATTACH the new database.)

Create from template database

I have a database that needs to be created over and over (with dozens of pre-populated tables & procedures) for various users of a software application. These databases must remain distinct. Currently, the table/procedure data is maintained in Create and Insert scripts (approx 15,000 lines total) that we run as needed, but maintaining these scripts in unwieldy at best.

Ideally, I would like to create each new database from a template database. This way, we could forget the scripts entirely, and make future modifications directly to the template, and have them automatically bubble into all future databases created from it.

I know this is how the "model" system database works, and think it is great! Make some change in model, and every new database is created with that change. Perfect, except that we cannot guarantee that our software application will be the only one using some instance of SQL (SS2K5), or that our databases will be the only ones inheriting from model.

So, instead of having EVERY new database inherit from model, is there a way to specify that only certain databases inherit from 'model' while others inherit from 'custom_model'? Is there a way to specify a database to use some template in the create process (similar to in postgresql)? Ideas? Thanks, -Matthew_53

Just thinking out loud really, but you could install a second SQL Server instance (possibly on the same server - limiting the memory available to the second instance, if appropriate) and maintain the model database within that instance.

Your database creation process would then be to create the database on the new instance, detach, then attach to the new instance - you could do this by running just one script per instance or even by setting up a linked server and performing the actions with a single script. If you set the default Data and Log folders to the same folders as the existing instance then you wouldn't even have to move any files around.

If you lock down the security on the second instance you can limit who has access to modify the model database.

Chris

|||Better yet, why not just create the template/model database on the one instance, then detach it and store the data and log files away for safe keeping? Then any time you wanted to clone it, it would only be a matter of duplicating these files into the appropriate data directories, and attaching them with the desired database name.

You could also achieve the same effect using a backup/restore, as long as you're using the WITH MOVE clause during the restore. This way, you could leave the template database online and maintain it easily, at the expense of increased cloning times.

Either approach could be made pretty turn-key with some scripts containing an appropriate mix of BACKUP, RESTORE, sp_attach, sp_detach, xp_cmdshell (to copy files), etc.