Re: Best Practice for MSDE User permissions

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 08/27/05 

Date: Sat, 27 Aug 2005 09:53:44 -0500

> 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