alter view permissions

Hello,
pls help, how can I remove alter view permission for
user who is member of ddladmin db role, select and update
queries shoud remain permited. The user should be able to
edit all other views, stored procedures etc, except this
one view.
thank you for helpHi
Add him to db_datawriter database role but remove him from ddladmin db role
"Gabriel" <anonymous@.discussions.microsoft.com> wrote in message
news:125c01c52f94$9b7e2d10$a501280a@.phx.gbl...
> Hello,
> pls help, how can I remove alter view permission for
> user who is member of ddladmin db role, select and update
> queries shoud remain permited. The user should be able to
> edit all other views, stored procedures etc, except this
> one view.
> thank you for help|||Thanks, I have not mentioned about ability to also create
new views, stored procedures etc. This actions are not
permited with db_datawriter role. Any ideas?

>--Original Message--
>Hi
>Add him to db_datawriter database role but remove him
from ddladmin db role
>
>"Gabriel" <anonymous@.discussions.microsoft.com> wrote in
message
>news:125c01c52f94$9b7e2d10$a501280a@.phx.gbl...
update[vbcol=seagreen]
to[vbcol=seagreen]
this[vbcol=seagreen]
>
>.
>|||One method is to grant CREATE permissions to the user. This will allow the
user to create/alter/drop objects that they own but not objects owned by
other users. You can then change ownership of the view in question to a
different user so that it can't be modified. Other users will need to
owner-qualify object names.
Another approach, which IMHO is better, is to employ a separate database for
those objects you don't want to user to modify. The user can then
db_ddladmin role member in your current database but not in the database
containing the sensitive objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gabriel" <anonymous@.discussions.microsoft.com> wrote in message
news:0bbe01c52fa6$ff2468e0$a601280a@.phx.gbl...[vbcol=seagreen]
> Thanks, I have not mentioned about ability to also create
> new views, stored procedures etc. This actions are not
> permited with db_datawriter role. Any ideas?
>
> from ddladmin db role
> message
> update
> to
> this

Alter View permission?

MSDN says the following at
http://msdn.microsoft.com/library/d...rl=/library/en-
us/tsqlref/ts_aa-az_2gtz.asp:
"ALTER VIEW permissions default to members of the db_owner and
db_ddladmin fixed database roles, and to the view owner. These
permissions are not transferable.
To alter a view, the user must have ALTER VIEW permission along with
SELECT permission on the tables, views, and table-valued functions being
referenced in the view, ..."
The BOL documentation for GRANT doesn't show ALTER VIEW as a permissible
statement type to grant permissions on. Trying to GRANT ALTER VIEW
doesn't work. How can I give a user ALTER VIEW permission? Does "not
transferable" mean I can't?
Thanks.
David WalkerYou can add the user to the db_owner or db_ddladmin fixed database roles.
AMB
"DWalker" wrote:

> MSDN says the following at
> http://msdn.microsoft.com/library/d...rl=/library/en-
> us/tsqlref/ts_aa-az_2gtz.asp:
> "ALTER VIEW permissions default to members of the db_owner and
> db_ddladmin fixed database roles, and to the view owner. These
> permissions are not transferable.
> To alter a view, the user must have ALTER VIEW permission along with
> SELECT permission on the tables, views, and table-valued functions being
> referenced in the view, ..."
> The BOL documentation for GRANT doesn't show ALTER VIEW as a permissible
> statement type to grant permissions on. Trying to GRANT ALTER VIEW
> doesn't work. How can I give a user ALTER VIEW permission? Does "not
> transferable" mean I can't?
> Thanks.
> David Walker
>|||I ran across the DDLAdmin role, but BOL makes it sound like you can
grant ALTER VIEW permissions. Maybe I'm misreading it -- I suppose
those permissions are only given to members of db_owner and db_ddladmin,
and they can't be granted to anyone else.
The phrase "ALTER VIEW permissions default to members of the db_owner
and db_ddladmin fixed database roles" should have "default to" replaced
by "are restricted to" if that's the case.
It would be nice to be able to grant ALTER VIEW permission on one view
to one user or role.
Thanks, AlejandroMesa.
David Walker
"examnotes"
<AlejandroMesa@.discussions.microsoft.com> wrote in
news:668F79B3-3335-4BC3-860F-67F467C12CEF@.microsoft.com:

