Re: SQL 2005 ALTER ANY LOGIN trouble

There are two types of principals in SQL Server - logins (server wide
principals) and users (database specific principals). Logins are used to
gain access to the server and can hold server level permissions. When you
connect to a specific database, your login will be mapped to a user. Users
can hold database specific permissions. At any point in time, what you can
do is determined by your login and user context.

When you impersonate a user, you only get the database context - no server
permissions will be available to that impersonated context. No errors are
given, because this isn't an error scenario - you just don't have
permissions under that specific context that you set up.

You can find more about EXECUTE AS and execution context from the
"Understanding Execution Context" article:
http://msdn2.microsoft.com/en-us/library/ms187096.aspx.

There is an execution context presentation I made that you might find
helpful at: http://cmcgc.com/Media/WMP/261115/. There are other security
presentations there as well.

Thanks

--
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/

This posting is provided "AS IS" with no warranties, and confers no rights.

"Ol Boldyrev" <olboldie@xxxxxxxxx> wrote in message
news:uaJhcYlNHHA.4720@xxxxxxxxxxxxxxxxxxxxxxx
Thank you, Erland, for your response!

That was my blunder indeed. Using 'as user' instead of 'as login'. (one
would expect an exception though, something about a missing user..)
It works OK with domain accounts, too.

Oleg

"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns98B63A1E1674Yazorman@xxxxxxxxxxxx
Ol Boldyrev (olboldie@xxxxxxxxx) writes:
That's what I'm doing:
1.
grant alter ANY login to [Domain\User]
which executes successfully. Then strange things begin to happen, .

And you were in master when you did this?

Was Domain\User added explicitly as login prior to this, or did it
have implicit access through a group?

For what it's worth, I was successful with this:

create login alteranylogin with password='cccccc'
grant alter any login to alteranylogin
execute as login = 'alteranylogin'
create login alteranylogin2 with password='cccccc'
revert

But this was an SQL login. Running on my home machine, it is somewhat
difficult to test with domain users.

--
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: System Administrator Implied Permissions
    ... > sa login, it assigns it the System Administrator fixed ... > Now, given this, why does SQL Server ... in each database is always a member of the public and db_owner roles. ... Other sysadmin role members have the exact same ...
    (microsoft.public.sqlserver.security)
  • Re: cannot login to the db after...
    ... Jasper Smith (SQL Server MVP) ... I have created a new database, "db_1", using the "sa" ... I then created a new login, "sqluser1" and gave ...
    (microsoft.public.sqlserver.security)
  • Re: Cant view merge agent properties (trying again)
    ... In the List of Actions for the Snapshot Agent History I see this repeated: ... every single database listed. ... So, just now, I went to computername\Administrator Login ID (because it's ... On the computer running SQL Server, ...
    (microsoft.public.sqlserver.replication)
  • Re: Unexpected Login Screen When Accessing SQL Data Via .NET Intra
    ... >> data and the SQL Server is on the same server. ... >> My problem is that one user has started getting a login popup ... >> right in via Windows Authentication like everyone else and like they ...
    (microsoft.public.sqlserver.connect)
  • Re: cannot acees two databases as owner
    ... it does not matter you are a member of the ... the sysadmin fixed server role or if there is no any other Login which is ... group if it's a domain) and you can login to your SQL Server with, ...
    (microsoft.public.sqlserver.setup)