Re: Permission issue with EXECUTE AS

It appears I have finally determined the difference.

After talking with the network guy, he noted that I was NOT a member of the
group that has the sysadmin rights for that box, but my personal domain
account had been explicitly given sysadmin rights to the box. Which he was
irked at, because that is not supposed to happen, I should have been added to
the admin group. However, the machine account was in that group.

But, that IS A DIFFERENCE, even though we have the same "rights" either way.
So I found out what other accounts were given rights via the group, and
tracked those guys down and ran my script under their accounts, and BAM, got
the error. There was one other account that had been given explicit access
(also shouldn't be) and my script ran without errors on that account. If it
wasn't for the fact this box is a test server, and not under the network
team's direct control, this situation would have never been allowed to exist.

At last I have resolution. And now I know I can't go down that path without
jumping through more hoops, because company policy dictates not giving
individuals explict rights, it's done via group membership.

Thanks again Erland, this one was driving me nuts.

Bob

"Erland Sommarskog" wrote:

INTP56 (INTP56@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I've been posting in Programming as initially I thought this was a code
problem, but thought I would try here. I've included my script at the end.

Basically, when I run the script logged in as me, everything works as
expected. However, when I run the script logged in as the machine
account SQL Server runs under, I get an error. And the error moves
depending on how I specify the logins. I'm looking for help trying to
track this problem down, as the production server DBA's also create the
databases under a machine account, then let me log in to create the
objects.

There is something fishy going on here. It seems that the domain
account does not have CONTROL SERVER permission, but then again it
create databases and logins and grant permissions, so it does have a
good set of permissions anyway.

Do this: find a database which is not owned by the domain account
and do USE on that database, and then run "SELECT USER". If it says
"dbo", you are sysadmin after all.

But even if you are not sysadmin (that is, have CREATE SERVER), I don't
see why you would have to specify FOR LOGIN. FOR LOGIN should simply
not matter when username and login name are the same. And same goes
for the strange error you get. Had the database been restored from a
different server - but you have just created it.

Just to check: you have SP2 of SQL 2005, haven't you?

If you are able to nail exactly which server-level permissions your
machine has, we might be able to repro it, and maybe then understand
what is going on. Right now, I'm clueless.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: Please! Doesnt anyone know a better way to do this?
    ... account, they need to automatically be directed to the page to enter data ... session variable on the Account page. ... I assume here that you're checking a database when the user attempts to ... When a new user attempts to login or clicks to register, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Secure web authentication system w/o SSL and PKI
    ... In the beginning, a user should be able to register for an account, ... minted one-time password to their email address. ... it is assumed that only both the server and the end ... I'm making the big assumption that the user's email login has not been ...
    (comp.security.misc)
  • Re: ADP/SQL Server 2000 Security Problem
    ... The server is running Windows 2003. ... I'll also test using a SQL Server account and see what happens. ... it worked in MSDE 2000. ... I have not created any new accounts for the production database. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Help with WSS 3.0 Server Farm Config - Backend SQL 2005
    ... I had to use only "sharepoint" to get the ... What interest me though is that the database get created but fails after ... Virtual Server with DBSVR ... an account local to the WEBSVR) to create and access the SQL server, ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: Restored Server but SharePoint refusing admin access
    ... > SID/BID or remove the user from the database and add it again. ... >, In SQL Configuration Manager go to SQL> Server ... > you had) you cannot access the database from that account. ... > newly added administrator account (for me, since I added a new admin ...
    (microsoft.public.windows.server.sbs)