sql server - in sqlserver can a user access only to a stored procedure or a function that checks login to a system? -

in desktop application, not store connection information in file. want authenticate user using guest user in sqlserver. guest user can check login via stored procedure or function in sqlserver. function takes 2 parameters user id , password , returns true or false. after login system, gets connection information , has user rights.

also guest user should not have right read login table. because if reads table, can admin or others' password.

how can achieve system ? thanks.

somewhere you'll have store connection key , application must db somehow.

you have decide if want use db-rights (grants , permits) or if there master password application , own rights management handled application. db rights can lead hardly solveable problems , leads like: sh$$, cannot solve tiny problem, let's grant rights make work...

if application not deal extremely sensible data should prefer masterpassword approach application driven rights system.

in 1 of our projects have xml-config file passwords in encrypted form. user's input compared against configured passwords - , there more hurdles don't want speak naturally.

your idea special login-user restricted access read passwords table sounds good. idea internal function check password sounds too.

better return true or false session token, leading key rights management, persistance of session data (current rows, window positions, whatever...). such token used in (web)services. have timeout, can bind auditing it...

good luck in finding best , fitting solution! rights management tricky , worth think before start...