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: 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
Books Online for SQL Server 2000 at

Relevant Pages

  • Re: Stored Procedure Security/Permissions
    ... Cross-Database Ownership Chaining Behavior Changes in SQL Server 2000 ... > permission to read either table; ... > permission to execute the stored procedure, ...
  • Re: Table Type Permissions
    ... SQL Server DBA ... to pass table-valued parameters to a stored procedure. ... permission on the type is needed for a user to create a stored procedure ... REFERENCES ON TYPE::MyType? ...
  • Re: Stored procedure to drop and create table for end user
    ... Columnist, SQL Server Professional ... such) so the only thing the end user has access to is execute permission on ... back with select permission not granted on a table the stored procedure is ... but a temp table needs ...
  • Re: Security Checking
    ... that user requiring an associated SELECT permission on the underlying ... If a stored procedure A executes stored procedure B, ... My opening examples imply they are only checked "on entry", ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
  • Security Checking
    ... SQL Server allows for a user to have SELECT permission on a View without ... If a stored procedure A executes stored procedure B, ... I guess to paraphrase what I am trying to determine is whether SQL Server ... examples imply they are only checked "on entry", but I am wondering if this ...