Re: SQL to get a list of users/roles/permissions
- From: "Neil Meyer" <nmeyer@xxxxxxxx>
- Date: Fri, 20 Oct 2006 11:03:48 -0700
This function doesn't do much -- it basically tells me (yes or no) if a user
is in a specific role, but then how do I list the effective permission for
that user for all non-system tables in the database?
Thanks
"Uri Dimant" <urid@xxxxxxxxxxx> wrote in message
news:OA4%23UdU7GHA.4708@xxxxxxxxxxxxxxxxxxxxxxx
JA
CREATE FUNCTION dbo.RoleCheckUser
(
@UserName sysname,
@RoleName sysname
)
RETURNS BIT
AS
BEGIN
DECLARE @RetVal BIT
SET @RetVal = 0
SELECT @RetVal = 1
WHERE EXISTS
(
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
WHERE
users.name = @UserName
AND groups.name = @RoleName
)
RETURN @RetVal
END
GO
-- Syntax to use the created function
SELECT dbo.RoleCheckUser('dbo', 'db_owner')
GO
"JA" <JA@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0F68942D-FD82-4D21-81A1-C0284354B1FA@xxxxxxxxxxxxxxxx
I'd like to perform an audit of users/roles/permissions within all my SQL
Server databases. How can I do so via a SQL query? I am looking for the
following sets of data:
1. username and role
2. role and permissions on each database table.
Thanks!!
.
- References:
- Re: SQL to get a list of users/roles/permissions
- From: Uri Dimant
- Re: SQL to get a list of users/roles/permissions
- Prev by Date: Re: Windows Group Access to SQL Scheduled Tasks - SQL 2000
- Next by Date: Re: An error occurred during decryption
- Previous by thread: Re: SQL to get a list of users/roles/permissions
- Next by thread: sp_xp_cmdshell_proxy_account error
- Index(es):
Relevant Pages
|
