April 29, 2010 at 1:47 am
Hi,
My manager wanted me to write a code that pulls out object level permissions in the following format:
Username, ObjectName, select(Y/N), update(Y/N), insert(Y/N), delete(Y/N), execute(Y/N).
i wrote the code using cursors but it's running very slow on machines having large number of objects or large number of users.
Can it be written more effeciently?
Here is the script
DECLARE @C_user VARCHAR(30)
DECLARE @C_user_name varchar(100)
DECLARE @C_table VARCHAR(30)
DECLARE @C_table_name VARCHAR(30)
DECLARE @SS VARCHAR(1000)
print 'UserName, ObjectName, Select, Update, Insert, Delete, Execute'
DECLARE User_Cursor CURSOR FOR
SELECT uid, name FROM sysusers where issqlrole=0 -- only users, no fixed db role
OPEN User_Cursor
FETCH NEXT FROM User_Cursor into @C_user, @C_user_name
WHILE @@FETCH_STATUS = 0
--set @SS=@C_user + ','
--print @i
BEGIN
DECLARE TABLE_CURSOR cursor FOR
SELECT id, name FROM sysobjects where xtype IN('u','v','p') - user table, views, SPs
OPEN Table_Cursor
FETCH NEXT FROM TABLE_CURSOR INTO @C_table, @C_table_name
WHILE @@FETCH_STATUS = 0
BEGIN
set @SS=@C_user_name + ',' + @C_table_name + ','
-- do something here
IF Exists (select * from sysprotects where id=@C_table and uid=@C_user and action=193) --select
set @SS=@SS + 'Y,'
else
set @SS=@SS + 'N,'
IF Exists (select * from sysprotects where id=@C_table and uid=@C_user and action=197) --update
set @SS=@SS + 'Y,'
else
set @SS=@SS+'N,'
IF Exists (select * from sysprotects where id=@C_table and uid=@C_user and action=195) --insert
set @SS=@SS + 'Y,'
else
set @SS=@SS+'N,'
IF Exists (select * from sysprotects where id=@C_table and uid=@C_user and action=196) --delete
set @SS=@SS + 'Y,'
else
set @SS=@SS+'N,'
IF Exists (select * from sysprotects where id=@C_table and uid=@C_user and action=224) --execute
set @SS=@SS + 'Y,'
else
set @SS=@SS+'N,'
FETCH NEXT FROM TABLE_CURSOR into @C_table, @C_table_name
print @SS
END
CLOSE TABLE_CURSOR
DEALLOCATE TABLE_CURSOR
FETCH NEXT FROM User_Cursor into @C_user, @C_user_name
END
CLOSE User_Cursor
DEALLOCATE User_Cursor
April 29, 2010 at 5:29 am
This does it without cursors.
SELECT [User] = su.name,
[Table] = so.name,
[select] = IsNull(max(case when sp.action = 193 then 'Y' else NULL end),'N'),
[update] = IsNull(max(case when sp.action = 197 then 'Y' else NULL end),'N'),
[insert] = IsNull(max(case when sp.action = 195 then 'Y' else NULL end),'N'),
[delete] = IsNull(max(case when sp.action = 196 then 'Y' else NULL end),'N'),
[execute] = IsNull(max(case when sp.action = 224 then 'Y' else NULL end),'N')
FROM sysprotects sp
JOIN sysobjects so
ON so.id = sp.id
JOIN sysusers su
ON su.uid = sp.uid
WHERE so.xtype in ('U', 'V', 'P')
AND su.issqlrole = 0
AND sp.action IN (193,195,196,197,224)
GROUP BY so.name, su.name
ORDER BY so.name, su.name
If you really need it in a comma-delimited format, it should be pretty easy from this to make that.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 29, 2010 at 5:48 am
Many thanks for the script Wayne. This took 5 ms on a database where as the earlier one took 1 min 17 secs.
April 29, 2010 at 9:46 am
ps. (4/29/2010)
Many thanks for the script Wayne. This took 5 ms on a database where as the earlier one took 1 min 17 secs.
Hmm, 77 secs / .005 sec = a performance improvement factor of 15400. Not too shabby.
Not surprised though... two nested cursors, and in the second one there was five selects going on.
Oh, and which one is easier to read and figure out what's going on?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 29, 2010 at 10:22 am
Your script is too neat and simple. and yeah, the performance improvement is massive. I'm little bad in writing complex queries. I'm waiting for my manager's comment on that. all credits to you 🙂
April 29, 2010 at 1:53 pm
Wayne, why the isnull() and max()?
Why not just case when sp.action = 193 then 'Y' else 'N' end?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
April 29, 2010 at 2:07 pm
jcrawf02 (4/29/2010)
Wayne, why the isnull() and max()?Why not just
case when sp.action = 193 then 'Y' else 'N' end?
I realized I could have dropped the IsNull and just put the 'N' in place of the NULL after I posted it, and didn't feel like editing it. But the Max is still needed for the grouping... otherwise it gives one row per permission (select, update, delete, insert, execute). He wanted them all on one row.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 29, 2010 at 2:18 pm
Gotcha, thanks! Learn something new every day...
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply