Procedure with execute as owner - failing

Question

Post reply

Procedure with execute as owner - failing

JohnG69

Hall of Fame

Points: 3114
More actions
October 14, 2015 at 1:50 pm

#321454

Hi all,
I'm having some issues trying to get the WITH EXECUTE AS OWNER to work without success.
My simple example is as follows:
I have a database called MyDB for which I put the owner of the DB to be 'SA'.
I have a windows group called Operators which contains the windows user operator1...
To the Windows Group, In SSMS, I added the following roles: db_ddladmin, db_datareader and db_datawriter.
Since I want operator1 to run a DBCC Checkdb as part of a nightly process, In SSMS, with a sysadmin account, I created a very simple Procedure:
Create procedure dbo.Check_database
With Execute as Owner as
DBCC CHECKDB;
under operator1, I then tried to run the the procedure, Exec dbo.Check_database... I noticed that the Operators group didn't have execute permission...
After giving the execute permission, to the Operators group (DB user), I now get the following error:
The Server Principal "sa" is not able to access the database "MyDB" under the security context.
I realized that the sa account was disabled, as it should be... I changed the owner of the database to the local administrator, which in my case is the sql sysadmin...
and now, I still get this error:
The Server Principal "[machinename]\administrator" is not able to access the database "MyDB" under the security context.
Any idea?
JG

Viewing 6 posts - 1 through 5 (of 5 total)

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

JohnG69 Hall of Fame Points: 3114 More actions · Answer 1

I was able to get it work...

All that was missing was:

ALTER Database MYDB Set TrustWorthy ON;

By setting it on, the Operator group is now able to Run the Procedure that runs the DBCC CheckDB.

but since the operator group is part of the db_ddladmin role... does that mean that they can Create any procedure with the "with execute as owner" in the dbo schema and run anything they want?

If so, how else can I have a user in the operator group run a DBCC CheckDB without compromising security?

It's been 2 days that I'm banging my head on this one, Any help is greatly appreciated.

Thank you

JG

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 2

Why do you feel it necessary to change ownership in a proc to run DBCC CHECKDB? It shouldn't be executed by users.

--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)

JohnG69 Hall of Fame Points: 3114 More actions · Answer 3

Hi Jeff,

The person that is always logged is the operator (a windows user which is already logged in), part of the Operators (windows group).

We don't have their login or password... We do have another windows group called administrators, but again we don't have logins and passwords... And mixed mode authentication is disabled.

we don't have access to SQL Agent (since, in most cases customers are using express)

my job is to restrain the operators group access, since now it's a free for all...

Since the minimum permission for the CHECKDB and SHRINKDB required is DB_OWNER, I thought of impersonation of the db_owner by the operators... but that means that I have to enable the database's TrustWorthy flag... which I don't really like.

After discussing it with the developers here, we're thinking of doing the impersonation in the code instead of in SQL.

any thoughts?

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

jghali (10/15/2015)
Hi Jeff,
The person that is always logged is the operator (a windows user which is already logged in), part of the Operators (windows group).
We don't have their login or password... We do have another windows group called administrators, but again we don't have logins and passwords... And mixed mode authentication is disabled.
we don't have access to SQL Agent (since, in most cases customers are using express)
my job is to restrain the operators group access, since now it's a free for all...
Since the minimum permission for the CHECKDB and SHRINKDB required is DB_OWNER, I thought of impersonation of the db_owner by the operators... but that means that I have to enable the database's TrustWorthy flag... which I don't really like.
After discussing it with the developers here, we're thinking of doing the impersonation in the code instead of in SQL.
any thoughts?

Understood and sure. My initial take on this is that none of them should be doing a DBCC CHECKDB. It would be a whole lot easier (IMHO) to do such things in a scheduled job and to direct the step output to either a table or a log file. You could easily email the results to whomever needed to see the results.

On the SHRINKDB thing, I wouldn't allow it no matter who was doing it. It uses a fair a pretty good amount of resources, doesn't fix anything if you're shrinking to compensate for something like daily growth, and causes extremely massive (99.99% typical) fragmentation of the database that's been shrunk.

--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)

JohnG69 Hall of Fame Points: 3114 More actions · Answer 5

Thanks for you input.

I tend to agree with you regarding these 2 maintenance operations but these are existing scheduled operations that we offer in our software. Are they really needed?

These maintenance tasks are scheduled within the software and are using the wrong user to execute them.

The question that I need to understand is why it was offered in the first place and what added value is it to the customers.

Thank you

JG