Solved

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

Posted on 2001-08-21
5
199 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
Comment Utility
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
Comment Utility
Thanks for reply.

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

Expert Comment

by:mathavra
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 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

13 Experts available now in Live!

Get 1:1 Help Now