Re: Permission in database
From: Dan Guzman (danguzman@nospam-earthlink.net)
Date: 08/02/02
- Next message: Sue Hoegemeier: "Re: Permission to Users under DB"
- Previous message: Andrew J. Kelly: "Re: Column & Data specific security"
- In reply to: Shailesh: "Permission in database"
- Next in thread: Christian Olsson: "Re: Permission in database"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Dan Guzman" <danguzman@nospam-earthlink.net> Date: Fri, 2 Aug 2002 09:04:44 -0500
You can create database roles and assign object permissions to these
roles to support the required functionality. You can then control
object access via user role membership. A user can be added to multiple
roles, if necessary, and permissions are cumulative.
In the case where a user needs only SELECT on all tables, you can use
the pre-defined db_datareader fixed database role.
I'm not sure I understand the details of your requirements but the
script below should get you started. You can accomplish the same result
using Enterprise Manager but it requires a lot of clicking if you need
to setup a lot of users.
Use MyDatabase
GO
--add roles
EXEC sp_addrole 'Administrators'
EXEC sp_addrole 'AccountDep'
EXEC sp_addrole 'SalesPersons'
GO
--grant object permissions to roles
GRANT SELECT, INSERT, UPDATE, DELETE ON Customer TO Administrators
GRANT SELECT, INSERT, UPDATE, DELETE ON Purchases TO Administrators
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO Administrators
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO AccountDep
GRANT SELECT ON Purchases TO AccountDep
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO SalesPersons
GO
--add users to database
EXEC sp_adduser 'User1'
EXEC sp_adduser 'User2'
EXEC sp_adduser 'User3'
EXEC sp_adduser 'User4'
GO
--add users to roles
EXEC sp_addrolemember 'Administrators', 'User1'
EXEC sp_addrolemember 'AccountDep', 'User2'
EXEC sp_addrolemember 'SalesPersons', 'User3'
EXEC sp_addrolemember 'db_datareader', 'User4'
GO
-- Hope this helps. Dan Guzman SQL Server MVP ----------------------- SQL FAQ links (courtesy Neil Pike): http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800 http://www.sqlserverfaq.com http://www.mssqlserver.com/faq ----------------------- "Shailesh" <shailesh@geminicorp.be> wrote in message news:02af01c239fa$10d9c670$3bef2ecf@TKMSFTNGXA10... > Dear Friends > > My company is having database in access and due to that > reason the performance is very slow.Hence we have decided > to go for the SQL server. > > With the upsize wizard in MS-Access I have made all the > tables and funtions which are working very fine but the > only problem i have is to give permission. > > I have 25 users working on the same database which > includes the table of > > 1) Sales > 2) Purchases > 3) Customer > > And following are my users ( Groups ) > > 1) Administrator -: One who can do anything in the > database. > > 2) Only Read permission users on all Tables > > 3) AccountDep : Who update the database of Sales and > Purchase but not customer they have only right of Read on > the purchases. > > 4) Sales Person: Who Update the Table of Sales .. > > Kindly suggest how i can give the permission to all so > that there will not be any security laps. > > Your reply will help me to perform best. > > Thanks in Advance
- Next message: Sue Hoegemeier: "Re: Permission to Users under DB"
- Previous message: Andrew J. Kelly: "Re: Column & Data specific security"
- In reply to: Shailesh: "Permission in database"
- Next in thread: Christian Olsson: "Re: Permission in database"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
