Re: Newbie on permissions: ADO.NET, C++.NET, SQL SERVER 2005 EXPRESS, Visual Studio 2005

raylopez99 (raylopez99@xxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Help understanding Stored proc Level Secuirty?
    ... Jasper Smith (SQL Server MVP) ... I set permissions to my Stored Procedures, ... Access to stored procs fail ...
    (microsoft.public.sqlserver.security)
  • Re: permissions required for executing CDOSys stored procedures
    ... properties of the sp_OACreate extended stored procedure has a permissions ... With SQL Server 2000 Enterprise ... Apparently only members of the sysadmin role can ... these stored procedures, however, the sql login for this application is ...
    (microsoft.public.sqlserver.security)
  • Re: Check SQL Server Table Permissions
    ... "BillyRogers" wrote in message ... program calls stored procedures in SQL Server from Access. ... permissions having been dropped for the group from one of the tables. ...
    (microsoft.public.access.modulesdaovba)
  • RE: Cannot break at stored procedures from VC.NET 2003
    ... How do you debug the stored procedures in Visual Studio .NET? ... Debug Stored Procedures in Visual Studio .NET ... And which database do you work on, is it SQL Server 2000 SP3? ...
    (microsoft.public.vsnet.debugging)
  • Re: Newbie: Restrict user account access to stored procedures
    ... You can deny users permissions to directly access tables and views by adding ... SQL Server does not, to the best of my knowledge, support any database role, ... stored procedures. ...
    (microsoft.public.sqlserver.security)