May 11, 2011 at 2:46 pm
Hi,
I am trying to give a user (ccr) to create table permission in 1 of db's.
I ran this:-
grant create table to ccr
it says command done successfully
BUT
ccr cant create table now also.
Then, i ran:-
grant create table on schema::dbo to ccr
Then, it says
Msg 102, Level 15, State 1, Line 0
Incorrect syntax near 'CREATE TABLE..'.
How shld i give permissions :
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
May 12, 2011 at 12:13 pm
Since CCR does not own the dbo schema, you must give permission to ALTER it, too.
GRANT ALTER ON SCHEMA::dbo TO ccr;
However, you probably want to create a role, given the permissions to the role, and make ccr a member of the role. For instance:
CREATE ROLE ModifyTable;
GO
GRANT CREATE TABLE TO ModifyTable;
GRANT ALTER ON SCHEMA::dbo TO ModifyTable;
GO
EXEC sp_addrolemember 'ModifyTable', 'ccr';
GO
REVOKE CREATE TABLE FROM ccr;
Now, because a user has ALTER permissions on the schema, he/she can affect existing objects. So you'll have to build a DDL trigger to restrict the role to just being able to touch tables. There are examples of this in the forums and the scripts if you do a search.
K. Brian Kelley
@kbriankelley
May 12, 2011 at 12:45 pm
@ k brian
Thanks a lot
Regards.
Sushant
Regards
Sushant Kumar
MCTS,MCP
October 18, 2013 at 12:36 pm
Did you test it? It looked like not working
September 10, 2015 at 10:57 am
K. Brian Kelley (5/12/2011)
Since CCR does not own the dbo schema, you must give permission to ALTER it, too.
GRANT ALTER ON SCHEMA::dbo TO ccr;However, you probably want to create a role, given the permissions to the role, and make ccr a member of the role. For instance:
CREATE ROLE ModifyTable;GO
GRANT CREATE TABLE TO ModifyTable;
GRANT ALTER ON SCHEMA::dbo TO ModifyTable;
GO
EXEC sp_addrolemember 'ModifyTable', 'ccr';
GO
REVOKE CREATE TABLE FROM ccr;
Now, because a user has ALTER permissions on the schema, he/she can affect existing objects. So you'll have to build a DDL trigger to restrict the role to just being able to touch tables. There are examples of this in the forums and the scripts if you do a search.
Here's some code for the DDL trigger referenced above:
-- This trigger fires on all DDL database level events (https://technet.microsoft.com/en-US/library/ms191441(v=SQL.90).aspx)
-- If the login is a member of the 'ModifyTable' role,
-- execute their DDL statement only if it is one of the following SQL statements:
--1. CREATE TABLE
--2. DROP TABLE
--3. ALTER TABLE
--4. SELECT INTO
CREATE TRIGGER db_trigger_BlockNonTableDDL
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
IF IS_MEMBER('ModifyTable') = 1
BEGIN
DECLARE @TriggerEventText nvarchar(max);
SET @TriggerEventText = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
IF NOT ((@TriggerEventText LIKE 'CREATE TABLE%') OR
(@TriggerEventText LIKE 'DROP TABLE%') OR
(@TriggerEventText LIKE 'ALTER TABLE%') OR
(@TriggerEventText LIKE 'SELECT % INTO %'))
BEGIN
RAISERROR (@TriggerEventText, 16, 1)
ROLLBACK TRANSACTION;
END
END;
END;
GO
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply