Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Sunday, February 09, 2014 9:25 PM
Points: 1,200, Visits: 3,236
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: Thursday, April 03, 2014 10:06 PM
Points: 6,621, Visits: 1,851
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: Sunday, February 09, 2014 9:25 PM
Points: 1,200, Visits: 3,236
@ 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: Tuesday, December 03, 2013 1:02 PM
Points: 1, Visits: 40
Did you test it? It looked like not working
Post #1506295
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse