Solved

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

Posted on 2001-08-21
5
205 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
  • 3
  • 2
5 Comments
 
LVL 3

Accepted Solution

by:
mathavra earned 40 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now