March 12, 2003 at 1:02 pm
We are converting Access database to SQL Server. The first thing I would like to setup security in SQL Server as same as Access Database.
Access security is as follow.
Administrator.. can do anything.
Principal.. can only view the data
Teacher .. can view,modfy,delete and update
let's say user login into the system as a teacher then system will give him or her a teacher permission. there are thousands of users in the databse.
What is the best way in setting Security in SQL Database as same as Access database.
Is it possible to migration users from Active directory and stores in SQL Server database with password, if so then how.
appreciated if you could help me in this project.
thanks in advance
March 12, 2003 at 7:02 pm
In SQL Server, System admin (sa) or logins with 'sa' privilege are the database adminsitrator who are able to do anything.
You can create and maintain two database roles with proper permission to your tables, views and stored procedures etc. And add your principals and teachers into corespond database roles will grant them access the information they need.
Create linked server to Active Directory may help you to access domain user account as I read in somewhere but never done that.
March 13, 2003 at 11:49 am
Thanks for your reply.
I think first we need to create logins for users. let's say there are 1000 users in Access database. Do I need to create 1000 logins in SQL database first and then create Users and then assign permission in roles.
March 13, 2003 at 1:04 pm
Can you use select statement to list all your users in Access or able to export the users into a table or text file?
Once you have have users name list, It will be quite easy to add them into SQL Server with following commands using T-SQL script in batch.
sp_addlogin
sp_password
sp_defaultdb
sp_adduser
sp_addrolemember
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply