hi anyone please post query for all sql permissions and role permissions of all users in a database for SQLSERVER 2000. I did lot of research in the internet but i didn't find any script to resolve my issue...!
use Database Select User_Name(SP.Uid), ' On '+Sj.Name , (Case Xtype When 'C' Then 'CHECK constraint' When 'D' Then 'Default or DEFAULT constraint' When 'F' Then 'FOREIGN KEY constraint' When 'L' Then 'Log' When 'FN' Then 'Scalar function' When 'IF' Then 'Inlined table-function' When 'P' Then 'Stored procedure' When 'PK' Then 'PRIMARY KEY constraint (type is K)' When 'RF' Then 'Replication filter stored procedure ' When 'S' Then 'System table' When 'TF' Then 'Table function' When 'TR' Then 'Trigger' When 'U' Then 'User table' When 'UQ' Then 'UNIQUE constraint (type is K)' When 'V' Then 'View' When 'X' Then 'Extended stored procedure' End) As ObjectType , (Case Action When 26 Then 'REFERENCES' When 178 Then 'CREATE FUNCTION' When 193 Then 'SELECT' When 195 Then 'INSERT' When 196 Then 'DELETE' When 197 Then 'UPDATE' When 198 Then 'CREATE TABLE' When 203 Then 'CREATE DATABASE' When 207 Then 'CREATE VIEW' When 222 Then 'CREATE PROCEDURE' When 224 Then 'EXECUTE' When 228 Then 'BACKUP DATABASE' When 233 Then 'CREATE DEFAULT' When 235 Then 'BACKUP LOG' When 236 Then 'CREATE RULE' End) As ActionType , (Case ProtectType When 204 Then 'GRANT_W_GRANT' When 205 Then 'GRANT' When 206 Then 'REVOKE' End) As ProtectType , Columns from dbo.sysprotects SP Inner Join dbo.sysobjects SJ On Sp.id=SJ.id where sp.uid <>0
Roles and User Permissions Script for SQLSERVER 20
Posted - 06/04/2012 : 10:30:51
