Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create table permission Expand / Collapse
Author
Message
Posted Wednesday, May 11, 2011 2:46 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 14, 2016 8:28 AM
Points: 1,203, Visits: 3,239
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

Post #1107353
Posted Thursday, May 12, 2011 12:13 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 16, 2016 11:44 AM
Points: 6,639, Visits: 1,905
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1107976
Posted Thursday, May 12, 2011 12:45 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 14, 2016 8:28 AM
Points: 1,203, Visits: 3,239
@ k brian

Thanks a lot

Regards.
Sushant
Post #1107992
Posted Friday, October 18, 2013 12:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 1, 2014 1:27 PM
Points: 1, Visits: 42
Did you test it? It looked like not working
Post #1506295
Posted Thursday, September 10, 2015 10:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 1, 2015 9:47 AM
Points: 161, Visits: 704
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
Post #1718483
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse