deny truncate?

What if I want to give users Query Analyzer-style access to my data, but
don't want them to delete it?
I can do
deny delete on dbo.mytable to public
which is great... assuming they don't know how to do 'truncate table'.
uh?
is there any way I can prevent users from truncating a table by permissions?Only members of the sysadmin server role and the db_owner and db_ddladmin
database roles and the table owner have permissions to Truncate the table.
Jacco Schalkwijk
SQL Server MVP
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:86930C7A-AC66-4E96-AB9C-487200CD223D@.microsoft.com...
> What if I want to give users Query Analyzer-style access to my data, but
> don't want them to delete it?
> I can do
> deny delete on dbo.mytable to public
> which is great... assuming they don't know how to do 'truncate table'.
> uh?
> is there any way I can prevent users from truncating a table by
> permissions?|||> is there any way I can prevent users from truncating a table by
permissions?
Yes, don't make them administrators or dbo.
http://www.aspfaq.com/
(Reverse address to reply.)|||From Books Online:
Permissions
TRUNCATE TABLE permissions default to the table owner, members of the
sysadmin fixed server role, and the db_owner and db_ddladmin fixed database
roles, and are not transferable.
The bottom line: Manage security properly and the users will not have
rights to do anything that you do not want them to.
Keith
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:86930C7A-AC66-4E96-AB9C-487200CD223D@.microsoft.com...
> What if I want to give users Query Analyzer-style access to my data, but
> don't want them to delete it?
> I can do
> deny delete on dbo.mytable to public
> which is great... assuming they don't know how to do 'truncate table'.
> uh?
> is there any way I can prevent users from truncating a table by
permissions?|||OK...
We have implemented a rule that any developer that needs to create tables in
a database has to be members of the db_owner role of the database as they
need to be able to create dbo. prefixed tables, as if they can't, then one
will create a table and it will be called, say
mydb.dave.davestable
and then when, say Julie, selects, she will effectively get the results from
mydb.julie.davestable
which either won't exist or else she won't get the same results as dave...
I don't need them to be db_owner for anything other than to be create and
run any existing dbo. prefixed SPs and also any future ones aswell without
the future ones being explicitly assigned to them.
e.g. I need to create a dbo. procedure, and I need to be able to create
another dbo.procedure, without having to spend time setting users permission
s
from it whenever I create a new procedure.
"Bonj" wrote:
[vbcol=seagreen]
> What if I want to give users Query Analyzer-style access to my data, but
> don't want them to delete it?
> I can do
> deny delete on dbo.mytable to public
> which is great... assuming they don't know how to do 'truncate table'.
> uh?
> is there any way I can prevent users from truncating a table by permissions?[/vbco
l]|||No problem as long as you have separate development and production
environments and the developers are not a member of the db_owner role on the
production environment?
Jacco Schalkwijk
SQL Server MVP
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:F3F68F4F-15C4-44DA-809C-B297C4F1AD84@.microsoft.com...[vbcol=seagreen]
> OK...
> We have implemented a rule that any developer that needs to create tables
> in
> a database has to be members of the db_owner role of the database as they
> need to be able to create dbo. prefixed tables, as if they can't, then one
> will create a table and it will be called, say
> mydb.dave.davestable
> and then when, say Julie, selects, she will effectively get the results
> from
> mydb.julie.davestable
> which either won't exist or else she won't get the same results as dave...
> I don't need them to be db_owner for anything other than to be create and
> run any existing dbo. prefixed SPs and also any future ones aswell without
> the future ones being explicitly assigned to them.
> e.g. I need to create a dbo. procedure, and I need to be able to create
> another dbo.procedure, without having to spend time setting users
> permissions
> from it whenever I create a new procedure.
>
> "Bonj" wrote:
>|||>>I don't need them to be db_owner for anything other than to be create and
run any existing dbo. prefixed SPs and also any future ones aswell without
the future ones being explicitly assigned to them.<<
They do not need to be dbo to execute a dbo owned stored procedure. Just
have dbo issue
GRANT EXECUTE ON dbo.YourProcName TO DeveloperNameGoesHere
You can easily create a stored procedure that issues the appropriate grant
statements to the appropriate users on all stored procedures or on a subset
of stored procedures based on creation date or name. You decide what you
need and create the supporting stored procedure to do it.
Giving users the ability to create tables...I would not give them dbo
rights for this. I would let them play in their own "sandbox" by creating
tables under their account. When their sand castle (or table) has been
approved then dbocan create the table within the development environment (as
dbo).
I have used the same techniques in the past with much success.
Keith
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:F3F68F4F-15C4-44DA-809C-B297C4F1AD84@.microsoft.com...
> OK...
> We have implemented a rule that any developer that needs to create tables
in
> a database has to be members of the db_owner role of the database as they
> need to be able to create dbo. prefixed tables, as if they can't, then one
> will create a table and it will be called, say
> mydb.dave.davestable
> and then when, say Julie, selects, she will effectively get the results
from
> mydb.julie.davestable
> which either won't exist or else she won't get the same results as dave...
> I don't need them to be db_owner for anything other than to be create and
> run any existing dbo. prefixed SPs and also any future ones aswell without
> the future ones being explicitly assigned to them.
> e.g. I need to create a dbo. procedure, and I need to be able to create
> another dbo.procedure, without having to spend time setting users
permissions[vbcol=seagreen]
> from it whenever I create a new procedure.
>
> "Bonj" wrote:
>
permissions?|||erm.... nah, that wouldn't really work.
You see, the thing is we *do* have separate development and production
environments... slightly (tries to keep a straight face). No, we do.
It's just that most things will need further development after they've been
put into production. In fact, I tend to rate a system as good if it needs
very little time spent developing it after it's been put into production,
although the contrary doesn't necessarily mean it's bad. However, I digress.
To put it diplomatically, when forming an expectation as to how long it will
be before the results of a 'requested improvement' will be seen, the
requester is likely to look on the optimistic end of the scale of how much
testing will be required. Consequently, code changes are made in production
environment - so the opportunity to use the results as real if the 'testing'
all passes, doesn't need to be missed. At the same time, if a production
server is busy with several jobs, other ad-hoc systems but that are
officially 'in production' will overflow onto one of the spare 'development'
servers.
Hence, it makes no sense to *force* a certain server to be either *always*
development or *always* production.
Besides, if a system is in production, the amount of use through Query
Analyzer it gets is less than one that's in development, so people are
unlikely to accidentally truncate the table anyway, but they are when in
development.
I think the most likely cause of the data getting deleted is the database
being recompiled. But then, that's a good thing, as it ensures one run
doesn't inherit the previous one's values. But I could go for miles down
that road - which is at the end of the day a lot like writing a load of
error handling code which might never be called.
but thanks for your help anyway
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23pmvt3TsEHA.1336@.tk2msftngp13.phx.gbl...
> No problem as long as you have separate development and production
> environments and the developers are not a member of the db_owner role on
the
> production environment?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> news:F3F68F4F-15C4-44DA-809C-B297C4F1AD84@.microsoft.com...
tables[vbcol=seagreen]
they[vbcol=seagreen]
one[vbcol=seagreen]
dave...[vbcol=seagreen]
and[vbcol=seagreen]
without[vbcol=seagreen]
but[vbcol=seagreen]
>|||> They do not need to be dbo to execute a dbo owned stored procedure. Just
> have dbo issue
> GRANT EXECUTE ON dbo.YourProcName TO DeveloperNameGoesHere
I know, the thing is though, typing out all those statements could be
regarded as work that isn't actually getting anybody anwhere that they
couldn't be just put there by flipping a switch once. There isn't a large
amount of developers, only 5 - in fact there's twice as many servers than
developers. I just thought if it was easy, then I could do it without too
much hassle.

