How to Lock out DBA from Salary tables

Is there a way to lock out DBAs from accessing certain tables e.g. Salary ta
bles.
If so what permissions need to be set?Peter,
No! The SysAdmin account is GOD! There is no way to prevent a SysAdmin
account from doing anything it wants with any of the tables or databases.
If you have sensitive information, then you should consider encrypting the
information using some mechanism unknown to your DBA.
Chief Tenaya
"Peter" <peter@.offtomexico.com> wrote in message
news:F58B95B8-640B-45B7-A90C-BCFDA4FBDA3E@.microsoft.com...
> Is there a way to lock out DBAs from accessing certain tables e.g. Salary
tables.
> If so what permissions need to be set?|||A DBO yes. If your DBA is a member of the Sysadmins group, then the answer
is no.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

How to list sp perms?

I query information_schema.table_privileges for permissions to tables
What do I query to get list of all permsissions grants on stored procedures
and udf's?
tia
chrisChris
In SQL Server 2005, you can use the Has_Perms_By_Name() function
(http://msdn2.microsoft.com/en-us/library/ms189802.aspx).

> For example, I would like to list all stored procedures which a role has
> execute permission for.
This is an example of usage:
SELECT o.SchemaAndName,
has_perms_by_name(o.SchemaAndName, 'OBJECT', 'EXECUTE')
FROM (SELECT name, SCHEMA_NAME(schema_id) AS [schema],
SCHEMA_NAME(schema_id)+'.'+name AS SchemaAndName
FROM sys.objects
WHERE type = 'P') AS o
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:3303F0F9-082F-48B1-AD48-0406FDB03761@.microsoft.com...
> I query information_schema.table_privileges for permissions to tables
> What do I query to get list of all permsissions grants on stored
> procedures
> and udf's?
> tia
> chris

How to list permissions for Public Role for a database in SQL Server

Hello,
Does anybody have the transact SQL to find the permissions granted to the public role in a Database ?
Also looking for the SQL to find the permissions granted to the user Guest in a database ?
thanksYou can do both of them using the following SELECT statement:SELECT a.[name] + ' ' + v.[name] + ' ON ' + QuoteName(oo.[name])
+ '.' + QuoteName(o.[name]) + ' TO ' + QuoteName(u.[name])
FROM dbo.sysprotects AS p
JOIN master.dbo.spt_values AS a
ON (a.number = p.protecttype
AND 'T' = a.type)
JOIN master.dbo.spt_values AS v
ON (v.number = p.action
AND 'T' = v.type)
JOIN dbo.sysobjects AS o
ON (o.id = p.id)
JOIN dbo.sysusers AS oo
ON (oo.uid = o.uid)
JOIN dbo.sysusers AS u
ON (u.uid = p.uid)
WHERE 'public' = u.name-PatP|||Pat Phelan,
Thanks for this. It works perfectely for Public Role.

However how will it work for the Guest user ?|||However how will it work for the Guest user ?Yes, if you change the constant it will work for any user.

-PatP|||You can also use:

sp_helprotect @.username='public'

sp_helprotect @.username='guest'

Regards,

hmscott|||thanks hmscott and PatP for the solutions.