June 19, 2015 at 5:15 am
This is a weird one. I have a periodic backup task, and when I need a development copy on which to test code, I restore my most recent backup to a different name, switch the ODBC link to that name on my development machine and have a current copy of the database to play with.
I've been doing this for years, and it works great. Just now I did it, and suddenly my development machine is unable to run any stored procedures that have the 'Execute as owner' clause in their definition. I'm using domain accounts, my personal account is the owner of the database, and everything works on the production copy, which is in the same instance on the same machine.
The test copy is identical to the production copy, which continues to work fine - it was just created using by restoring the backup of the production copy, but I can't run anything with this clause. As soon as I delete the 'Execute as owner' line, the procedure is suddenly available. If I put it back, I'm locked out again.
The error message is: The server principal “sa” is not able to access the database “WhateverDB” under the current security context
June 19, 2015 at 7:01 am
Was the sa login recently disabled or renamed on the instance where your development is pointing to?
Joie Andrew
"Since 1982"
June 19, 2015 at 1:27 pm
Joie Andrew (6/19/2015)
Was the sa login recently disabled or renamed on the instance where your development is pointing to?
As far as I know, nothing has changed. At least, I haven't changed anything, and I am the sole programmer/developer/DBA/everything on this server. However, something clearly HAS changed, and now my production database is showing the same behavior - refuses to execute any SP with the Execute as owner clause. But even at that, I don't get it. -I- am the owner, not sa.
June 19, 2015 at 2:51 pm
Is the owning account a single user account? Or did it get changed into a group account or role?
When you use "EXECUTE AS OWNER" the owner cannot be a group account or role, it must be a single-user account.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 19, 2015 at 3:38 pm
ScottPletcher (6/19/2015)
Is the owning account a single user account? Or did it get changed into a group account or role?When you use "EXECUTE AS OWNER" the owner cannot be a group account or role, it must be a single-user account.
Hm, I'll have to check that. I -think- it's my own personal account, but I'm not able to access the machine right now. In any case, it has been working for years - just took a dump this morning, after no changes that I'm aware of.
Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Or me trying to code T-SQL.
June 21, 2015 at 2:29 pm
Well, I've made some progres on it. I had to manually change a bunch of permissions, and I got it running again. However, I have no idea why something that has worked for years suddenly went on strike. I'm still convinced that I didn't do anything, and if I did accidentally make a change, it certainly was not as radical as manually changing the permissions on fifty views, tables, function and procedures.
I though I had permissions fairly well set up, but apparently I still have no idea what I'm doing. The interplay of users, roles, logins, schemas, individual permissions, chaining permissions, ownership and all that is still about as clear to me as a politician's actual beliefs. I get something set up, it seems to work, but when I try to make some changes according to the way I understand things, either nothing changes or I break something.
I read articles till I've got SQL terminology pouring out my ears, think I've got a grip, and nothing. Does anyone know of an article or tutorial with comprehensive coverage of this stuff, from idiot level up to somewhere in the functional range?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply