Re: Detailed User Access Rights

> In our system, we only really have one main user, the SA user. Thats the
> user that is used to do all the reads & writes for our application. Works
> fine.

Although this works, it doesn't make it right. The Best Practice in SQL
Server is to never use 'sa' for routine database access.

> Now our customers are interested in writing views & user defined functions
> etc, but we cant seem to find a way to allow users access to create / mod
> /
> delete views & user defined functions, and NOT create / mod / delete DB
> tables.

You can allow users to create objects in only their own schema by granting
the desired object create permissions. Note that only privileged users
(sysdamin, dbo, db_owner and db_ddladmin) can create objects owned by other
users or the 'dbo' schema. Consequently, you could grant CREATE TABLE as
well without compromising data in tables owned by other users.

GRANT
CREATE VIEW,
CREATE FUNCTION
TO CustomerUser

If those user objects reference your application tables, SELECT permissions
on those tables will be needed.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"-Ldwater" <Ldwater@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7C2D9969-BBEF-4469-982B-5AC3B5307656@xxxxxxxxxxxxxxxx
> Hi all, I have a question about user access in MS-SQL.
>
> In our system, we only really have one main user, the SA user. Thats the
> user that is used to do all the reads & writes for our application. Works
> fine.
>
> A problem arises when our customers want to have access to the DB. Now,
> we
> want to control this so they cant go hacking up thier data and blaming us,
> so
> we only allow read only access for report writings etc.
>
> Now our customers are interested in writing views & user defined functions
> etc, but we cant seem to find a way to allow users access to create / mod
> /
> delete views & user defined functions, and NOT create / mod / delete DB
> tables.
>
> Any ideas / suggestions?
>
> Thanks!


.

Quantcast