Re: Detailed User Access Rights

> 1) You say that using the SA admin isn't the best way of access the DB
> through our application. Is there a specific reason why this is bad?

For routine application database access, it's best to use an account with no
more permissions than actually needed. Using 'sa' or a sysadmin role member
account is overkill unless your app requires that level of security.
Sysadmin role members have full SQL Server permissions and OS permissions
are limited only by the SQL Server service account.

> 2) Now that my customers can create their own views etc, I need them to be
> able to access user defined functions created by the application. How do
> I
> allow exe permissions on these functions to allow all users to execute
> them?

You can GRANT EXECUTE or SELECT, depending on whether the function is scalar
or table-valued.

GRANT SELECT ON MyTableFunction TO CustomerUser
GRANT EXECUTE ON MyScalarFunction TO CustomerUser

--
Hope this helps.

Dan Guzman
SQL Server MVP

"-Ldwater" <Ldwater@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:95E53960-B58A-4125-B298-430AAAC1191D@xxxxxxxxxxxxxxxx
> Excellent! Thats just what I needed! Tried it out, and it works just
> fine :D
>
> But while were on the subject, could I ask a few more questions?
>
> 1) You say that using the SA admin isn't the best way of access the DB
> through our application. Is there a specific reason why this is bad?
>
> 2) Now that my customers can create their own views etc, I need them to be
> able to access user defined functions created by the application. How do
> I
> allow exe permissions on these functions to allow all users to execute
> them?
>
> Thanks again!
>
> "Dan Guzman" wrote:
>
>> > 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!
>>
>>
>>


.

Relevant Pages

  • Permissions to public in the master database
    ... The company I work for provides an application that runs on SQL Server 2000. ... One of our customers is requiring that we revoke all access granted to the ... messing with the master database at all. ... post on one of the communities, "Should you alter permissions in master, your ...
    (microsoft.public.sqlserver.security)
  • Re: Permission to Insert records for DB USERS
    ... You could use the GRANT command to grant INSERT permissions to users. ... See SQL Server Books Online for more information. ... > for the permissions on the database. ...
    (microsoft.public.sqlserver.security)
  • Re: Creat trusted user
    ... By 'trusted user' I assume you mean an existing Windows account. ... You can then grant the account access to a particular database using ... The user will also need object permissions. ... SQL Server MVP ...
    (microsoft.public.sqlserver.security)
  • Using Network Service Account to remote SQL Server with WSS
    ... seems like a good idea and kb below has some detail on how to grant ... permissions in SQL Server by using Domain\Computername$ ... Saves manaully creating service account and having a password that should be ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: Connection Woes :-(
    ... Then you didn't grant the appropriate permissions to the aspnet user on sql server. ...
    (microsoft.public.dotnet.framework.aspnet)