Group database roles into different permissions

Question

Group database roles into different permissions

mngong

SSCertifiable

Points: 6167
More actions
September 10, 2004 at 9:43 am

#60427

I will like to seperate the sp_dbfixedrolepermission into groups of dbfixedroles.
Is there a straightfoward way(command) to do that?? I did put
together a stored procedure but something tells me there is a
better way
TIA for your time
Mike

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

chrisleonard SSCertifiable Points: 5030 More actions · Answer 1

Hi Mike,

What are you trying to do exactly? If you mean "can we manipulate the individual permissions listed in sp_dbfixedrolepermission output and build our own roles using those permissions as building blocks" (something I've been asking MS for) they have told me that the answer is "no." For example, even though 'dbcc shrinkfile' is listed as a permission in the sp_dbfixedrolepermission output, you cannot grant it to another role.

If, on the other hand, you mean, "can we list only the permissions for a given fixed db role" then I suppose you know that you can pass in a fixed db role as a parameter, as in exec sp_dbfixedrolepermission db_ddladmin.

But somehow I bet I haven't understood what you want to do exactly. It might help to post your stored procedure so we could see from there what you're trying to do.

Cheers,

Chris

mngong SSCertifiable Points: 6167 More actions · Answer 2

Thanks a million for your reply...

In oracle Break on would perform such a function, this will list

all the different permissions but can group them by fixed roles

if you so ask

Hope this makes my intentions clearer

Michael

chrisleonard SSCertifiable Points: 5030 More actions · Answer 3

No, there isn't anything really similar to that in SQL Server. Of course, the BREAK statement is not an Oracle *server* command, but a SQL*Plus command. It tells SQL*Plus to format the output a certain way, but does not change the query run against Oracle.

In the SQL Server world, that kind of thing is usually left to a "real" report writing environment, like Crystal Reports, Reporting Services, Access, Business Objects, or whatever.

It's nice that SQL*Plus has such a feature, but to emulate it in SQL Server might not be worth the effort. I mean, you could do something like the following, but that would be a bit much, wouldn't it?

set nocount on
create table #perms (DbFixedRole sysname not null, Permission nvarchar(200) not null)

insert into #perms exec sp_dbfixedrolepermission

alter table #perms add PrimarySortCOde sysname null
go

update #perms set PrimarySortCode = DbFixedRole

alter table #perms add SecondarySortCode tinyint not null default 0
go

insert into #perms select distinct DbFixedRole, '', '', 1 from #perms

select DbFixedRole, Permission from #perms order by DbFixedRole, PrimarySortCode DESC, SecondarySortCode, Permission

select * from #perms
drop table #perms

So I say, the best way to do this is probably to throw together a quick-and-dirty report in Access. Or if this is the only query you need to do this for, you could just cut-and-paste into a word processor and format it the way you want it. Alternatively, you could try to write a generic SP to do in a generic way what the code snippet above does for a specific query.

Not the answer you were hoping for, probably, but it's about the best I can do for you.

Cheers,

Chris