May 12, 2008 at 11:30 am
I have the following script.. I need to incorporate things such as "Create Database/Table/View...etc". Right now, it's simple pulling privs on objects. Very similar to help protect.
The issue is, I need to pull permissions in a script very similar to sp_helprotect b/c it seems to be broken and simple need a script to replace the SP.
Any suggestions...?
select
sysusers.name as username, sysobjects.name as objectname, sysobjects.xtype as ObjectType,
CASE WHEN sysprotects_1.action is null THEN CASE WHEN sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'Select' END as 'SELECT',
CASE WHEN sysprotects_2.action is null THEN CASE WHEN sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'INSERT' END as 'INSERT',
CASE WHEN sysprotects_3.action is null THEN CASE WHEN sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'UPDATE' END as 'UPDATE',
CASE WHEN sysprotects_4.action is null THEN CASE WHEN sysobjects.xtype = 'P' THEN 'N/A' ELSE 'No' END ELSE 'DELETE' END as 'DELETE',
CASE WHEN sysprotects_5.action is null THEN CASE WHEN sysobjects.xtype = 'U' THEN 'N/A' ELSE 'No' END ELSE 'EXECUTE' END as 'EXECUTE'
from
sysusers
full join sysobjects on ( sysobjects.xtype in ( 'P', 'U', 'X', 'V' ) and sysobjects.Name NOT LIKE 'dt%' )
left join sysprotects as sysprotects_1
on sysprotects_1.uid = sysusers.uid and sysprotects_1.id = sysobjects.id and sysprotects_1.action = 193 and sysprotects_1.protecttype in ( 204, 205 )
left join sysprotects as sysprotects_2
on sysprotects_2.uid = sysusers.uid and sysprotects_2.id = sysobjects.id and sysprotects_2.action = 195 and sysprotects_2.protecttype in ( 204, 205 )
left join sysprotects as sysprotects_3
on sysprotects_3.uid = sysusers.uid and sysprotects_3.id = sysobjects.id and sysprotects_3.action = 197 and sysprotects_3.protecttype in ( 204, 205 )
left join sysprotects as sysprotects_4
on sysprotects_4.uid = sysusers.uid and sysprotects_4.id = sysobjects.id and sysprotects_4.action = 196 and sysprotects_4.protecttype in ( 204, 205 )
left join sysprotects as sysprotects_5
on sysprotects_5.uid = sysusers.uid and sysprotects_5.id = sysobjects.id and sysprotects_5.action = 224 and sysprotects_5.protecttype in ( 204, 205 )
where
(sysprotects_1.action is not null or sysprotects_2.action is not null or
sysprotects_3.action is not null or sysprotects_4.action is not null or
sysprotects_5.action is not null)
order by
sysusers.name, sysobjects.name
May 12, 2008 at 11:59 am
Sorry Topher, I cannot figure what you are actually asking for here.
IS your script broken and you want help with it? If so, please tell us what is wrong with it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2008 at 12:03 pm
If I could take a stab here, I don't think he/she is saying that script they posted is broken - I went through a similar undertaking recently, and script will return each user/object and the SELECT, INSERT, UPDATE, DELETE, EXECUTE permissions associated, but would need it expanded further to identify any user with the CREATE DATABASE permission, the CREATE TABLE permission, CREATE PROC permission, etc...
In my endeavor, we were unable to come up with a place to look for that type of permissions information... if any one can solve that, I'd love to see the solution myself...
"Got no time for the jibba jabba!"
-B.A. Baracus
May 12, 2008 at 12:04 pm
Kind of. It simple needs additional info. Or, simple a way to capture more data.
But, if you know a script that is exactly the same as sp_helprotect, that would work too :).
But, I need to capture additional things like "Create Database". I'm simple looking to mirror sp_helprotect's output from a script....
For example, if I look at a user acct, I see 'grant select on object1', 'create table'. Right now, my script is only grabbing 'grant select on object1'
Thx!
May 12, 2008 at 12:06 pm
I can see where the info is coming from somewhat..but I'm failing to see a way to tie in everything to display results.
select distinct b.name, a.action
from sysprotects a, sysusers b
where (a.action = 198
or a.action = 203
or a.action = 207
or a.action = 222
or a.action = 228
or a.action = 223
or a.action = 235
or a.action = 236)
and b.islogin = 1
order by
b.name
26 = REFERENCES
193 = SELECT
195 = INSERT
196 = DELETE
197 = UPDATE
198 = CREATE TABLE
203 = CREATE DATABASE
207 = CREATE VIEW
222 = CREATE PROCEDURE
224 = EXECUTE
228 = BACKUP DATABASE
233 = CREATE DEFAULT
235 = BACKUP LOG
236 = CREATE RULE
May 12, 2008 at 12:25 pm
did you have a look at "http://www.sqlservercentral.com/scripts/Miscellaneous/30268/ ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 12, 2008 at 12:35 pm
Auhutens, to answer your Question
select b.name,a.action, category =
CASE a.action
WHEN '198' then 'Create Table'
WHEN '203' then 'Create Database'
WHEN '207' then 'Create View'
WHEN '222' then 'Create Procedure'
WHEN '228' then 'Backup Database'
WHEN '223' then 'Create Default'
WHEN '235' then 'Backup Log'
WHEN '236' then 'Create Rule'
End
from sysprotects a, sysusers b
where (a.action = 198
or a.action = 203
or a.action = 207
or a.action = 222
or a.action = 228
or a.action = 223
or a.action = 235
or a.action = 236)
and b.islogin = 1
order by
b.name
May 12, 2008 at 12:38 pm
Thanks, that'll help out
"Got no time for the jibba jabba!"
-B.A. Baracus
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply