SQL Permissions - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL Permissions

Author

Topic

nishithrn
Yak Posting Veteran

58 Posts

Posted - 2003-12-27 : 07:32:29

Is it that whenever any permission is granted/revoked, an entry is made in one of the system tables..?? If yes, which is that table..???

Regards

Nazim
A custom title

1408 Posts

Posted - 2003-12-27 : 09:41:23

BOL says
sysprotects
Contains information about permissions that have been applied to security accounts with the GRANT and DENY statements. This table is stored in each database.

HTH

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2004-01-02 : 07:31:26

or sysobjects..........

CREATE PROCEDURE usp_GrantSELECTPermissions
/* Grant SELECT permissions to ALL tables in UserRpt DB */
AS

SET NOCOUNT ON

DECLARE @tablename varchar(50)

DECLARE @tablepermission CURSOR

SET @tablepermission = CURSOR FOR

SELECT
name
FROM
sysobjects
WHERE
type = 'U'

OPEN @tablepermission

FETCH NEXT FROM @tablepermission
INTO @tablename

WHILE @@fetch_status = 0

BEGIN
EXEC ('GRANT SELECT ON dbo. ' + @tablename + ' TO UserReport')

FETCH NEXT FROM @tablepermission
INTO @tablename
END

CLOSE @tablepermission
DEALLOCATE @tablepermission

====
Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources