Determining permissions through Stored Procedures

Is it possible in SQL 2005 to determine what rights a user has to a given DB
(down to the table level) using a Stored Procedure, or Function? I would lik
e
to know if a user has "Insert" rights to a table before I give them an "Add
New" button on my form.
Thanks
DaveDave,
Using sp_helprotect is the traditional method, but it does not return
information about securables introduced in SQL Server 2005. You can use
sys.database_permissions and fn_builtin_permissions instead.
Here is something I use for quick checks that might help you get started.
select u.name, p.permission_name, p.class_desc, object_name(p.major_id)
ObjectName, state_desc
from sys.database_permissions p join sys.database_principals u
on p.grantee_principal_id = u.principal_id
order by ObjectName, name, p.permission_name
select u.name DatabaseRole, u2.name Member
from sys.database_role_members m
join sys.database_principals u on m.role_principal_id = u.principal_id
join sys.database_principals u2 on m.member_principal_id = u2.principal_id
order by DatabaseRole
RLF
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:CB769D2E-1E60-440D-A474-C44B36C6860E@.microsoft.com...
> Is it possible in SQL 2005 to determine what rights a user has to a given
> DB
> (down to the table level) using a Stored Procedure, or Function? I would
> like
> to know if a user has "Insert" rights to a table before I give them an
> "Add
> New" button on my form.
> Thanks
> Dave
>|||Dave (Dave@.discussions.microsoft.com) writes:
> Is it possible in SQL 2005 to determine what rights a user has to a
> given DB (down to the table level) using a Stored Procedure, or
> Function? I would like to know if a user has "Insert" rights to a table
> before I give them an "Add New" button on my form.
Check out the function Has_perms_by_name in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx