Making sp_permissions work in 2005 vs 2000
- From: "Neil Meyer" <nmeyer@xxxxxxxx>
- Date: Fri, 20 Oct 2006 14:34:17 -0700
Hi. Someone shared this procedure with me earlier this year (I'm sorry I
can't find who). Anyway, it's great for SQL Server 2000, but doesn't work
in 2005 because the old security views in 2000 have been superceded by
catalogs in 2005.
Can anyone out there make sp_permissions work in 2005 and share it with us?
Here's sp_permissions for 2000 (it will run as is in 2005 but everything is
NULL, so it's not running correctly because 2005 does security differently
than 2000):
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Permissions]
AS
/*
Note: You will see multiple Objects with the same name if the
user has more than one inheritance for that table.
Keep in mind that Granted superseeds revoked and denied superseeds all
when viewing the output. Keep in mind this is still a project in
progress and I will be adding to it. This will capture the defined
object permissions and not things like db_owner, db_denydatareader
and such but I paln to add this later.
SELECT
Grant, revoke, or deny SELECT permissions on this object.
INSERT
Grant, revoke, or deny INSERT permissions on this object.
UPDATE
Grant, revoke, or deny UPDATE permissions on this object.
DELETE
Grant, revoke, or deny DELETE permissions on this object.
EXEC
Grant, revoke, or deny EXECUTE permissions on this object.
DRI
Grant, revoke, or deny declarative referential integrity permissions on this
object
*/
SET NOCOUNT ON --Don't want all the counts from the process to return
--Check for and drop our temp table if exists
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE [name] LIKE '#tmpInher%')
DROP TABLE #tmpInher
--Create our temp work table to make sure we have all the inheritance
CREATE TABLE #tmpInher (
[qid] [int] IDENTITY (1,1) NOT NULL,
[user] [int] NOT NULL,
[inherfrom] [int] NOT NULL,
PRIMARY KEY (
[user],
[inherfrom]
)
)
--Insert the inheritance base items which are the users themselves.
INSERT INTO #tmpInher ([user], [inherfrom]) SELECT [uid], [uid] FROM
sysusers WHERE issqlrole = 0 AND hasdbaccess = 1 and uid != 1
--Loop thru until we get all the inheritance items that a user is associated
with.
WHILE EXISTS (SELECT
oT.[user],
groupuid
FROM
sysmembers
INNER JOIN
#tmpInher oT
ON
oT.[inherfrom] = sysmembers.memberuid
WHERE
groupuid NOT IN (
SELECT
inherfrom
FROM
#tmpInher iT
WHERE
iT.[user] = oT.[user]
)
)
BEGIN
INSERT INTO #tmpInher ([user], [inherfrom])
SELECT
oT.[user],
groupuid
FROM
sysmembers
INNER JOIN
#tmpInher oT
ON
oT.[inherfrom] = sysmembers.memberuid
WHERE
groupuid NOT IN (
SELECT
inherfrom
FROM
#tmpInher iT
WHERE
iT.[user] = oT.[user]
)
END
--Check permissions for the user from all inheritance paths.
SELECT
u2.[name] AS UserName,
u1.[name] AS InheritesVia,
CASE xtype
WHEN 'U' THEN 'Table'
WHEN 'V' THEN 'View'
WHEN 'S' THEN 'System'
WHEN 'P' THEN 'Procedure'
WHEN 'FN' THEN 'Function'
END AS ObjectType,
sysobjects.[name] AS Object,
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 1) = 1 THEN 'Granted'
WHEN (actmod & 1) = 1 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [SELECT],
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 8) = 8 THEN 'Granted'
WHEN (actmod & 8) = 8 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [INSERT],
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 2) = 2 THEN 'Granted'
WHEN (actmod & 2) = 2 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [UPDATE],
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 16) = 16 THEN 'Granted'
WHEN (actmod & 16) = 16 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [DELETE],
CASE WHEN xtype IN ('P','FN') THEN
CASE
WHEN (actadd & 32) = 32 THEN 'Granted'
WHEN (actmod & 32) = 32 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [EXEC],
CASE WHEN xtype IN ('U','V','S') THEN
CASE
WHEN (actadd & 4) = 4 THEN 'Granted'
WHEN (actmod & 4) = 4 THEN 'Denied'
ELSE 'Revoked'
END
ELSE ''
END AS [DRI]
FROM
syspermissions
INNER JOIN
#tmpInher
INNER JOIN
sysusers u1
ON
u1.uid = [inherfrom]
INNER JOIN
sysusers u2
ON
u2.uid = [user]
ON
[inherfrom] = grantee
INNER JOIN
sysobjects
ON
sysobjects.[id] = syspermissions.[id]
ORDER BY
[UserName],
[ObjectType],
[Object]
--Drop out temp table as we no longer need.
DROP TABLE #tmpInher
.
- Prev by Date: Re: An error occurred during decryption
- Next by Date: GRANT Permission To User
- Previous by thread: Re: An error occurred during decryption
- Next by thread: GRANT Permission To User
- Index(es):
Relevant Pages
|
