How to ALTER COLUMN without SELECT and UPDATE permissions

Question

Post reply

How to ALTER COLUMN without SELECT and UPDATE permissions

Pete Softown

Say Hey Kid

Points: 669
More actions
March 26, 2021 at 8:40 pm

#3862552

I try to ALTER a column by changing the datatype from integer to NUMERIC(35,10). IMPORTANT : The user that execute the DDL has db_ddladmin role and that's allright because we only want it to do DDL instructions and no be able to extract data for security reasons.
With this query :
ALTER TABLE tablename ALTER COLUMN columnName NUMERIC(35,10);
I get this error :
Msg 229, Level 14, State 5, Line 5
The SELECT permission was denied on the object 'tablename', database 'databaseName', schema 'dbo'.
Msg 229, Level 14, State 5, Line 5
The UPDATE permission was denied on the object 'tablename', database 'databaseName', schema 'dbo'.
I understand that it tries to look at data to see if some could be truncated but can I skip this check ? I tried WITH NOCHECK but it doesn't work.
Is there another way to do this ? another role or workaround ?
Thanks for your help
Pete
March 30, 2021 at 7:52 am

This was removed by the editor as SPAM
April 2, 2021 at 3:37 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

Does 'tablename' in the errors you're getting match the tablename in the ALTER TABLE statement?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Pete Softown Say Hey Kid Points: 669 More actions · Answer 2

Hi Jeff,

Yes, the tablename are the same. In the error message its the name of the table containing the column I want to ALTER.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

Hi Pete,

This is interesting. I've never seen such a thing before. The reason for my question is I'm looking for something else that might be causing this problem for you. Can you check to see if the system, database, and the table have any type of trigger on them? Also, check for other things that are using that column. For example, are there any views, functions, indexes, or keys that point at that particular column?

As a bit of a side bar (and I don't know if it was just an example or not), jumping from an INT (only 4 Bytes) to a NUMERIC(35,10) (17 Bytes) seems like a very strange change to me. I can see changing from INT to BIGINT but changing to something that will handle such huge numbers (NUMERIC(35,10) will handle SEPTILLIONS!) with rather tight accuracy (nanos) is really an odd requirement. With that, and I'm just curious, why the need for such a strange change? What business requirement is driving that?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90245 More actions · Answer 4

I am confused by the requirement - it appears you want to be able to grant a user the ability to change the database schema, but that same user who can make these types of changes cannot select, update, delete the data?

I don't think that is going to be possible. I could see it being possible if - and only if - the change was a meta-data only change and did not require any type of validation on the existing data in the table. As soon as the change needs to validate the data then the user making the change must be able to select and update that data.

For this example - SQL must update the data in the table and modify every page with the updated column definition. The original data and column definition does not have the necessary information to determine where the decimal will be placed - let alone how the data is stored in the page, which will be different because it can no longer be defined and stored in 4-bytes.

If you need to provide this type of functionality to a user - without granting them access, then you probably will need to use EXECUTE AS with dynamic SQL or some other way of escalating permissions.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

It's an amazing thing... I've been working with SQL Server since before SQL Server 7 came out and it just dawned on me that I've never tried to alter a column on a table without having at least DBO privs.

With that in mind, I setup a standard Windows user on my laptop, created that AD user on the SQL instance, gave it only db_DDLAdmin privs on one database, logged in as that user, tried to alter a column datatype, and "BLAM"... same "Select" error. I reverted back to me as the login and everything went just fine for the alter.

That means that you can forget that I asked the questions that I previously did. 😀

So, yeah... not possible directly. The suggestion that Jeffrey made in his last paragraph above is probably the only way to pull this off without granting extra privs. It also has the advantage of the user not needing db_DDLAdmin privs to begin with.

My question remains, though. Why on Earth would anyone need to change an INT column to something that can handle Septillions with a resolution of 1/10th of a nano??? And what's the need for having a user that has such power to begin with? Is this one of those things where you want a DBA or database owner to have all the responsibility of maintaining a database without being able to see any of the data? If so, I suppose that it's possible to do with things like special stored procedures that use impersonation like Jeffrey suggested above... you do have to be very careful to not allow SQL Injection. Personally, it think it would be better to temporarily grant a DBA "dbo" privs to do what needs to be done under supervision and then disable their login.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Pete Softown Say Hey Kid Points: 669 More actions · Answer 6

Hi Jeff and Jeffrey,

The change from INT to numeric was for an example only. You can try changing a varchar(10) to a varchar(9) and get the same error. But you don't get the error changing from varchar(9) to varchar(10), so I think the check on data must be skipped because there cannot be data truncated

I felt the same way. I never tested this ddl command without being dbowner so I've never had this error before. But since we wanted to use flyway and give it just enough permission to do his job, we didn't want to give datareader and datawriter permissions. It's a requirement from the client.

If we look at the definition from the Microsoft Documentation Database Levels roles :

db_ddladmin

Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

That's a pretty clear sentence here. But not totally true ! In a way, I was hoping that the SQL engine could look at data by itself to execute the DDL. Especialy considering that there's no data in the output of this ddl command

With the help of your suggestions I'll try to figure a workaround to allow Flyway to achieve his duty with the least privileges it needs!

Thanks!

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

Thanks for the observations you made on this, Pete. It might be worthy of a new "Feedback" item as a "bug".

And, yeah... I can see people doing this... there have been many requests in the past where people wanted a DBA (or whatever) to be able to do their job but not see any of the data. And I totally agree with the requirements of the Client and that's becoming the norm rather than the exception lately.

Like what has been said and just to repeat it as a reminder, you could build a stored procedure that would do this and EXECUTE AS OWNER and then only provide privs to the stored procedure. I don't know how that with fit in with what needs to be done with flyway, though because I've never used it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)