> You can add the user to the db_owner or db_ddladmin fixed database
> roles.
>
> AMB
> "DWalker" wrote:
>
>|||You cannot give a user (that is not member of db_owner and db_ddladmin
fixed dabase roles) the permission to alter a specific view (that he
does not own). This is exactly what "not transferable" means (at least,
this is what it means to me).
As I see it, you have the following alternatives:
a) make the user a member of db_ddladmin role: this will enable him to
make any modification to the objects in the database, including
creating and deleting other objects (tables, views, procedures, etc)
b) grant the user the "CREATE VIEW" permission; the views that are
created by him will be will be owned by him, and so he will be able to
modify them
c) create the views in his name, by prefixing them with his user name
instead of "dbo" (without granting him the "CREATE VIEW" permission).
He will be able to modify those views (and to delete them), but he
won't be able to create new views (or other objects).
The problem with the b) and c) alternatives is that only that user will
be able to access those views by specifying only the view's name (the
other users must prefix the view's name with the user name).
Razvan|||It would be NICE if I could grant a user permission to alter a specific
view, but we'll take what we can get...
I'll probably add the user to the ddladmin role, although that's more
power than I would like to give. But the other choices aren't great
either. Thanks.
David
"Razvan Socol" <rsocol@.gmail.com> wrote in
news:1112639463.986674.297650@.f14g2000cwb.googlegroups.com:

