Avoid alter and drop command on a Table and SP in SQL Server
I have a table and a SP in SQL Server. I want to add Permissions on a table and SP that no one can change the Structure of table and Logic of SP. Is there any way to specify such type of Permissions. Any Trigger which avoids drop and alter commands, or any other way to do this. Thanks in Advance.
It is highly recommended that you manage the permissions on the objects. However, if you have no control over the permissions, consider setting up a database DDL trigger to at least log the events.
create table AuditTable ( event_type varchar(max) not null , tsql_command varchar(max) not null , modified_by varchar(128) not null default (current_user) , modified_time datetime not null default (getdate()) ) go create trigger log_database_level_event on database for ddl_database_level_events as insert AuditTable ( event_type , tsql_command ) values ( eventdata().value('(/EVENT_INSTANCE/EventType)', 'varchar(max)') , eventdata().value('(/EVENT_INSTANCE/TSQLCommand)', 'varchar(max)') ) go create user tester without login go execute as user = 'tester' go create proc test_proc as select @@version go alter proc test_proc as select 1 go revert go select * from AuditTable go
You need to create and use a separate user that has only privileges that you explicitly allow it to (eg GRANT SELECT from table or GRANT EXECUTE on your stored procedure).
Rather than looking at it as disallowing certain actions you should consider what actions are allowed (see Principle of Least Privilege).
Yes, this is possible but not using constraint . Constraint is a bussiness rule kind of validation and here your question about Permission on Objects so now this is clear that you need to define permission on object for specific user.If you want to secure your Table and Stored Procedure then please follow this step.
- Create one new User/Login with specific Database/Objects permission.
Give grant on your Secure table using - GRANT SELECT ON <TableName> TO <Username>
GIVE grant on your Secure Stored Procedure using - GRANT EXECUTE ON <SP Name> TO <Username>
for further regarding permission please do some search on Google .