login via ad groups - query to find this info.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Martin - 22 Jul 2008 11:05 GMT

Hi,

Is it possible to run a query to find all the ways a user has been granted a
login to SQL server.
for example a user may have a login because their windows login has been
given login righs to sql server,
This senario is pretty easy to find.
However a user may have login rights because they have been granted this via
an AD group that has been given
access to SQL Server.

Can I run a query that tells me all of the ways - and ad groups - that a
given user / login belongs to with the sql server.
I am using sql server 2005

any help is appreciated.

many thanks in advance.

cheer

martin.

Reply to this Message

Uri Dimant - 22 Jul 2008 13:07 GMT

Hi
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

> Hi,
>
[quoted text clipped - 18 lines]
>
> martin.

Reply to this Message