I have a front-end app in a small, closed domain where the users create
tables owned by dbo through a stored procedure that calls sp_executesql
aginst a second stored procedure. I had hoped that I could put a Grant
line in this second stored procedure, but it refuses to allow it with
"Grantor does not have Grant permission" error. Of course I understand
that this is by design and for security reasons, but is there some way
to circumvent this when you want to?

My process allows the program to create tables and then put a certain
number of default rows in the table. But since the users don't have the
ability to execute a grant command, it fails to insert the rows with a
permission denied error on the insert statement.

Thanks, Bob

Why are your users creating their own tables? You could let them store all
the data in one table and use views to allow users only to see the rows that
belong to them.

E.g.:
In the table definition include a column to store the user name.

In the view inside the where clause add a condition:
(<table>.<username_column> = system_user)

Now the only thing you need is to reference the view where you used to
reference the table.


ML

---
http://milambda.blogspot.com/

Can't make a single table to hold the data because each table
represents one building in an construction estimating program. I can't
know ahead of time how many floors each bulding will have, and how many
rooms there are on each floor, therefore I can't know how many columns
to put in the table. Giving them limited abiity to create tables
through my front end app is relatively safe, this is a small company
and there is no access to this sql server from outside the domain. I
realize I could have a table for buildings, a table for floor objects
and a table for room objects, but this would involve a huge amount of
dynamic coding, especially to display all this in the grid control, and
handle updates from the grid  control. Having the grid bind to a
single, discreet table allows using .net automation for filling the
grid, formatting the grid, and no-touch automation of update and delete
statements from the grid back to the data source. (Just three or four
lines of code to create and use a CommandBuilder object.)

The only remaining hiccup in my app is that though the users can create
tables, they can't automatically have insert, update and delete
priveledges unless the table is owned by the Windows user group instead
of dbo. Having this glitch means that when I deploy this app to our
sister company, I have to modify all the stored procedures and the code
in my app to hard code in the group name wherever these tables are
being called. It all works very elegantly except for this.

Bob

Seems to me you don't actually need a relational database...

I'm sorry, but I would actually use a fully relational model - you did
mention three entities and the relationships between them. This allows for
unified procedures, since the number of columns is fixed for any user
(implementation). All objects exist before-hand and appropriate permissions
can be set.

I personally think visualization can benefit greatly from well thought-out
business entities. Why use a grid to represent a building? Buildings are not
flat. Let your competition use grids, you can use something better. :) Ok,
I'm drifting here.

Back to permissions: is there a special reason your users aren't dbo's? They
create objects in order to use them - that's what the dbo role is for. In a
fixed model, however, they wouldn't have to be object owners - having
read/write permissions would suffice.

I suggest you rethink your design - everybody has to, eventually, and each
time using a proper relational model makes the job a lot easier.


ML

---
http://milambda.blogspot.com/

trying to do some weird math in an update statement
XOR and foreign key constraints
Like operator returns unexpected records
drop users connected to a database
default clustered index when selecting primary key