Posted by Erland Sommarskog on 03/29/07 22:03

raylopez99 (raylopez99@yahoo.com) writes:
> At the risk of looking even more stupid, if you know of how to "also
> add that user a role which has the privileges you want, for instance
> to the sysadmin role" within Visual Studio 2005 and/or Windows XP (for
> the program SQL Server 2005 Express Edition), please feel free to let
> me know. I just want to add the Power User to have Administrator
> access for the Visual Studio 2005, when working on ADO.NET (SQL Server
> 2005), not for all programs, if possible. For now I will simply
> program while logged in as an Administrator, which seems to be a good
> workaround to my problem.

Do I understand this correctly that you want your user to have heavy
perms when connected through VS and your application, but not when it's
connected through Management Studio? There is no way you can assign
permissions per application. Permissions are per logins and users.

Of course, it's a legit requirement that a user should only be able to
access objects in the database through the application, as the application
then can control what the user can see and update. There are a couple of
ways to implement this requirement. The method that has been most tested
and rried is to use stored procedures. There are several ways that
permissions can be granted through stored procedures, whereof the most
useful is ownership chaining. If all stored procedures and tables are
owned by dbo, the users only need execute permissions to the stored
procedures.

There are ways to handle this without stored procedures, but I am less
of fond of these methods.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]