Security - Where do I start

[DebbieChapman]

I've created an Access 2000 database with linked tables to SQL V7.

I want to use SQL Security, and get the Users to input there password at the startup of the database, but havn't a clue where to start. Would appreciate any help or pointers into the right direction

 

[SQLSister]

For the Access side of this question I would go to the Access forum. BUt mostly you want to not specify the username and password inthe connection string which will make the users have to login, I suspect. OR you could consider having one login that everybody uses and then that can nbe hard-coded into the application as part of the connection string. If you do this, under no circumstances should you use th SA or any login with system adminsistration rights. Users don't need these rights and can do massive amounts of damage if they are given these rights.

AS far as setting up security, SQL Server database all require security. USers are generally gotten from the NT user list. Each server must have the users created for the server and then they must be added to each database. This is most easily done in Enterprise Manager although it can also be done in T-SQL in Query Analyzer. For the server users, Go to the the section entiled security under the server and you will see how to add users. At the time you set up a user this way you can specify what roles they and what databases they have access to. OR you can go to the specific database and set the users.

It is best to set up the permissions according to Roles. It takes much less time and is usually mor e accurate.

Set up a role in the database that you will assign the permissions to. Then add the users you want in that role to it. Then assign permissions. This way you only need to set the permissions once rather than for each individual. Consider what permissions you want to set carefully. Never give more permissions than a role actually needs. If they never need to insert, update or delete data, they should only have select permissions. If there is data you do not want a particular role to see then do not give them permission to select either.

Suggest you start all this by reading up on security in Books Online.