Detailed User Access Rights

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, s
o
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!> 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@.discussions.microsoft.com> wrote in message
news:7C2D9969-BBEF-4469-982B-5AC3B5307656@.microsoft.com...
> 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!|||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:

> Although this works, it doesn't make it right. The Best Practice in SQL
> Server is to never use 'sa' for routine database access.
>
> 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 othe
r
> 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 permission
s
> on those tables will be needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
> news:7C2D9969-BBEF-4469-982B-5AC3B5307656@.microsoft.com...
>
>|||> 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@.discussions.microsoft.com> wrote in message
news:95E53960-B58A-4125-B298-430AAAC1191D@.microsoft.com...[vbcol=seagreen]
> 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:
>|||Right.. i've added those & all is well.
Just as a closing thought.. is there any way I can declare a 'grant' command
to ALL users, current & future?
Its more than likely that once a view / function has been made, it could be
used by other SQL users that will require access, and even though we control
the number of SQL users, it makes more sence to make a function 'global' /
'public' rather than having to recode to add a new user each time we decide
to add one.
Thanks!|||The Best Practice is to create database roles and grant object permissions
only to roles. You can then control access via role membership. This
greatly simplifies security administration.
Hope this helps.
Dan Guzman
SQL Server MVP
"-Ldwater" <Ldwater@.discussions.microsoft.com> wrote in message
news:6F88596D-0A1A-47FB-B56D-9E37EFF3CC94@.microsoft.com...
> Right.. i've added those & all is well.
> Just as a closing thought.. is there any way I can declare a 'grant'
> command
> to ALL users, current & future?
> Its more than likely that once a view / function has been made, it could
> be
> used by other SQL users that will require access, and even though we
> control
> the number of SQL users, it makes more sence to make a function 'global' /
> 'public' rather than having to recode to add a new user each time we
> decide
> to add one.
> Thanks!