> You cannot give a user (that is not member of db_owner and db_ddladmin
> fixed dabase roles) the permission to alter a specific view (that he
> does not own). This is exactly what "not transferable" means (at
> least, this is what it means to me).
> As I see it, you have the following alternatives:
> a) make the user a member of db_ddladmin role: this will enable him to
> make any modification to the objects in the database, including
> creating and deleting other objects (tables, views, procedures, etc)
> b) grant the user the "CREATE VIEW" permission; the views that are
> created by him will be will be owned by him, and so he will be able to
> modify them
> c) create the views in his name, by prefixing them with his user name
> instead of "dbo" (without granting him the "CREATE VIEW" permission).
> He will be able to modify those views (and to delete them), but he
> won't be able to create new views (or other objects).
> The problem with the b) and c) alternatives is that only that user
> will be able to access those views by specifying only the view's name
> (the other users must prefix the view's name with the user name).
> Razvan
>

Alter table permission to dbo

I have the following requirement

I am creating a login and database user 'test' on a database with dbo
role .
I want to remove create table , alter table permisions to this user.
I am able to revoke create table permission but alter table goes
through.
I gave a command deny insert,delete,update on ssycolumns to test.
Still I am not able to prevent user altering schema . Alter table
successfully goes throgh.

I do not want to use datreader and datwriter role.
since I want user 'test' to create storred procedure with dbo owner

Is there a way to achieve this ?

Thanks

M A Srinivas"M A Srinivas" <masri@.vsnl.com> wrote in message
news:f7e90f78.0309260634.3791a935@.posting.google.c om...
> I have the following requirement
> I am creating a login and database user 'test' on a database with dbo
> role .
> I want to remove create table , alter table permisions to this user.
> I am able to revoke create table permission but alter table goes
> through.
> I gave a command deny insert,delete,update on ssycolumns to test.
> Still I am not able to prevent user altering schema . Alter table
> successfully goes throgh.
> I do not want to use datreader and datwriter role.
> since I want user 'test' to create storred procedure with dbo owner
> Is there a way to achieve this ?
> Thanks
> M A Srinivas

You can't prevent the user from modifying/dropping an existing object. If
you need to create objects with dbo owner, then the user must be in the
db_owner role, and that means he can modify/drop any dbo object. If you can
explain why you need the test user to create stored procedures, then perhaps
someone can suggest an alternative approach. Are you creating the procedures
dynamically, are you deploying new code to several server, etc.

Simon

alter table inside a stored procedure

Hi,
Our application needs to issue an alter table statement. Since the user
using
the application does not have dbo permission, we are planning to use
a stored procedure with dynamic sql.
SET @.RUNSQL = "alter table dbo.gggg .."
EXEC(@.RUNSQL)
The stored procedure is owned by dbo. However it is not allowing
the alter table because of lack of permission. Does that mean
that any EXEC inside a stored procedure does not run as user
dbo.
Is there a workaround for it?
thanks.Hi
Well , if you use dynamic sql within a stored procedure, user must have
permissions (SELECT,UPDATE...) on underlyaing tables.
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||This is a security feature.
> Is there a workaround for it?
In 2005, you can specify EXECUTE AS for the procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dcruncher4@.aim.com> wrote in message news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||To add to the other responses, an unbroken ownership chain (e.g. 'dbo' owns
all objects involved) does not change the execution context. With an
unbroken chain, *object* permissions are simply not checked on indirectly
referenced objects and note that dynamic SQL always breaks the ownership
chain. *Statement* permissions (e.g. ALTER TABLE) are always checked in the
execution security context. The execution context can't be changed on
versions prior to SQL 2005.
The need to execute DDL by non-privileged users and use dynamic SQL can
indicate an application design issue. Perhaps someone can suggest an
alternative if you provide the requirements driving this approach.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>

alter table inside a stored procedure

Hi,
Our application needs to issue an alter table statement. Since the user
using
the application does not have dbo permission, we are planning to use
a stored procedure with dynamic sql.
SET @.RUNSQL = "alter table dbo.gggg .."
EXEC(@.RUNSQL)
The stored procedure is owned by dbo. However it is not allowing
the alter table because of lack of permission. Does that mean
that any EXEC inside a stored procedure does not run as user
dbo.
Is there a workaround for it?
thanks.Hi
Well , if you use dynamic sql within a stored procedure, user must have
permissions (SELECT,UPDATE...) on underlyaing tables.
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||This is a security feature.

> Is there a workaround for it?
In 2005, you can specify EXECUTE AS for the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dcruncher4@.aim.com> wrote in message news:1140310808.885046.206220@.g47g2000cwa.googlegroups
.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>|||To add to the other responses, an unbroken ownership chain (e.g. 'dbo' owns
all objects involved) does not change the execution context. With an
unbroken chain, *object* permissions are simply not checked on indirectly
referenced objects and note that dynamic SQL always breaks the ownership
chain. *Statement* permissions (e.g. ALTER TABLE) are always checked in the
execution security context. The execution context can't be changed on
versions prior to SQL 2005.
The need to execute DDL by non-privileged users and use dynamic SQL can
indicate an application design issue. Perhaps someone can suggest an
alternative if you provide the requirements driving this approach.
Hope this helps.
Dan Guzman
SQL Server MVP
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegroups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>

alter table inside a stored procedure

Hi,
Our application needs to issue an alter table statement. Since the user
using
the application does not have dbo permission, we are planning to use
a stored procedure with dynamic sql.
SET @.RUNSQL = "alter table dbo.gggg .."
EXEC(@.RUNSQL)
The stored procedure is owned by dbo. However it is not allowing
the alter table because of lack of permission. Does that mean
that any EXEC inside a stored procedure does not run as user
dbo.
Is there a workaround for it?
thanks.
Hi
Well , if you use dynamic sql within a stored procedure, user must have
permissions (SELECT,UPDATE...) on underlyaing tables.
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegr oups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>
|||This is a security feature.

> Is there a workaround for it?
In 2005, you can specify EXECUTE AS for the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dcruncher4@.aim.com> wrote in message news:1140310808.885046.206220@.g47g2000cwa.googlegr oups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>
|||To add to the other responses, an unbroken ownership chain (e.g. 'dbo' owns
all objects involved) does not change the execution context. With an
unbroken chain, *object* permissions are simply not checked on indirectly
referenced objects and note that dynamic SQL always breaks the ownership
chain. *Statement* permissions (e.g. ALTER TABLE) are always checked in the
execution security context. The execution context can't be changed on
versions prior to SQL 2005.
The need to execute DDL by non-privileged users and use dynamic SQL can
indicate an application design issue. Perhaps someone can suggest an
alternative if you provide the requirements driving this approach.
Hope this helps.
Dan Guzman
SQL Server MVP
<dcruncher4@.aim.com> wrote in message
news:1140310808.885046.206220@.g47g2000cwa.googlegr oups.com...
> Hi,
> Our application needs to issue an alter table statement. Since the user
> using
> the application does not have dbo permission, we are planning to use
> a stored procedure with dynamic sql.
> SET @.RUNSQL = "alter table dbo.gggg .."
> EXEC(@.RUNSQL)
> The stored procedure is owned by dbo. However it is not allowing
> the alter table because of lack of permission. Does that mean
> that any EXEC inside a stored procedure does not run as user
> dbo.
> Is there a workaround for it?
> thanks.
>

alter permission on SP

Hi,
I have created a sql user and granted CREATE PROCEDURE rights on that
database for that user and added the user in the db_datareader and
db_denydatawriter database group. I am curious to know why the user cannot
ALTER a procedure. I have tried removing the user from the db_denydatawriter
database role but I am still unable to alter the SP (I assume it is because
the SPs are owned by dbo user).
Any hints?
--
I saw it work in a cartoon once so I am pretty sure I can do it.
Sasan,
I don't see why the user can not alter the procedure he/she created. If he
tries to alter one where he is not the owner (either direct or indirect
through his role memberships), then he will have problem. But if he can
create one, he should be able to alter it.
Quentin
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.
|||It is because they are owned by dbo.
If your user is called Frog, then the following is possible:
CREATE PROC Frog.LeapFrog
AS
-- Code Here.
Your user should also be able to do the following:
ALTER PROC Frog.LeapFrog
AS
-- Code Here
They should also be able to do the following:
CREATE PROC dbo.LilyPad
AS
-- Code Here
But they will NOT be able to do an
ALTER PROC dbo.LilyPad
Notes:
If your user does the following:
CREATE PROC Swim
AS
-- Code here
Then in order for them to alter that procedure it would be
ALTER PROC Frog.Swim
If they simply try ALTER PROC Swim it will try to alter a procedure named
dbo.Swim which does not exist, nor if it did exist, would they have
permissions.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.

alter permission on SP

Hi,
I have created a sql user and granted CREATE PROCEDURE rights on that
database for that user and added the user in the db_datareader and
db_denydatawriter database group. I am curious to know why the user cannot
ALTER a procedure. I have tried removing the user from the db_denydatawriter
database role but I am still unable to alter the SP (I assume it is because
the SPs are owned by dbo user).
Any hints?
--
--
I saw it work in a cartoon once so I am pretty sure I can do it.Sasan,
I don't see why the user can not alter the procedure he/she created. If he
tries to alter one where he is not the owner (either direct or indirect
through his role memberships), then he will have problem. But if he can
create one, he should be able to alter it.
Quentin
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.|||It is because they are owned by dbo.
If your user is called Frog, then the following is possible:
CREATE PROC Frog.LeapFrog
AS
-- Code Here.
Your user should also be able to do the following:
ALTER PROC Frog.LeapFrog
AS
-- Code Here
They should also be able to do the following:
CREATE PROC dbo.LilyPad
AS
-- Code Here
But they will NOT be able to do an
ALTER PROC dbo.LilyPad
Notes:
If your user does the following:
CREATE PROC Swim
AS
-- Code here
Then in order for them to alter that procedure it would be
ALTER PROC Frog.Swim
If they simply try ALTER PROC Swim it will try to alter a procedure named
dbo.Swim which does not exist, nor if it did exist, would they have
permissions.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.

alter permission on SP

Hi,
I have created a sql user and granted CREATE PROCEDURE rights on that
database for that user and added the user in the db_datareader and
db_denydatawriter database group. I am curious to know why the user cannot
ALTER a procedure. I have tried removing the user from the db_denydatawriter
database role but I am still unable to alter the SP (I assume it is because
the SPs are owned by dbo user).
Any hints?
--
--
I saw it work in a cartoon once so I am pretty sure I can do it.Sasan,
I don't see why the user can not alter the procedure he/she created. If he
tries to alter one where he is not the owner (either direct or indirect
through his role memberships), then he will have problem. But if he can
create one, he should be able to alter it.
Quentin
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.|||It is because they are owned by dbo.
If your user is called Frog, then the following is possible:
CREATE PROC Frog.LeapFrog
AS
-- Code Here.
Your user should also be able to do the following:
ALTER PROC Frog.LeapFrog
AS
-- Code Here
They should also be able to do the following:
CREATE PROC dbo.LilyPad
AS
-- Code Here
But they will NOT be able to do an
ALTER PROC dbo.LilyPad
Notes:
If your user does the following:
CREATE PROC Swim
AS
-- Code here
Then in order for them to alter that procedure it would be
ALTER PROC Frog.Swim
If they simply try ALTER PROC Swim it will try to alter a procedure named
dbo.Swim which does not exist, nor if it did exist, would they have
permissions.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:8339D23B-11CD-48C5-AEA7-9F87F7B90150@.microsoft.com...
> Hi,
> I have created a sql user and granted CREATE PROCEDURE rights on that
> database for that user and added the user in the db_datareader and
> db_denydatawriter database group. I am curious to know why the user cannot
> ALTER a procedure. I have tried removing the user from the
db_denydatawriter
> database role but I am still unable to alter the SP (I assume it is
because
> the SPs are owned by dbo user).
> Any hints?
> --
> --
> I saw it work in a cartoon once so I am pretty sure I can do it.

Allow broadcast from CLR Stored Procedure without using permission set 'Unsafe'

Hello,

I develop a database that notifies clients when data changes by sending an UDP broadcast message using an extended stored procedure. Now I want to use a CLR stored procedure to send the UDP broadcast instead:

using System;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Net.Sockets;

public partial class UserDefinedFunctions

{

[SqlProcedure]

public static void UdpSend(SqlString address, SqlInt32 port, SqlString message)

{

System.Net.Sockets.UdpClient client = new System.Net.Sockets.UdpClient();

byte[] datagram = message.GetUnicodeBytes();

client.Send(datagram, datagram.Length, (string)address, (int)port);

}

};

I have found that to be allowed to send to 255.255.255.255 I must give the assembly permission set 'Unsafe'. If I change to 'External access' I get:

Msg 6522, Level 16, State 1, Procedure UdpSend, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'UdpSend':

System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.CodeAccessPermission.Demand()

at System.Net.Sockets.Socket.CheckSetOptionPermissions(SocketOptionLevel optionLevel, SocketOptionName optionName)

at System.Net.Sockets.UdpClient.CheckForBroadcast(IPAddress ipAddress)

at System.Net.Sockets.UdpClient.Send(Byte[] dgram, Int32 bytes, String hostname, Int32 port)

at UserDefinedFunctions.UdpSend(SqlString address, SqlInt32 port, SqlString message)

I cannot use permission set 'Unsafe' in production environment, so what I want is to customize the effective permissions with higher resoloution than the three pre-defined permission sets 'Safe', 'External access' and 'Unsafe'. Except from what is allowed by 'Safe' I only want the permissions necessary to send an UDP broadcast.

Anyone who has something like this ?

No, you can not alter any of the existing permission sets. What you can do is, by using CAS, further restricting what a permission set can do by setting (for that assembly) specific CAS policies. However, you still have to create the assembly with whatever permission set, that is required.

Niels
|||You can't modify the built-in SQL CLR permission sets, but you can restrict your assembly's permission grant by using assembly-level permission attributes. However, your assembly would still need to be deployed at the UNSAFE level in order to be granted the SecurityPermission\UnmanagedCode permission that it needs.