A few days ago, I saw someone asked if I can easily get the permissions of SQL Server2000 specified objects and the right to specify user.
limit. I wrote a stored procedure that can get the permissions of users and roles. Please help you test. See
No, there is bug :-)
IF ObjectProperty (Object_ID ('USP_GetObjectAuthor'), 'IsProcedure') = 1
Drop Proc USP_GETOBJECTAUTHOR
Go
/ ************************************************** **********************************
***** /
/ * Created by: leimin * /
/ * CREATED ON: 29 May 2004 * /
/ * Description: this Stored Procedure Returns The Object Permission Which
YOU * /
/ * GRANT, DENY AND ROKE.
* /
/ ************************************************** **********************************
***** /
Create Proc USP_GetObjectAuthor
@Objectname sysname = null,
@username sysname = null
AS
Set nocount on
Begin
/ ************************************************** **********************************
***** /
/ * Defined the initilization variable * /
/ ************************************************** **********************************
***** /
Declare @RC INT
Declare @Rowcount Int
Declare @groupid Int
Set @ rc = 0
Set @ rowcount = 0
/ ************************************************** **********************************
***** /
/ * Judge the input parameters, if @objectname is null and @username IS
Null * /
/ * The return all objects authorization. * /
/ ************************************************** **********************************
***** /
IF @Objectname is null and @username is null
Begin
Select Object_name (A.ID) As Objectname,
User_name (a.uid) AS Usename,
Case B.issqlrole when 1 Then 'Group'
Else 'User'
End as role,
Case A.ProtectType When 205 Ten 'Grant'
When 204 TEN 'Grant'when 206 Ten' Deny '
Else 'Revoke'
End as protecttype,
Case a. [Action] when 26 Ten 'References'
When 178 TEN 'CREATE FUNCTION'
When 193 Ten 'SELECT'
When 195 TEN 'INSERT'
When 196 Ten 'delete'
When 197 TEN 'UPDATE'
When 198 Ten 'Create Table'
When 203 Then 'Create Database'
When 207 TEN 'CREATE VIEW'
When 222 Ten 'Create Procedure'
When 224 Ten 'Execute'
When 228 Ten 'Backup Database'
When 233 TEN 'CREATE DEFAULT'
When 235 Ten 'Backup Log'
When 236 TEN 'CREATE RULE'
ELSE '0'
End as [action],
User_name (a.grantor) AS Grantor
From sysprotects a inner join sysusers b on a.uid = B.UID
WHERE EXISTS (SELECT 1 from sysobjects
Where [name] = Object_name (A.ID) and xtype <> 's')
Order by Object_name (A.ID)
SELECT @Rowcount = @@ RowCount
IF @ rowcount = 0
Begin
SELECT @ rc = -1
Print 'There a no user objects in database!'
Return @rc
end
end
/ ************************************************** **********************************
***** /
/ * Judge the input parameters, if @objectname is null and @username is not
Null * /
/ * Then return all objects authorization where reason @USERNAME * /
/ * If the user belong to a group, so we must add the group authorization * /
/ ************************************************** **********************************
***** /
IF @ rc = 0 and @username is not null and @Objectname is NULL
Begin
IF not exists (select * from sysusers where [uid] = user_id (@username) and
STATUS <> 0)
Begin
SELECT @ rc = -2
Print 'The user name is not include in sysusers Table.'Return @RC
end
IF exists (SELECT 1 from sysmembers where [memberuid] = user_id (@username))
Begin
Select Object_name (A.ID) As Objectname,
User_name (a.uid) AS Usename,
Case B.issqlrole when 1 Then 'Group'
Else 'User'
End as role,
Case A.ProtectType When 205 Ten 'Grant'
When 204 TEN 'GRANT'
When 206 TEN 'DENY'
Else 'Revoke'
End as protecttype,
Case a. [Action] when 26 Ten 'References'
When 178 TEN 'CREATE FUNCTION'
When 193 Ten 'SELECT'
When 195 TEN 'INSERT'
When 196 Ten 'delete'
When 197 TEN 'UPDATE'
When 198 Ten 'Create Table'
When 203 Then 'Create Database'
When 207 TEN 'CREATE VIEW'
When 222 Ten 'Create Procedure'
When 224 Ten 'Execute'
When 228 Ten 'Backup Database'
When 233 TEN 'CREATE DEFAULT'
When 235 Ten 'Backup Log'
When 236 TEN 'CREATE RULE'
ELSE '0'
End as [action],
User_name (a.grantor) AS Grantor
From sysprotects a inner join sysusers b on a.uid = B.UID
WHERE EXISTS (SELECT 1 from sysobjects
Where [name] = Object_name (A.ID) and xtype <> 's')
And (exists (SELECT 1 from sysmembers
Where groupuid = a.UID and meid = user_id (@username))
OR A.UID = User_ID (@USERNAME))
Order by Object_name (A.ID)
SELECT @Rowcount = @@ RowCount
IF @ rowcount = 0
Begin
SELECT @ rc = -3
Print @ Username 'Have Not Any Objects Authorization.'
Return @rc
end
end
Else
Begin
Select Object_name (A.ID) As Objectname,
User_name (a.uid) AS Usename,
Case B.issqlrole When 1 Then 'Group' Else 'User'
End as role,
Case A.ProtectType When 205 Ten 'Grant'
When 204 TEN 'GRANT'
When 206 TEN 'DENY'
Else 'Revoke'
End as protecttype,
Case a. [Action] when 26 Ten 'References'
When 178 TEN 'CREATE FUNCTION'
When 193 Ten 'SELECT'
When 195 TEN 'INSERT'
When 196 Ten 'delete'
When 197 TEN 'UPDATE'
When 198 Ten 'Create Table'
When 203 Then 'Create Database'
When 207 TEN 'CREATE VIEW'
When 222 Ten 'Create Procedure'
When 224 Ten 'Execute'
When 228 Ten 'Backup Database'
When 233 TEN 'CREATE DEFAULT'
When 235 Ten 'Backup Log'
When 236 TEN 'CREATE RULE'
ELSE '0'
End as [action],
User_name (a.grantor) AS Grantor
From sysprotects a inner join sysusers b on a.uid = B.UID
WHERE EXISTS (SELECT 1 from sysobjects
Where [name] = Object_name (A.ID) and xtype <> 's')
And A.uid = User_ID (@username)
Order by Object_name (A.ID)
SELECT @Rowcount = @@ RowCount
IF @ rowcount = 0
Begin
SELECT @ rc = -4
Print @ Username 'Have Not Any Objects Authorization.'
Return @rc
end
end
end
/ ************************************************** **********************************
***** /
/ * Judge the input parameters, if @Objectname is not null and @username IS
Null * /
/ * Then return One Objects Authorization * /
/ ************************************************** **********************************
***** /
IF @ rc = 0 and @Objectname is not null and @username is null
Begin
IF not exists (select * from sysobjects where [id] = Object_id (@objectname) and xtype <> 's')
Begin
SELECT @ rc = -5
Return @rc
end
IF @ rc = 0
Begin
Select Object_name (A.ID) As Objectname,
User_name (a.uid) AS Usename,
Case B.issqlrole when 1 Then 'Group'
Else 'User'
End as role,
Case A.ProtectType When 205 Ten 'Grant'
When 204 TEN 'GRANT'
When 206 TEN 'DENY'
Else 'Revoke'
End as protecttype,
Case a. [Action] when 26 Ten 'References'
When 178 TEN 'CREATE FUNCTION'
When 193 Ten 'SELECT'
When 195 TEN 'INSERT'
When 196 Ten 'delete'
When 197 TEN 'UPDATE'
When 198 Ten 'Create Table'
When 203 Then 'Create Database'
When 207 TEN 'CREATE VIEW'
When 222 Ten 'Create Procedure'
When 224 Ten 'Execute'
When 228 Ten 'Backup Database'
When 233 TEN 'CREATE DEFAULT'
When 235 Ten 'Backup Log'
When 236 TEN 'CREATE RULE'
ELSE '0'
End as [action],
User_name (a.grantor) AS Grantor
From sysprotects a inner join sysusers b on a.uid = B.UID
WHERE EXISTS (SELECT 1 from sysobjects
Where [name] = Object_name (A.ID) and xtype <> 's')
AND [ID] = Object_id (@Objectname)
Order by Object_name (A.ID)
SELECT @Rowcount = @@ RowCount
IF @ rowcount = 0
Begin
SELECT @ rc = -6
Print @ Objectname 'Have Not Grant Aut Autiotization To Any User'
Return @rc
end
end
end
/ ************************************************** **********************************
***** /
/ * Judge the input parameters, if @Objectname is not null and @username IS
NOT NULL * /
/ * Then return one Objects Authorization by one user * /
/ ************************************************** **********************************
***** /
IF @ rc = 0 and @Objectname is not null and @username is not null
Begin
If not exists (select * from sysobjects where [id] = Object_id (@Objectname)
And xtype <> s')
Begin
SELECT @ rc = -7
Print 'The Object Name Is Not Include in Sysobjects Table.'
Return @rc
end
IF not exists (select * from sysusers where [uid] = user_id (@username) and
STATUS <> 0)
Begin
SELECT @ rc = -8
Print 'The user name is not incrude in sysusers table.'
Return @rc
end
IF exists (SELECT 1 from sysmembers where [memberuid] = user_id (@username))
Begin
Select Object_name (A.ID) As Objectname,
User_name (a.uid) AS Usename,
Case B.issqlrole when 1 Then 'Group'
Else 'User'
End as role,
Case A.ProtectType When 205 Ten 'Grant'
When 204 TEN 'GRANT'
When 206 TEN 'DENY'
Else 'Revoke'
End as protecttype,
Case a. [Action] when 26 Ten 'References'
When 178 TEN 'CREATE FUNCTION'
When 193 Ten 'SELECT'
When 195 TEN 'INSERT'
When 196 Ten 'delete'
When 197 TEN 'UPDATE'
When 198 Ten 'Create Table'
When 203 Then 'Create Database'
When 207 TEN 'CREATE VIEW'
When 222 Ten 'Create Procedure'
When 224 Ten 'Execute'
When 228 Ten 'Backup Database'
When 233 TEN 'CREATE DEFAULT'
When 235 Ten 'Backup Log'
When 236 TEN 'CREATE RULE'
ELSE '0'
End as [action],
User_name (a.grantor) AS Grantor
From sysprotects a inner join sysusers b on a.uid = B.uidwhere EXISTS (SELECT 1 from sysobjects
Where [name] = Object_name (a. [id]) and xtype <> 's')
And (exists (select 1 from sysmembers
Where groupuid = a.UID and meid = user_id (@username))
OR A.UID = User_ID (@USERNAME))
AND [ID] = Object_id (@Objectname)
Order by Object_name (A.ID)
SELECT @Rowcount = @@ RowCount
IF @ rowcount = 0
Begin
SELECT @ rc = -9
Print @ Username 'Have Not Any Objects Authorization.'
Return @rc
end
end
Else
Begin
Select Object_name (A.ID) As Objectname,
User_name (a.uid) AS Usename,
Case B.issqlrole when 1 Then 'Group'
Else 'User'
End as role,
Case A.ProtectType When 205 Ten 'Grant'
When 204 TEN 'GRANT'
When 206 TEN 'DENY'
Else 'Revoke'
End as protecttype,
Case a. [Action] when 26 Ten 'References'
When 178 TEN 'CREATE FUNCTION'
When 193 Ten 'SELECT'
When 195 TEN 'INSERT'
When 196 Ten 'delete'
When 197 TEN 'UPDATE'
When 198 Ten 'Create Table'
When 203 Then 'Create Database'
When 207 TEN 'CREATE VIEW'
When 222 Ten 'Create Procedure'
When 224 Ten 'Execute'
When 228 Ten 'Backup Database'
When 233 TEN 'CREATE DEFAULT'
When 235 Ten 'Backup Log'
When 236 TEN 'CREATE RULE'
ELSE '0'
End as [action],
User_name (a.grantor) AS Grantor
From sysprotects a inner join sysusers b on a.uid = B.UID
WHERE EXISTS (SELECT 1 from sysobjects
Where [name] = Object_name (a. [id]) and xtype <> 's')
And A.uid = User_ID (@username)
AND [ID] = Object_ID (@Objectname) Order by Object_name (A.ID)
SELECT @Rowcount = @@ RowCount
IF @ rowcount = 0
Begin
SELECT @ rc = -10
Print @ Username 'Have Not Any Objects Authorization.'
Return @rc
end
end
end
end
Go
EXEC USP_GETOBJECTAUTHOR
