INSERT problem and GRANT problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

INSERT problem and GRANT problem

Author

Topic

Ufin
Starting Member

7 Posts

Posted - 2011-06-02 : 17:28:58

Hi guys and Gurus, I have 3 questions (hope easy for you)

1 question) I have 3 tables with a lot of records. Two are linked with foreign key to maintain data integrity. From the third table I want to select data and insert them to table 2. If the data inserted to table 2 have no respective record in table 1, error occur and no record is inserted. Is there any command how to suppress it and insert all correct data?

Example (not the real situation but for ilustration):

Tab 1 fields (id, name) records(1,ales) (2,marek) (3, lojza) (4, John)
Tab 2 fields(id, surname) records (1, hubl), (2,stelcik)
foreign key fn_tab2_id_tab2_id
Tab 3 fields (id, surname) records (3, Peter), (4,AAA), (5,BBB)

insert into Tab 2 (id, surname) select id, surname from tab 3.
Records (3, Peter), (4,AAA) would be inserted but (5,BBB) not (because of foreign key). Is there any command how to suppress this and insert only correct values? or i have to make for example left join first?

2 question: Want to grant privileges for all tables starting "IN" and have following code. However it doesnt works. The print part works properly and when put out the granting privileges everything is OK and skript run properly. However with the grant part error message appears:

Msg 102, Level 15, State 1, Line 18
Incorrect syntax near '@table_name'.
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near '@table_name'

DECLARE @table_name varchar(50)

DECLARE Cursor_in_table CURSOR -- Declare cursor

LOCAL SCROLL STATIC

FOR

Select name from SYS.tables where name like 'IN%'

OPEN Cursor_in_table -- open the cursor granting privileges for IN tables

FETCH NEXT FROM Cursor_in_table

INTO @table_name

PRINT 'Granting privileges to the following table: '+ @table_name -- print the name
GRANT SELECT, UPDATE, INSERT, DELETE ON @table_name TO [MASINA\LICusers]

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM Cursor_in_table

INTO @table_name

PRINT 'Granting privileges to the following table: '+ @table_name
GRANT SELECT, UPDATE, INSERT, DELETE ON @table_name TO [MASINA\LICusers]

END
close Cursor_in_table

3 question (the last one):
have table Application_Users where username and hash (encrypted password as varchar) are stored. Can you help me how to create function function change_password (username as string, password as string) returns bit --- return 0 if password WAS NOT successfully changed and 1 if user does not exist??

I can change password but I cannot treat the situation when password was not changed and if user event do not exists

Thanks a lot guys in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-02 : 20:10:37

Append a where clause to the select as


where  exists (select * from Tab1 where Tab1.id = Tab3.id )

Regarding granting permissions, you have to do one table at a time. Alternatively, if you are on SQL 2005 or higher you can grant permissions on the schema using


GRANT SELECT, UPDATE, INSERT, DELETE  on SCHEMA::dbo to [MASINA\LICusers]

Ufin
Starting Member

7 Posts

Posted - 2011-06-03 : 09:48:27

Thanks a lot it helped. One more question.

I have used pdwencrypt function to encryp password in user table.
However I want to create function which use pwcompare to compare password that user enter to application and password stored in user table.

However when i ues query select password from users where username="XXX" i dont know how to insert result to variable in order to use pwdcompare (result from query, password from the application)

Thanks a lot

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-03 : 10:02:41

In general, it would be something like this:


declare @password varchar(32);
select @password = password from users where username="XXX"

Then you can use @password to do the comparison etc.

Ufin
Starting Member

7 Posts

Posted - 2011-06-03 : 11:06:45

sunitabec: ok thanks. Can you help me why the following comparism returns 0? I have a simple password which I encrypt and then decrypt and it does not works. I want to do this in order to check whether user password is ok (when logging to application).

update dbo.ctrl_users
set dbo.ctrl_users.password = pwdencrypt('aabbcc22')
where dbo.ctrl_users.username = 'stelcik'

declare @password2 varchar(64)
select @password2 = password from dbo.ctrl_users where username='stelcik'
select @password2
select pwdcompare(@password2, pwdencrypt('aabbcc22'))

OUTPUT:

(1 row(s) affected)

----------------------------------------------------------------
Æ¤™Åî¥µùGØ?øqÚð½ ÅN9A�ß

(1 row(s) affected)

-----------
0

(1 row(s) affected)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-03 : 11:25:02

First parameter is the clear text password, so last line in your code should be:


select pwdcompare('aabbcc22',@password2)

Ufin
Starting Member

7 Posts

Posted - 2011-06-03 : 12:13:40

So it is unable to apply this function to compare password which came from application in varchar and result of query? What i want is receive password unencrypted and compare it with stored (encrypted) password.
Which another function can I use in order to compare stored passwords?

I modify and it still not working , always result 0

update dbo.ctrl_users
set dbo.ctrl_users.password = pwdencrypt('aabbcc22')
where dbo.ctrl_users.username = 'stelcik'

declare @password2 varchar(64)
select @password2 = password from dbo.ctrl_users where username='stelcik'

declare @password varchar(64)
select @password = pwdencrypt('aabbcc22')
select @password2
select @password
select pwdcompare(@password,@password2)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-03 : 12:21:29

You can indeed compare an unencrypted password against an encrypted password - that is what that function does. The unencrypted password should be the first parameter and the encrypted password should be ethe second parameter. Change your code as shown below:


update dbo.ctrl_users
set dbo.ctrl_users.password = pwdencrypt('aabbcc22')
where dbo.ctrl_users.username = 'stelcik'

declare @password2 varchar(64)
select @password2 = password from dbo.ctrl_users where username='stelcik'

declare @password varchar(64)
--select @password = pwdencrypt('aabbcc22')
select @password = 'aabbcc22'
select @password2
select @password
select pwdcompare(@password,@password2)

Ufin
Starting Member

7 Posts

Posted - 2011-06-03 : 14:38:06

thanks a lot, it works now.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-03 : 18:00:30

You are very welcome!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources