Query to determine public access

Is there any T-SQL Script or SP that would determine what tables public users have access to? I only ask because I would like to make sure that any users with public rights do not have access to SYSXLOGINS, SYSDATABASES, SYSOBJECTS, SYSCOLUMNS and mswebtasks tables. Thanks
-Kyle

All users are members of the public role by default and out of the box public won't have any explicit permissions assigned to it. So unless you've tampered with these settings, you should be ok.

Remember if you assign explict deny permissions on public this could have unexpected consequences as ALL logins won't have access to those tables.

Id suggest having a user defined database role which you add all users that has the desired permissions.

HTH!

|||Being a DBA in a somewhat large company means that you inherit a lot of the main aspects of the job. Along with that, you can't satisfy auditers with the explanation that out of the box the public user role won't have rights to the tables. Thus, if anybody has a query that would tell the tables that the public user role has that would be fantastic.

Query to connect to a database with different user

Hello:

Is there a way through T-SQL to run a store procedure and inside the store procedure, change the user, execute a select, and get back the original user that run the store procedure?

Thanks

In SQL 2005, yes there is.

Refer to Books Online, Topic: 'EXECUTE AS (Transact-SQL).

Example A demonstrates exactly what you are seeking.

|||

Thanks Arnie, is this possible in SQL 2000?|||

Unfortunately, no.

There is no easy way to cause security context switching.

|||You can use the Ad-Hoc remote quries, OPENROWSET & OPENDATASOURCE..