October 12, 2012 at 8:20 am
John Mitchell-245523 (10/12/2012)
Not DENY ALTER SCHEMA. Something like DENY ALTER ON SCHEMA::SchemaName. You'd have to check out the exact syntax yourself if that isn't quite correct.John
DENY ALTER ON SCHEMA::SchemaName works, thanks.
Now I need to figure out how to DENY DROP TABLE and yes I have looked up the syntax but what I have found does not work.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 12, 2012 at 8:31 am
So you've tried DENY DELETE instead of DENY DROP?
john
October 12, 2012 at 8:49 am
John Mitchell-245523 (10/12/2012)
So you've tried DENY DELETE instead of DENY DROP?john
I do not want to restrict the users from Deleting Data, I just want to restrict them from Dropping Tables.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 12, 2012 at 8:59 am
Has the user in question tried dropping any objects since you denied ALTER on the schema?
John
October 12, 2012 at 9:52 am
John Mitchell-245523 (10/12/2012)
Has the user in question tried dropping any objects since you denied ALTER on the schema?John
Not yet, thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 12, 2012 at 12:59 pm
There is a fix on these posts.
http://www.sqlservercentral.com/Forums/Topic768507-359-1.aspx
http://www.sqlservercentral.com/Forums/Topic581296-359-2.aspx
DENY ALTER ON SCHEMA::[dbo] to [someuser]
October 12, 2012 at 9:31 pm
John Mitchell-245523 (10/11/2012)
Welsh Corgi (10/11/2012)
Could you please expand on your comments about DELETE and the ON Clause?Yes - try DENYing DELETE or ALTER on the schema that the tables are in.
Deny permission overide all permissions granted explicitly or implicitly through a role.
Yes, I think you're right. I must have been thinking of the dbo user.
I was able to DENY CREATE TABLE to two users and it worked.
Indeed. Like I said, I don't know why the rules might be different for DENY CREATE and DENY ALTER/DELETE.
I'm sysadmin and I could DENY Read Permissions and it would work.
Read permission? I think the rules for DDL and DML permissions might be slightly different.
John
If you need to revoke the DENY I assume that you execute a revoke?
If I execute the following the DENY ALTER ANY SCHEMA does no show up?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 12, 2012 at 9:54 pm
I revoke the permissions. Later I ran the following command on the schema and it returned no rows, so it looks like I'm good to go.
EXEC sp_helprotect NULL, NULL,dw
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 8 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply