Posted by Erland Sommarskog on 02/08/07 23:00

akkha1234@gmail.com (akkha1234@gmail.com) writes:
> When I click on the properties of a 2005 sql server database and then
> permissions.
> If I select a group and then effective permission, I got an error,
> saying that "cannot execute as the principal server because the 'xxx
> \group' does not exist, this type of principal cannot be impersonated,
> or you do not have permission (Microsoft SQL Server; Error: 15406)"
>
> I guess the error is valid, since the group does not exist in the
> database as a user. If so, how do I get the effective permissions of a
> group?

I would think the easiest would be to take a user which is a member of that
group, but I was not able to get that to work. Or more precisely, I was
not able to impersonate as such a user.

You can always look directly into sys.database_permissions, but I don't
really know this information is exposed. In SQL 2005 you can grant a
principal a permission on a schema, which then applies to all objects
in that schema. But I don't think there is a row for every object in
the schema, but I have not investigated this.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

[Back to original message]