Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2001-08-21
5
Medium Priority
?
226 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 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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

564 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