Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to use SQL query statement to add a user into the DB?

Posted on 2001-08-21
5
Medium Priority
?
221 Views
Last Modified: 2012-06-21
I am sorry for my poor English.

I use SQL SERVER 7 and MDAC 2.6.

I have a DB named Manage, and there are three table, userinfo, resource and product, in it.

How to use SQL query statement to add a user into the DB, and grant the user in the userinfo, resource and product table?

For example, I want to add a user Jerry to the DB, and grant Jerry to the tree table with selecting, updating and deleting.

Thanks
0
Comment
Question by:Jerryleo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 3

Accepted Solution

by:
mathavra earned 160 total points
ID: 6410738
1)
First, you have to check whether the login exists in the server. If not, use the following to add.

exec sp_addlogin jerry, password, defaultdatabase

Ex: sp_addlogin jerry, password, manage

2) Then logon to the SQLServer and execute the following commands to add him as a user in the database.

use manage
go
exec sp_adduser jerry, jerry
go

3) Grant access to the specific tables

grant all on userinfo to jerry
grant all on resource to jerry
grant all on product to jerry
go

NOTE: You can also define groups and grant access to the group level so that you do not have to grant access to each user.
0
 

Author Comment

by:Jerryleo
ID: 6410772
Thanks for reply.

And How to define groups and grant access to the group level?
0
 
LVL 3

Expert Comment

by:mathavra
ID: 6410975
1) Add a group:

exec sp_addgroup Usergroup1
go

2) The following command adds a user to particular group.

exec sp_adduser jerry, jerry, Usergroup1
go

3) Grant at the group level

grant select on table to Usergroup1

The coolthing about it is, you can add as many number users under the group and they all will have the same access level as you have granted to the group.

0
 

Author Comment

by:Jerryleo
ID: 6411897
Thank you very much!

Now I see.

By the way, how to drop a group or an user from DB or table.

Thanks again!
0
 
LVL 3

Expert Comment

by:mathavra
ID: 6411911
Drop a group:

exec sp_dropgroup  groupname


Drop a user:

exec sp_dropuser   username

If they own any objects in the database, you will not be able to drop the user.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question