Hi All
I am new to MSDE/SQL Server and need some guidance on best practices for
user permissions.
I have a VB6 program running in a bakery factory
The computer network is a peer to peer 3 computer network running WIndowsXP
MSDE runs on computer A and the data entry person runs my program on this
machine to enter daily orders for their customers
A manager needs to access the MSDE data from another computer for reporting
tasks and is not allowed to enter or modify data
I am sure I can't use Windows authentication as it is only a peer network.
Is this correct?
Should I create a new Login and set individual permissions on each table or
is it OK to use the sa account etc?
Any ideas appreciated
Regards
Steve> I am sure I can't use Windows authentication as it is only a peer network.
> Is this correct?
Windows authentication is problematic when you have multiple computers
without a domain. It is possible by mapping a drive on the client to the
SQL Server using a local server account but this is a kluge.
> Should I create a new Login and set individual permissions on each table
> or
> is it OK to use the sa account etc?
I suggest you use SQL authentication and assign permissions to roles. You
can prompt for the user's SQL login and password during application at
startup. Never use the 'sa' login for routine application access.
USE MyDatabase
--setup role-based security
EXEC sp_addrole 'Manager'
EXEC sp_addrole 'Clerk'
GRANT SELECT ON MyTable TO Manager
GRANT SELECT, INSERT, UPDATE, DELETE ON MyOtherTable TO Manager
GRANT SELECT ON MyOtherTable TO Clerk
--create login for managers
EXEC sp_addlogin 'SomeManager', 'SomeManagerPassword', 'MyDatabase'
EXEC sp_grantdbaccess 'SomeManager'
EXEC sp_addrolemember 'Manager', 'SomeManager'
--create login for clerks
EXEC sp_addlogin 'SomeClerk', 'SomeClerkPassword', 'MyDatabase'
EXEC sp_grantdbaccess 'SomeClerk'
EXEC sp_addrolemember 'Clerk', 'SomeClerk'
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:6DE28FBB-20B4-4637-BC82-7BDDD9FDA62B@.microsoft.com...
> Hi All
> I am new to MSDE/SQL Server and need some guidance on best practices for
> user permissions.
> I have a VB6 program running in a bakery factory
> The computer network is a peer to peer 3 computer network running
> WIndowsXP
> MSDE runs on computer A and the data entry person runs my program on this
> machine to enter daily orders for their customers
> A manager needs to access the MSDE data from another computer for
> reporting
> tasks and is not allowed to enter or modify data
> I am sure I can't use Windows authentication as it is only a peer network.
> Is this correct?
> Should I create a new Login and set individual permissions on each table
> or
> is it OK to use the sa account etc?
> Any ideas appreciated
> --
> Regards
> Steve
No comments:
Post a Comment