Can the Developer ALTER the Table?

Question

Can the Developer ALTER the Table?

Steve Jones - SSC Editor

SSC Guru

Points: 736494
More actions
August 1, 2022 at 12:00 am

#4060388

Comments posted to this topic are about the item Can the Developer ALTER the Table?

August 15, 2022 at 8:54 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jose.DBA Valued Member Points: 55 More actions · Answer 1

The original premise of the question is that the sql login has no permissions other than CONNECT to the database, but when I tried granting CONTROL on a schema to a user with no other permissions it was still unable to CREATE a table (much less ALTER) in that schema. I've used the code below. Am I missing something?

/* connected as sysadmin, prep test */SELECT SUSER_NAME() AS starting_user
use master
GO
CREATE LOGIN JoeUser 
  WITH PASSWORD=N'JoeUser', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE DATABASE SchemaTest
GO
USE [SchemaTest]
GO
CREATE USER [JoeUser] FOR LOGIN [JoeUser] WITH DEFAULT_SCHEMA=[dbo]
GO
CREATE SCHEMA Dev
GO
GRANT CONTROL ON SCHEMA::Dev TO JoeUser
GO

/* running commands as JoeUser */EXECUTE AS LOGIN = 'JoeUser'
SELECT SUSER_NAME() AS switch_to_JoeUser
CREATE TABLE Dev.MyTable (myid INT) --<< this errors with "CREATE TABLE permission denied in database 'SchemaTest'."
GO

/* check perms on schema and db (nothing about CREATE), then revert to sysadmin */SELECT SUSER_NAME() AS user_now
SELECT * FROM sys.fn_my_permissions('dev','schema')
SELECT * FROM sys.fn_my_permissions(NULL,'database')
REVERT;

/* clean up */USE master
SELECT SUSER_NAME() AS post_revert
DROP DATABASE SchemaTest
DROP LOGIN JoeUser

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 2

Apologies, mis-worded question. Because the CREATE TABLE works, this user must have GRANT CREATE TABLE also. I'll change the wording and fix points.