Re: all permissions for a particular login

From: Jasper Smith (jasper_smith9@hotmail.com)
Date: 11/13/02 

From: "Jasper Smith" <jasper_smith9@hotmail.com>
Date: Tue, 12 Nov 2002 23:29:36 -0000

You can try sp_helprotect , details in BOL

This example lists all permissions that user Judy
has in the current database.

EXEC sp_helprotect NULL, 'Judy'

Also you can try the following sp_ListPermissions from IBG

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
Credit goes to 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 

-- 
HTH
Jasper Smith (SQL Server MVP)
Check out the PASS Community Summit - Seattle, the largest and only user
event entirely dedicated to SQL Server, November 19-22.
http://www.sqlpass.org/events/seattle/index.cfm
"DC Gringo" <glevine@visiontechnology.net> wrote in message
news:uTHp7epiCHA.1784@tkmsftngp11...
> I would ike to get a list of all the permissions for a given login for a
> particular database. Is there an sp_xxx system stored procedure that will
> give me that ...or perhaps someone has written code for this?
>
> THANKS!
>
> _____
> DC G
>
>

Relevant Pages

  • Re: rights for each login
    ... This example lists all permissions that user Judy has in the current ... EXEC sp_helprotect NULL, 'Judy' ...
    (microsoft.public.sqlserver.server)
  • Re: Determining Users Effective Rights
    ... Thanks for the suggestions Jasper. ... The routine by Itzik ... >PERMISSIONS function ... >SET @objectid = object_id ...
    (microsoft.public.sqlserver.security)
  • Re: Determining Users Effective Rights
    ... Also below is a proc by Itzik Ben-Gan that uses the ... Procedure to list a uses effective permissions ... SET @objectid = object_id ... > Documentation on the PERMISSIONS function can be found in the SQL Server ...
    (microsoft.public.sqlserver.security)
  • Reg. Directory listing program
    ... I am new to this mailing list and I am very new to PERL. ... I wrote a code that lists files in a directory with the permissions. ... Directory listing program ...
    (perl.beginners)
  • Re: Different Access Permission Levels on Sharepoint CompanyWeb?
    ... > WSS provides the ability to control permissions on a per-list basis, ... > users, to groups of users, or to a site group. ... > have access to all lists and document libraries on that Web site. ...
    (microsoft.public.windows.server.sbs)
Quantcast