Query on Database System Tables : permissions
-
Hi,
havent a clue how to do this, but I need a SELECT query that can list the following for each view, table, stored proc and function for a single database.
DBRole ... MyDbRole
ObjectName ...vwAuthors
ObjectType ..View or V
SELECT ... default(unchecked) or checked(Grant) or crossed(Deny)
INSERT ... default(unchecked) or checked(Grant) or crossed(Deny)
UPDATE ... default(unchecked) or checked(Grant) or crossed(Deny)
DELETE ... default(unchecked) or checked(Grant) or crossed(Deny)
EXEC ... default(unchecked) or checked(Grant) or crossed(Deny)
ie showing which one of the permissions is activated default(unchecked) or checked(Grant) or crossed(Deny)
Thanks in advance
-
From memory, I think there was a script in the script library that does that.
http://www.sqlservercentral.com/search/turbo.asp
--------------------
Colt 45 - the original point and click interface
-
I found this script named : ..."To compare object permissions with 2 databases"...it does what I ask except...it does not seam to list my USER tables. views and procs, only system views and tables and procs, why..
USE TEST
GO
select obj.name as 'OBJECT NAME',su.name as 'USER NAME',
'OBJECT TYPE' = CASE
WHEN OBJ.XTYPE = 'P' THEN 'PROCEDURE'
WHEN OBJ.XTYPE = 'V' THEN 'VIEW' --My Addtion
ELSE 'TABLE'
END,
'SELECT' = case when ( per.actadd&000001 > 0 AND obj.xtype = 'U') then '1'
else '0' end ,
'UPDATE' = case when ( per.actadd&000010 > 0 AND obj.xtype = 'U' ) then '1'
else '0' end ,
'DRI' = case when ( per.actadd&000100 > 0 AND obj.xtype = 'U') then '1'
else '0' end,
'INSERT' = case when ( per.actadd&001000 > 0 AND obj.xtype = 'U') then '1'
else '0' end ,
'DELETE' = case when ( per.actadd&010000 > 0 AND obj.xtype = 'U') then '1'
else '0' end,
'EXECUTE' = case when per.actadd&100000 > 0 then '1'
else '0' end
from sysobjects obj
join syspermissions per on obj.id = per.id
join sysusers su on per.grantee = su.uid
where xtype IN ('U','P','V') -- I added 'V'
go
-
Ok, as the query is referencing syspermissions using an INNER JOIN, if you haven't granted any permissions to objects then they're not listed. If you change the joins to outer joins you'll get all objects listed.
EG: from sysobjects obj LEFT join syspermissions per on obj.id = per.id LEFT join sysusers su on per.grantee = su.uid
You might need to play around with the query a bit more to eliminate NULLs and such.
--------------------
Colt 45 - the original point and click interface -
Thanks for help, worked great !
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply