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.

Answers


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)[1]', 'varchar(max)')
    , eventdata().value('(/EVENT_INSTANCE/TSQLCommand)[1]', '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.

  1. Create one new User/Login with specific Database/Objects permission.
  2. Give grant on your Secure table using - GRANT SELECT ON <TableName> TO <Username>

  3. 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 .


Need Your Help

MyISAM Selects locks inserts inside procedure only

mysql stored-procedures select insert myisam

We have a large MyISAM table to which rows get inserted to the bottom of the table only.

_CRTDBG_MAP_ALLOC not showing file name

c++ debugging memory-leaks crtdbg.h

I am trying to detect memory leak, and I am using make _CRTDBG_MAP_ALLOC macro to locate where the leaks area. So I am defining MACRO like following:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.