> You can easily create a stored procedure that issues the appropriate grant
> statements to the appropriate users on all stored procedures or on a
subset
> of stored procedures based on creation date or name. You decide what you
> need and create the supporting stored procedure to do it.
That's an option - it does need repeatedly calling, i.e. every time the
database runs, though.

> Giving users the ability to create tables...I would not give them dbo
> rights for this. I would let them play in their own "sandbox" by creating
> tables under their account. When their sand castle (or table) has been
> approved then dbocan create the table within the development environment
(as
> dbo).
Don't get me wrong - I'm not the administrator. I'm a developer.
The 'sandbox' approach wouldn't really work though, because all too often
than not, we have to use the results of what another developer has done,
which is thus in his table. The 'users' of the system, as such, don't even
get to see SQL server. All they see are Excel sheets and graphs, and various
things such as crystal reports in pdf format, that are downloaded off the
servers.
Cheers|||> I know, the thing is though, typing out all those statements could be
> regarded as work that isn't actually getting anybody anwhere that they
> couldn't be just put there by flipping a switch once.
Do you use source control? Do you store your stored procedures within
script files within that source control system? If so just add the
appropriate command to the stored procedure script files. Each time the
stored procedure is compiled (created) from the script file the appropriate
grant statement(s) will be executed.

> That's an option - it does need repeatedly calling, i.e. every time the
> database runs, though.
No, it needs to be called every time a stored procedure is dropped and
created (or newly created).
The sandbox approach would only apply for a very short development cycle.
You develop a table and a stored procedure in your sandbox. You get it
working. You get your DBA to create the table and stored procedure(s) as
dbo and you are done.
Keith
"Bonj" <benjtaylor at hotpop d0t com> wrote in message
news:OqrBpjVsEHA.2128@.TK2MSFTNGP11.phx.gbl...
Just[vbcol=seagreen]
> I know, the thing is though, typing out all those statements could be
> regarded as work that isn't actually getting anybody anwhere that they
> couldn't be just put there by flipping a switch once. There isn't a large
> amount of developers, only 5 - in fact there's twice as many servers than
> developers. I just thought if it was easy, then I could do it without too
> much hassle.
>
grant[vbcol=seagreen]
> subset
you[vbcol=seagreen]
> That's an option - it does need repeatedly calling, i.e. every time the
> database runs, though.
>
creating[vbcol=seagreen]
> (as
> Don't get me wrong - I'm not the administrator. I'm a developer.
> The 'sandbox' approach wouldn't really work though, because all too often
> than not, we have to use the results of what another developer has done,
> which is thus in his table. The 'users' of the system, as such, don't even
> get to see SQL server. All they see are Excel sheets and graphs, and
various
> things such as crystal reports in pdf format, that are downloaded off the
> servers.
>
>
>
>
>
> Cheers
>