Determining Users permissions

While it is possible and easy to answer the question:
"Which Users Have permissions on This Table?" by right-clicking the table,
choosing properties from the menu, then Permissions from the dialog.
Is it possible to answer the question:
"What Permissions does This user have?"
Right-clicking the user and choosing properties lets us choose a tab
"Securables" which is always empty. we can see Server Roles, but not
permissions.
We are using Active Directory Groups to group users into permission-roles.
We cannot use Schemas because a lot of our users use MS Access data projects
to view their data.
There doesn't seem to be any way to determine what permissions a User has,
from the user point of view.
I'm sure we could do this withthe Server 2000 Enterprise Manager, but not
with the new 2005 Management Studio.Hi
IF PERMISSIONS()&2=2
CREATE TABLE test_table (col1 INT)
ELSE
PRINT 'ERROR: The current user cannot create a table.'
--
This example determines whether the current user can
grant the INSERT permission on the authors table to another user.
IF PERMISSIONS(OBJECT_ID('authors'))&0x80000=0x80000
PRINT 'INSERT on authors is grantable.'
ELSE
PRINT 'You may not GRANT INSERT permissions on authors.'
--http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlr
ef/ts_pa-pz_6f78.asp>
How can I retrieve a list of objects and permissions for a specified role?
----
--
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
"BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
news:D130E054-26BD-4AB2-85BE-F4153BB7E7DD@.microsoft.com...
> While it is possible and easy to answer the question:
> "Which Users Have permissions on This Table?" by right-clicking the table,
> choosing properties from the menu, then Permissions from the dialog.
> Is it possible to answer the question:
> "What Permissions does This user have?"
> Right-clicking the user and choosing properties lets us choose a tab
> "Securables" which is always empty. we can see Server Roles, but not
> permissions.
> We are using Active Directory Groups to group users into permission-roles.
> We cannot use Schemas because a lot of our users use MS Access data
> projects
> to view their data.
> There doesn't seem to be any way to determine what permissions a User has,
> from the user point of view.
> I'm sure we could do this withthe Server 2000 Enterprise Manager, but not
> with the new 2005 Management Studio.
>|||Ok, thats all very well for coders, but for example, in Enterprise Manager,
if someone asks me "What permissions on the database does Joe Bloggs in
Accounts have?" I just look up Joe Blogs login, right-click and choose Manag
e
Permissions. I check the box for only showing permissions explicitly defined
and there it is.
I can't DO that in Management studio.
I had a quick look through the functions in master, but the permissions ones
just seem to be concerned with the *current* user. I can see the value of
these... do the test, and if the can, then do the action.
But for a harrased admin being asked the above question... what can be done?
I don't mind cobbling up soem T-SQL to return the perms if required, but it
must be able to be run for any login, not jsut the current one.
BAzz
"Uri Dimant" wrote:

> Hi
> IF PERMISSIONS()&2=2
> CREATE TABLE test_table (col1 INT)
> ELSE
> PRINT 'ERROR: The current user cannot create a table.'
> --
> This example determines whether the current user can
> grant the INSERT permission on the authors table to another user.
> IF PERMISSIONS(OBJECT_ID('authors'))&0x80000=0x80000
> PRINT 'INSERT on authors is grantable.'
> ELSE
> PRINT 'You may not GRANT INSERT permissions on authors.'
> --http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsq
lref/ts_pa-pz_6f78.asp>
> How can I retrieve a list of objects and permissions for a specified role
?
> ----
--
> In SQL Server 2005, you can use the Has_Perms_By_Name() function
> (http://msdn2.microsoft.com/en-us/library/ms189802.aspx).
>
> 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
>
>
>
>
> "BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
> news:D130E054-26BD-4AB2-85BE-F4153BB7E7DD@.microsoft.com...
>
>|||BAzz,
Assuming that you are a system administrator for the SQL Server, you can
execute in the context of the login that you are checking. Here is a
sample:
EXECUTE AS login='MYDOMAIN\MyLogin'
SELECT name, is_member(name) FROM sys.database_principals
WHERE is_member (name) = 1
SELECT name, (permissions(object_id)) FROM sys.objects
WHERE permissions(object_id) > 0
REVERT
FWIW, RLF
"BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
news:A8D05A43-38F5-4461-8334-06B8D4FBDC99@.microsoft.com...[vbcol=seagreen]
> Ok, thats all very well for coders, but for example, in Enterprise
> Manager,
> if someone asks me "What permissions on the database does Joe Bloggs in
> Accounts have?" I just look up Joe Blogs login, right-click and choose
> Manage
> Permissions. I check the box for only showing permissions explicitly
> defined
> and there it is.
> I can't DO that in Management studio.
> I had a quick look through the functions in master, but the permissions
> ones
> just seem to be concerned with the *current* user. I can see the value of
> these... do the test, and if the can, then do the action.
> But for a harrased admin being asked the above question... what can be
> done?
> I don't mind cobbling up soem T-SQL to return the perms if required, but
> it
> must be able to be run for any login, not jsut the current one.
> BAzz
>
> "Uri Dimant" wrote:
>|||Thats a great help, thanks. the Execute as... will do the trick in a
parameterised stored proc that I can have return a formatted list of perms..
Makes me wonder, though, why the functionality was removed from the 2005
Management studio.
"Russell Fields" wrote:

> BAzz,
> Assuming that you are a system administrator for the SQL Server, you can
> execute in the context of the login that you are checking. Here is a
> sample:
> EXECUTE AS login='MYDOMAIN\MyLogin'
> SELECT name, is_member(name) FROM sys.database_principals
> WHERE is_member (name) = 1
> SELECT name, (permissions(object_id)) FROM sys.objects
> WHERE permissions(object_id) > 0
> REVERT
> FWIW, RLF
>
> "BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
> news:A8D05A43-38F5-4461-8334-06B8D4FBDC99@.microsoft.com...
>
>|||FWIW, I wonder, too. The current Studio behavior is not helpful to me
either.
RLF
"BAzz" <Barry.Freeman@.nospam.co.uk> wrote in message
news:0FF6C000-6010-4119-B1DB-E84047507080@.microsoft.com...[vbcol=seagreen]
> Thats a great help, thanks. the Execute as... will do the trick in a
> parameterised stored proc that I can have return a formatted list of
> perms..
> Makes me wonder, though, why the functionality was removed from the 2005
> Management studio.
>
> "Russell Fields" wrote:
>