July 13, 2009 at 10:03 pm
Hi,
We are using SQL Server 2005 and we have a requirement to access a database with the following scenerios.
1. Users/Groups should be able to create/drop/alter/execute Stored Procedures.
2. Users/Groups should be able to do all DML operations in table (Insert/Update/Delete/Truncate) and Select
3. Users/Groups should not be able to do any DDL operations (Create tables/Alter tables/Drop tables)
Please provide your inputs to acheive this using T-SQL or GUI.
Thanks,
Balaji.
July 13, 2009 at 10:29 pm
TRUNCATE is a DDL operations and not a DML operation.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 14, 2009 at 12:53 pm
SQL Server's security model is rather simple: if you aren't given specific rights to do something, you can't do it. This takes care of #3. As for the rest.
#1 - Look up GRANT CREATE PROCEDURE in Books Online.
#2 - Look up GRANT SELECT, INSERT, UPDATE, DELETE in Books Online.
Truncate Table requires ownership of the table. Therefore, if they had that, they could drop the table or alter it and you can't stop them. As Barry pointed out, it's not a DML operation.
K. Brian Kelley
@kbriankelley
July 15, 2009 at 2:05 am
Best is to work with minimal rights.
So go for the solution provided by K. Brian Kelley !
But as a little safeguard, you could implement a database trigger to only allow the ddl actions you want.
This trigger is no excuse to implement "dbo for everyone" !!!
e.g.
CREATE TRIGGER [ddlDatabaseTriggerNonSA]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
-- Restrict DDL to Func and Sproc for non-sa
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
DECLARE @schema sysname;
DECLARE @object sysname;
DECLARE @eventType sysname;
declare @db_id int;
declare @db_name sysname;
select @db_id = db_id();
select @db_name = db_name();
SET @data = EVENTDATA();
SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
IF @object IS NOT NULL
PRINT ' ' + @eventType + ' - ' + @schema + '.' + @object;
ELSE
PRINT ' ' + @eventType + ' - ' + @schema;
IF @eventType IS NULL
PRINT CONVERT(nvarchar(max), @data);
IF NOT ( @eventType LIKE '%function%'
OR @eventType LIKE '%procedure%' )
BEGIN
IF IS_SRVROLEMEMBER ('sysadmin',SUser_SName()) = 0 -- only sysadmin are allowed to non fn sproc stuff
BEGIN
RAISERROR ('You are not entitled to perform this modification [d]' , 1,1,@eventType) WITH log
ROLLBACK TRAN
END
END
/* log the intended action in your audit db */
INSERT [TheLoggingDB].[dbo].[TheLoggingTB]
([DatabaseID],
[DatabaseName],
[PostTime],
[DatabaseUser],
[Event],
[Schema],
[Object],
[TSQL],
[XmlEvent]
)
VALUES
(@db_id,
@db_name,
GETDATE(),
CONVERT(sysname, Suser_Sname()),
@eventType,
CONVERT(sysname, @schema),
CONVERT(sysname, @object),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
);
END;
Off course, if they shut down your dbtrigger, ....
having minimal rights this would come to :
use SSC_Test
drop table dbo.T_truncate ;
Create table dbo.T_truncate (idnr int identity primary key, tsisrt datetime not null default getdate()) ;
go
/* fill the tb with some data */
set nocount on
go
insert into dbo.T_truncate default values
go 100
set nocount off
go
Select suser_sname(), count(*) from dbo.T_truncate ;
go
Grant select, alter on dbo.T_truncate to theDEV;
go
execute as user = 'theDEV'
Select suser_sname(), count(*) from dbo.T_truncate ;
truncate table dbo.T_truncate ;
Select suser_sname(), count(*) from dbo.T_truncate ;
drop table dbo.T_truncate ;
revert;
Select suser_sname(), count(*) from dbo.T_truncate ;
resulting in :
Beginning execution loop
Batch execution completed 100 times.
who nRows
------------- -----------
TheSYSADMIN 100
(1 row(s) affected)
who nRows
------------- -----------
theDEV 100
(1 row(s) affected)
who nRows
------------- -----------
theDEV 0
(1 row(s) affected)
Msg 3701, Level 14, State 20, Line 10
Cannot drop the table 'T_truncate', because it does not exist or you do not have permission.
who nRows
------------- -----------
TheSYSADMIN 0
(1 row(s) affected)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 15, 2009 at 9:27 am
Thanks for your inputs Brian and ALZDBA.
I will check these options and revert back.
Thanks,
Balaji.
July 18, 2009 at 5:19 am
Thanx ALZDBA, Its working fine.
Thanks,
Balaji.
July 18, 2009 at 8:54 am
Which one did you pick ?
- the ddl trigger ?
- least privileged ?
- both ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 19, 2009 at 9:43 pm
Hi,
The DDL Trigger itself solved my issue. I didnot go for the minmal rights.
Thanks,
Balaji.
July 19, 2009 at 10:00 pm
Hi,
The DDL Trigger itself solved my issue. I didnot go for the minmal rights.
Thanks,
Balaji.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply