SQL Server Performance Forum – Threads Archive

sqlwish@microsoft.com :: db_spExecutor

Have u ever wished there was a built-in role called "db_spExecutor" (it’s just an idea)
that would allow its members to execute all stored procedures in the database? I find that usually the db_datawriter members tend to have those kind of privileges : read/write data and additionally execute stored procedures …. By the way, it looks like we got back to the "subscription to topic" feature …

Often, the owner of a source object (the view, stored procedure, or user-defined function) also owns all target objects (the objects it depends on). When one user owns the source object and all target objects, the ownership chain is said to be unbroken. If different users own the target objects, the ownership chain is broken. SQL Server relies on the state of the ownership chain to determine when to check permissions. So I believe this point may have considered in SQL 2005. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

When I said "I find that usually the db_datawriter members tend to have those kind of privileges : read/write data and additionally execute stored procedures …." I meant that in a production environment, seen from a logical or conceptual point of view, -not a technical point-, the users with write access tend to have execute privileges with most of the written stored procedures … I have read some documentation about Yukon Beta 2 and there are some new built-in roles, but I havent been able to find any related to this particular "wish" … I’ll keep trying …

Hm, one feature I really like about MS Access is that you can set permissions for new items of a given object type. So when you create a new table then the permissions are automatically assigned to a user or group, but you can still overrule afterwards.

Actually db_owner, db_writer, db_reader and all that stuff are not dependent on the objects associated, this is why they are so useful … When u create new objects, the members still can access to it without any extra work from DBA’s side. My "wish" is to create such a non-object-dependent group which would allow to execute all stored procedures. Yes, Access allows you to set a DEFAULT permission for any kind of new different objects, but I dont really think this approach is better than the generic "roles" set in SQL Server. Well, actually the security approach is totally different! Anyway, I am a former MsAccess lover and so far, I know nothing about security in 2002, has it been improved? Does it already have any sort of groups? are u using ADO to access the SQL server databases?

As far as I know, Access security hasn’t changed structurally since v 2.0 (wouldn’t be surprised if actually since v 1.0). It’s based on DAO, and I don’t know why you say there are no groups!? We always assign permissions to groups, and make the user logins member of the appropriate grups to inherit the permissions. We use a dbo-like login, instead of the standard ‘admin’ user: admin does not have any permissions in our MDBs, and the same for the ‘users’ group. This way people must use our MDW file to open our system. For systems with a SQL back-end, we use a generic Access-login and then force the user to logon to SQL Server at startup, with the appropriate type of authentication. We always use database roles for permissions, and the SQL logon is used for any ADO or DAO or ODBC call. We’re using ODBC linked tables for data entry, and ADO for calling stored procedures. We also use Access (=DAO) pass-through queries to call procedures as datasources for reports, because our core system is in MDB or MDE format. We do have some additional tools that are in ADP or ADE format, but I’m not familiar with that part. You need some pretty impressive procedures and functions to make a workable ADO interface in an MDB/MDE, and I’ve found myself using DAO ptq’s as an ad-hoc interface, since they are so much more easily adjustable than what the original developers on our system have copied from the usual text books. – Well, you could say I’m old-fashioned …

silly ME!
Adriaan yes, you are right there were (are) GROUPS!
my baaaad! Actually your scenario looks pretty close to what I have here. So far, we discarded "attached tables for data entry" as we got rid of any Recordset/Dynaset. Instead, we use "independent forms" and OdbcDirect + Execute to INSERT, UPDATE. It’s tedious from the programming side, but it works better for a very concurrent site. For large sites I recommend you to get rid of JET-based security and move forward to SQL-based security on the server: it is much easier to mantain (u dont have to have that shared MDW or MDA file) but you will have to adapt those groups to functions and roles… Again, my apologies to MS Access groups …. This topic (ODBC, Access, Attached tables, DAO, ADO), however, should be discussed in another Forum …

SQL security easier to maintain? It’s not easy to maintain either in Access or on SQL because the tools will let you do only one thing at a time, and you need to close the window/wizard and go through the menus again to do something else that is closely related. Another thing is that they don’t let you copy all role/group memberships and specific permissions from one login to another.

Oh yes, the #1 reason why we still have linked tables is that MDB/MDEs are brilliant for end-users to build their own queries and reports. As far as I know Access has one of the best user interfaces for that. Also we do need to do a lot of Q&A with the user before committing changes, and it’s much easier to use the BeforeUpdate and AfterUpdate events of controls and forms to catch every possible combination of factors in the greater area of business rules. So bound forms are actually the right solution for us. Also, our users are not pressed for time: no problem if they have to wait for a couple of seconds. There are colleagues working on an ASP-based interface for the data entry, but of course there’s no query builder there ……

]]>