Re: which users can see everybody in sysusers?



(ray@xxxxxxxxxx) writes:
When my Access front end logs in, it first looks at the list of users
in sysusers. It compares this list to an application managed table of
user names (App.SecurityUser). If there are new users in sysusers on
SQL server, it adds these new users to App.SecurityUser. BUT, if it
finds that there are users in App.SecurityUser that are no longer in
sysusers, it DELETES them from App.SecurityUser.

This has worked well so far, but a new user has just logged in who
can't see the other users in sysusers. So it clobbers everybody else
from App.SecurityUser. (With the result that they aren't allowed to
log in.)

Presumeably, this user has different permissions than are normally
given to users.

The other way round. In SQL 2005, users do by default not have permission
to view the definition of entities that they don't have any permission to.

The best solution in this case, is to put the code that access
sys.database_principals (which is what you should use on SQL 2005)
in a stored procedure, sign that procedure with a certificate,
create a user from the certificate and grant that use VIEW DEFINITION
on database level.

For a lot more details on procedure signing, there is an article on
my web site: http://www.sommarskog.se/grantperm.html that covers this
in depth.

You can also grant the problematic user VIEW DEFINITION on database
level directly, but the he can see every stored procedure etc, so I
would not recommed this.

1) Can a user know that if it is seeing only a partial list of other
users? (In which case, I can bypass this code.)

fn_my_permissions() may help.




--
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
.