Tips for giving the user rights, and few small clarifications

[dwight1]

1. I am a dbo/Administrator.I have given user rights of datareader and datawriter for a database. Under database users i have given them rights to exec stored procedures. Do i have to specifically give rights under database user properties to Select,insert, update, delete and dri (do not know what it means).

2. I have a combo in which there is an amount field. I want to put commas for the amount field. I did it by highlighting the field and changing the field properties to standard and decimal 2. But the same does not reflect when i try at the user instance of adp. (Infact when i selecet the field properties, the field does not have options to format).

3. Server filter and form filter. I am having problems with this. When i select a name and want to open a specific borrower based on the combo selection some times it opens the right borrower and sometimes it opens a different borrower. When i check in the server filter, it stores some borrower id which is not what i selected in the combo. What is the best practice about using serverfilterby form and form filter.

4. Cont..to the above the same with report. I have a buttono that opens a report based on the current form. (opened using the combo pick). Some times the report picks the right guy and sometimes not.

I would really appreciate if some one can help me on this. This are some small property errors i am doing. Thanks for your time.

Dwight

 

[dwight1]

Any ideas please.

 

[danvlas]

1. When you create objects on the server, nobody but the owner can execute them, so you need to create roles and users, then assign users to roles and finally you grant the rights to roles.

My way of managing the permissions is:
a) NO permissions on tables
b) ONLY SELECT permissions on views
c) Execute permissions on stored procedures as appropriate.

This way all edits, inserts and deletes are done through sps, never through views so you can control the data completely at sp runtime, many times to transactions.

2. (Not sure, though) Data displayed in a combo is probably seen as text, regardless of the data type of the column. I'd be happy to be proven wrong.

3 & 4: Check that 'Allow Design Changes' property of the form is set to Design mode only, then make sure you delete any filter that may have remained in form/report definition, then save the object. It worked for me.

HTH


DRI definition from:

http://www.frick-cpa.com/ss7/Theory_DataIntegrity.asp

Declarative Referential Integrity (DRI) enforces data integrity automatically when inserting, updating or deleting data by denying any data modification that causes a violation


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[dwight1]

Danvlas,

Thanks for your response. All seems to be good and will try in the production tomorrow.

Well, you mentioned about no permissions on tables. How do i ensure that. I generally create a user on the server, create the same user for the database and then give him datareader and datawriter from the database property window. For SP i go and give permissions if they need.

Would appreciate if you can let me know the way to stop access to table. (hope they still can insert, edit and delete data)Because they are data entry operators.

Thanks again

Dwight

 

[danvlas]

When you create the tables, login as
MS Sql Enterprise Manager
an Administrator.

After you have created the tables, only Admins can work with the tables directly (select, insert, update, delete, change structures and so on).
Leave them that way.

To revoke the permissions, go table by table and remove all roles/users that have any rights on the tables.

Then, if you go only by stored procedures to edit/insert/delete, you're home free.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[dwight1]

Thank you.