How to create users/permissions and database/tables in SQL Server?!


I want to use SQL Server as backend database and VB 6.0 as front end. But I am very new to MS SQL Server. I want that no normal user can  delete database/tables or database structure nor he can make big changes to database except editing and adding data. So I have some questions, please help me.

...will installing sql server on Win2k server computer which is on workgroup, serve our purpose(like security reasons & user permissions). to create users/tables/database/permissions/login accounts on sql server on workgroup. to connect to this database through LAN/workgroup i.e. how to setup connection to this database within from VB 6.0.

Thanx in advance.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
To connect with VB to SQL Server:

dim c as ADODB.Connection
set c = new ADODB.Connection ("Provider=SQLOLEDB;SERVER=YourServerName;DATABASE=YourDatabase;UID=SqlLoginName;PWD=SqlLoginPassword;")
--or if using Windows NT Account Security-- ("Provider=SQLOLEDB;SERVER=YourServerName;DATABASE=YourDatabase;Integrated Security=SSPI;")

-- now, you have the connection open (unless connection failure), you can execute any statements:
c.execute ("INSERT / UPDATE / DELTE ...")

-- or, to retrieve data:
dim r as ADODB.recordset
set r= new ADODB.recordset
set r.activeConnection = c ("SELECT .... ")

Now, If you install on Win2k server in a Workgroup (thus not in a NT Domain), you cannot use Windows Authentication...

If you protect the NT Administrator account on the NT Box, a normal user should not be able to break into the SQL Server...

From VB, you will have to connect to the "master" database if you want to create some databases, otherwise directly to the application database.

To create users/logins etc on SQL Server, please consult the Books Online about the following statements/procedures
sp_add_login   <<< create a login (server-wide)
sp_add_user   <<< create a user (based on login, for a given database)
CREATE TABLE <<< create a table
GRANT   <<< grant permissions on objects (tables, views, procedures etc)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.