| Author |
 Topic  |
|
|
Ufin
Starting Member
7 Posts |
 Posted - 06/02/2011 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 06/02/2011 : 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 - 06/03/2011 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 06/03/2011 : 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 - 06/03/2011 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 06/03/2011 : 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 - 06/03/2011 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 06/03/2011 : 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 - 06/03/2011 : 14:38:06
|
| thanks a lot, it works now. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 06/03/2011 : 18:00:30
|
| You are very welcome! |
|
|
| |
Topic |
|
INSERT problem and GRANT problem
