Re: Determining User's Effective Rights
From: Jasper Smith (jasper_smith9@hotmail.com)
Date: 10/27/02
- Next message: Gidi Ehrenhalt: "Security and Authentication Mode"
- Previous message: Bob Hilton: "Re: Determining User's Effective Rights"
- In reply to: BP Margolin: "Re: Determining User's Effective Rights"
- Next in thread: Bob Hilton: "Re: Determining User's Effective Rights"
- Reply: Bob Hilton: "Re: Determining User's Effective Rights"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Jasper Smith" <jasper_smith9@hotmail.com> Date: Sun, 27 Oct 2002 01:29:05 +0100
You could see if sp_helprotect will do what you want
Also below is a proc by Itzik Ben-Gan that uses the
PERMISSIONS function
HTH
Jasper Smith
USE master
GO
IF OBJECT_ID('sp_ListPermissions') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID('sp_ListPermissions'),'IsProcedure')=1
DROP PROCEDURE sp_ListPermissions
GO
CREATE PROCEDURE sp_ListPermissions
@object nvarchar(515) = NULL,
@column sysname = NULL
AS
/*
Procedure to list a uses effective permissions
Author : Itzik Ben-Gan
*/
SET NOCOUNT ON
DECLARE @permissions int
DECLARE @objectid int
SET @objectid = object_id(@object)
--Check input
IF @object IS NOT NULL AND @objectid IS NULL
BEGIN
RAISERROR('Invalid object %s supplied.', 16, 1, @object)
RETURN 1
END
IF @objectid IS NOT NULL
BEGIN
PRINT 'Object permissions on: ' + @object + ' for login: ' + SUSER_SNAME()
+ ', user: ' + USER_NAME() + '...'
IF @column IS NOT NULL
BEGIN
SET @permissions = permissions(@objectid, @column)
PRINT 'On column: ' + @column + '...'
IF @permissions & 1 = 1 PRINT 'SELECT'
IF @permissions & 2 = 2 PRINT 'UPDATE'
IF @permissions & 4 = 4 PRINT 'REFERENCES'
END -- @column IS NOT NULL
ELSE -- @column IS NULL
BEGIN
SET @permissions = permissions(@objectid)
IF @permissions & 1 = 1 PRINT 'SELECT ALL'
IF @permissions & 2 = 2 PRINT 'UPDATE ALL'
IF @permissions & 4 = 4 PRINT 'REFERENCES ALL'
IF @permissions & 8 = 8 PRINT 'INSERT'
IF @permissions & 16 = 16 PRINT 'DELETE'
IF @permissions & 32 = 32 PRINT 'EXECUTE'
IF @permissions & 4096 = 4096 PRINT 'SELECT ANY (at least one column)'
IF @permissions & 8192 = 8192 PRINT 'UPDATE ANY (at least one column)'
IF @permissions & 16384 = 16384 PRINT 'REFERENCES ANY (at least one
column)'
END -- @column IS NULL
END -- @objectid IS NOT NULL
ELSE -- @objectid IS NULL
BEGIN
SET @permissions = permissions()
PRINT 'Statement permissions for login: ' + SUSER_SNAME() + ', user: ' +
USER_NAME() + '...'
IF @permissions & 1 = 1 PRINT 'CREATE DATABASE'
IF @permissions & 2 = 2 PRINT 'CREATE TABLE'
IF @permissions & 4 = 4 PRINT 'CREATE PROCEDURE'
IF @permissions & 8 = 8 PRINT 'CREATE VIEW'
IF @permissions & 16 = 16 PRINT 'CREATE RULE'
IF @permissions & 32 = 32 PRINT 'CREATE DEFAULT'
IF @permissions & 64 = 64 PRINT 'BACKUP DATABASE'
IF @permissions & 128 = 128 PRINT 'BACKUP LOG'
--IF @permissions & 256 = 256 PRINT 'Reserved'
END -- @objectid IS NULL
GO
grant exec on sp_ListPermissions to public
GO
"BP Margolin" <bpmargo@attglobal.net> wrote in message
news:Ozp8z4QfCHA.1756@tkmsftngp12...
> Bob,
>
> See is the T-SQL PERMISSIONS built-in function provides you what you need.
> Documentation on the PERMISSIONS function can be found in the SQL Server
> 2000 Books Online.
>
> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.
>
> "Bob Hilton" <bhilton@psmanagement.net> wrote in message
> news:25a601c27d0b$be5d4110$35ef2ecf@TKMSFTNGXA11...
> > I am using Windows 2000 and SQL Server 2000 in Windows
> > Authentication mode in a single domain with 40 servers.
> > Users are Members of Functional Groups with a
> > corresponding Role in SQL 2000. Users may be in more than
> > one Windows 2000 Group and the corresponding groups' Roles
> > may be members of multiple application Roles within the
> > database. Is there a tool that will allow me to determine
> > the Effective Rights for a given user? sp_helpuser
> > reports for the "domain" groups but does not go to the
> > individual users. With over a dozen groups and hundreds
> > of users, this is a real bear to do by hand.
> >
> > Any help with ideas or tools is greatly appreciated.
> >
>
>
- Next message: Gidi Ehrenhalt: "Security and Authentication Mode"
- Previous message: Bob Hilton: "Re: Determining User's Effective Rights"
- In reply to: BP Margolin: "Re: Determining User's Effective Rights"
- Next in thread: Bob Hilton: "Re: Determining User's Effective Rights"
- Reply: Bob Hilton: "Re: Determining User's Effective Rights"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
