?
Solved

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

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

752 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