Effective Permissions Error with Domain User

If I create a new Login at the server level for a domain group :i.e.,
MyDomain\Domain Users
Then I go add a user tied into this new Login to my database i.e., Northwind
or pubs to add a user tied to this login.
Why is it then when I go to Database Properties -> Permissions and select
Effective Permissions on this new user I get the following error:
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot execute as the server principal because the principal "EGUIRES\Domain
Users" does not exist, this type of principal cannot be impersonated, or you
do not have permission. (Microsoft SQL Server, Error: 15406)
For help, click:
tSrc=MSSQLServer&EvtID=15406&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...06&LinkId=20476
BUTTONS:
OK
The guest account seems fine for this but any manual entries I try that is
linked to my domain gets this error.
I would appreciate any light you can shed on this
Scott EguiresScott
Did you get these database from SQL Server 2000 by using a RESTORE command?
What is the compatibilty level of both databases?
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:870F7328-15EF-40E5-B7ED-7F9262C46DD7@.microsoft.com...
> If I create a new Login at the server level for a domain group :i.e.,
> MyDomain\Domain Users
> Then I go add a user tied into this new Login to my database i.e.,
> Northwind
> or pubs to add a user tied to this login.
>
> Why is it then when I go to Database Properties -> Permissions and select
> Effective Permissions on this new user I get the following error:
>
> TITLE: Microsoft SQL Server Management Studio
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> Cannot execute as the server principal because the principal
> "EGUIRES\Domain
> Users" does not exist, this type of principal cannot be impersonated, or
> you
> do not have permission. (Microsoft SQL Server, Error: 15406)
> For help, click:
> EvtSrc=MSSQLServer&EvtID=15406&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...06&LinkId=20476
> --
> BUTTONS:
> OK
>
> The guest account seems fine for this but any manual entries I try that is
> linked to my domain gets this error.
>
>
> I would appreciate any light you can shed on this
>
> Scott Eguires
>|||Yes I did. I set the database compatibility to 2005. WHat is interesting i
s
that Effective Permissions work for the Domain\Administrator group. I did a
server profile trace and found that it was calling the Execute As User. Thi
s
led me to the following clues:
When I run the following:
EXECUTE AS USER = 'MYDOMAIN\Domain Users';
I get the error:
Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal
"MYDOMAIN\Domain Users" does not exist, this type of principal cannot be
impersonated, or you do not have permission.
But if I run:
EXECUTE AS USER = 'MYDOMAIN\Administrator';
It works fine.
This leads me to believe it is some sort of permissions issue.
Any info would be greatly appreciated.
Thanks
"Uri Dimant" wrote:

> Scott
> Did you get these database from SQL Server 2000 by using a RESTORE command
?
> What is the compatibilty level of both databases?
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:870F7328-15EF-40E5-B7ED-7F9262C46DD7@.microsoft.com...
>
>|||Scott
Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
the user a member of loca Administrator group on a computer when you run
SQL Server?
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...[vbcol=seagreen]
> Yes I did. I set the database compatibility to 2005. WHat is interesting
> is
> that Effective Permissions work for the Domain\Administrator group. I did
> a
> server profile trace and found that it was calling the Execute As User.
> This
> led me to the following clues:
> When I run the following:
> EXECUTE AS USER = 'MYDOMAIN\Domain Users';
> I get the error:
> Msg 15517, Level 16, State 1, Line 1
> Cannot execute as the database principal because the principal
> "MYDOMAIN\Domain Users" does not exist, this type of principal cannot be
> impersonated, or you do not have permission.
>
> But if I run:
> EXECUTE AS USER = 'MYDOMAIN\Administrator';
> It works fine.
> This leads me to believe it is some sort of permissions issue.
> Any info would be greatly appreciated.
> Thanks
>
> "Uri Dimant" wrote:
>|||No the 'MYDOMAIN\Domain Users' login is neither a member of the local
administrator
group nor a member of the sysadmin server role on the machine I am running
SQL Server.
"Uri Dimant" wrote:

> Scott
> Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
> the user a member of loca Administrator group on a computer when you run
> SQL Server?
>
>
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...
>
>|||Scott
I tried tp reproduce the problem on my worstation SQL Server 2005 (Dev
Edition)
1) Restore a database from SQL Server 2000 which had an user called 'John'
(I changed compatibility level to 90)
2) Create a new Login 'NewJohn' and granted him an access to the database
(non sysadmin)
3) Right Click on the database --Properties --Permissions ( I sow
'John' user along with'NewJohn' )
4) Click on Effective Permissions ( I got the same error)
5) Click on Remove button and removed 'John' user
6) Click on Effective Permissions again and now it working
I think it is because that user "John" was transfered as a database user
(orphaned) without mapping to any login on the new server
MS has provided two stored procedures to move the logins with their original
SID but it worked fime on SS2K and I have not tried yet on SS2005
"Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
news:4F36A7F9-3108-4142-875A-2DB8CA46ABB3@.microsoft.com...[vbcol=seagreen]
> No the 'MYDOMAIN\Domain Users' login is neither a member of the local
> administrator
> group nor a member of the sysadmin server role on the machine I am running
> SQL Server.
>
> "Uri Dimant" wrote:
>|||It seems that you cannot execute as user on a Group. Which makes sense sinc
e
the text is
Execute as User. So this tells me I will never be able to get the effective
permissions for a group
through the SQL Workbench tool which is fine I suppose. I had an additional
problem in that I had
my service account was set to local and needed to set it to a domain user
with appropriate rights. Without that my Execute as user fails since it
cannot resolve the user in my domain.
Scott Eguires
"Uri Dimant" wrote:

> Scott
> Is the 'MYDOMAIN\Domain Users' login a member of sysadmin server role.Is
> the user a member of loca Administrator group on a computer when you run
> SQL Server?
>
>
>
> "Scott Eguires" <ScottEguires@.discussions.microsoft.com> wrote in message
> news:2D896D0E-CA3E-49A6-9B90-CE157B2A56E6@.microsoft.com...
>
>