How to add different user with different permission for the Database

Question

Post reply

How to add different user with different permission for the Database

vinayak.v

SSCrazy

Points: 2414
More actions
June 27, 2011 at 12:30 am

#238493

hi..
i want to create a different user with different permissions..
i have one database..named School Database..
now what i want is i want the principle with different
login name and password and the staff with different login name
and password..
and the main thing the principle should have the rights to
create delete and edit permission and the staff should have
only the read permission ...
I'm using Sql server 2008
help me....

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

CREATE LOGIN [Domain\Username] FROM WINDOWS WITH DEFAULT_DATABASE=[SomeDB], DEFAULT_LANGUAGE=[us_english]

GO

USE [SomeDB]

GO

CREATE USER [SomeUSer] FOR LOGIN [Domain\Username] WITH DEFAULT_SCHEMA=[dbo]

GO

GRANT SELECT ON [OBJECT] TO SOMEUSER

GRANT UPDATE ON [OBJECT] TO SOMEUSER

Perform the above steps then repeat the same for the next principal and user with only grant select permissions.

Jayanth Kurup[/url]

vinayak.v SSCrazy Points: 2414 More actions · Answer 2

thanks for the reply ..

in domain what we should write..

and for edit permission what we should write..

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 3

Grant update = edit permission , if you need to add new rows use grant insert

domain is the network name , say for example your company is amazon and your email id is a.b@amazon.com

then your windows nt login id would probably be something like

Amazon\a.b

where Amazon = Domain

Jayanth Kurup[/url]

Satheesh E.P. SSCrazy Points: 2509 More actions · Answer 4

Follow the below steps, if you need to create SQL Logins instead of windows

-- Creating Login For Principal

Create Login [PrincipalLoginName] With Password = 'GiveYourPasswordHere', default_database = [GiveYourDBNameHere], Check_policy = OFF;

-- Creating Login For Staff

Create Login [StaffLoginName] With Password = 'GiveYourPasswordHere', default_database = [GiveYourDBNameHere], Check_policy = OFF;

-- Creating User for Principle

Create User [PrincipalUserName] For Login [PrincipalLoginName] with Default_Schema = [dbo]

-- Creating User for Staff

Create User [StaffUserName] For Login [StaffLoginName] with Default_Schema = [dbo]

-- Granting the edit access for principal login

use [GiveyourDBNameHere]

Exec sp_addrolemember db_datawriter, PrincipalUserName

-- Granting the read access for staff login

use [GiveyourDBNameHere]

Exec sp_addrolemember db_datareader, StaffUserName

Hope the above script will help you to create logins in SQL but you should always consider creating windows login for security purpose.

If at all you are creating the SQL login then you should consider check_policy ON which will enforce more security. Refer the link .

---------------------------------------------------
Thanks,
Satheesh.

vinayak.v SSCrazy Points: 2414 More actions · Answer 5

Hi thanks for the reply

it is working good for only StaffLoginName and operations are working good

but it is not working for PrincipalLoginName when login and expand the

database and select one table to edit or select top 1000 rows ..

error is occurred displaying u dont have permission to select top 1000 rows..

what to do

?

Satheesh E.P. SSCrazy Points: 2509 More actions · Answer 6

We didn't add the db_datareader role which will permit the user to perform select operation on the user table. Execute the below statment to provide the same.

Exec sp_addrolemember db_datareader, PrincipalUserName

---------------------------------------------------
Thanks,
Satheesh.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 7

I recommend creating two database roles viz. Principal and Staff.

Instead of directly granting the permissions to Users.

vinayak.v SSCrazy Points: 2414 More actions · Answer 8

vinayak.v

SSCrazy

Points: 2414

June 27, 2011 at 6:24 am

#1344455

Thank u .. i got it...