August 31, 2007 at 12:49 pm
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
September 3, 2007 at 2:37 am
You can find this out for individual users wit the help of the has_perms_by_name function.
For example to see if the current user has perimission to sys.syslogins:
SELECT has_perms_by_name('sys.syslogins', 'OBJECT', 'SELECT')
To see all the tables, views, ... including system ones in the current database that I can select from:
SELECT s.name AS [Schema], o.name AS [Object]
FROM sys.all_objects AS o JOIN sys.schemas AS s ON o.schema_id=s.schema_id
WHERE has_perms_by_name(s.name + '.' + o.name, 'OBJECT', 'SELECT') = 1
AND o.type IN ('S', 'U', 'V', 'FT', 'TF', 'IF')
Note that it is only the current user, so you will need to use "execute as user ='foo' "
An alternative is to go through the role memberships, and the sys.database_permissions table.
Or, you could deny permission to these objects explicitly?
Regards,
Andras
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply