script db object permissions

I'm looking into processes to move to using database specific roles and migrating specifically assigned user permissions to these roles. I have made progress on listing permissions in the public role and assigning these same permissions to the new role that I have created for the given database. However, when I inspect permissions on tables and other objects, permissions have also been specifically granted for application users in the db. My ojective is to script out the permissions against the database objects (mainly user tables) and then use the output to assign the same permissions to the appropriate role instead. However, I'm having difficulty finding which system table stores the table and column level permissions.

If I do select * from sysobjects, I can see the user tables. However, I can't find the related tables that store user object permissions. For example, sysprotects doesn't store this info and, if I do an inner join on 'id' between these two tables, I don't see any user tables. Where do I look? Are there any useful 'already done' scripts/procs around for these purposes?

Regards,

CliveTry this one..
sp_helprotect
sskris|||well
i dont have an sql server with me now, but you could create a query between the sysobjects, sysusers, and sysprotects table and you could interpret the action and the protecttype columns
[Books Online] sysusers
[Books Online] sysobjects
[Books Online] sysprotects

if you want a quick way to view system tables in a graphical format you should download systbl.chm (http://download.microsoft.com/download/SQLSVR2000/sysmap/2000/WIN98MeXP/EN-US/systbl.chm) which is also available in your sql server 2000 resource kit.|||/*
This script creates a view to display users and objects that they have permissions for
and the permissions that are set
*/

use master
go
Create View VUserRights
as
SELECT top 100 percent
U.[Name] as UName
,O.Name as OName
,case xtype
when 'S' then 'System Table'
when 'P' then 'Stored Procedure'
when 'C' then 'Check Constraint'
when 'D' then 'Default'
when 'F' then 'Foreign Key'
when 'L' then 'Log'
when 'FN' then 'Scalar Function'
when 'IF' then 'Inlined Table-Function'
when 'PK' then 'PRIMARY KEY'
when 'RF' then 'Replication Filter Stored Procedure'
when 'S' then 'System Table'
when 'TF' then 'Table Function'
when 'TR' then 'Trigger'
when 'U' then 'User Table'
when 'UQ' then 'UNIQUE Constraint'
when 'V' then 'View'
when 'X' then 'Extended Stored Procedure'
else cast(xtype as varchar(30))
end as XType

,Case p.[action]
When 26 then 'REFERENCES'
When 178 then 'CREATE FUNCTION'
When 193 then 'SELECT'
When 195 then 'INSERT'
When 196 then 'DELETE'
When 197 then 'UPDATE'
When 198 then 'CREATE TABLE'
When 203 then 'CREATE DATABASE'
When 207 then 'CREATE VIEW'
When 222 then 'CREATE PROCEDURE'
When 224 then 'EXECUTE'
When 228 then 'BACKUP DATABASE'
When 233 then 'CREATE DEFAULT'
When 235 then 'BACKUP LOG'
When 236 then 'CREATE RULE'
Else cast([Action] as varchar(20))
End as 'Action'

,Case p.protecttype
When 204 Then 'GRANT_W_GRANT'
When 205 Then 'GRANT'
When 206 Then 'REVOKE'
Else cast(protecttype as varchar(20))
end as ProtectType

FROM sysusers U join sysprotects P
on u.uid = P.uid
Join sysobjects O
on P.id = O.id
where xtype <>'s'
order by U.uid ASC, O.xtype Desc

/*
Here are some calling statements
--2 is an oracle trick that i learned to
create a permissions assignment statement from exisiting metadata
*/

GO
--1
select * from vuserrights
Go
--2
select Protecttype + ' ' +
Action + ' ON ' +
Oname
-- +'('+ Xtype+')'
+ ' TO ' + Uname from vuserRights|||Thank you for the info. Nice View by the way.

I ended up writing a script that used sp_helprotect. It dumps out the permissions for a given user to a temp table and then I cursor through the temp table to 'grant' the permissions to a new role. eg. give me the Public permissions that have been granted against objects in this db and copy them to a new db role. Also, I can revoke the permsissions on Public as an option. So, I simply do:-

set @.GrantSQL ='Grant ' + @.action + ' on [' + @.Obj + '] to ' + @.Role
exec(@.GrantSQL)

However, although the above works fine on the db's I'm working on, I suspect that it wouldn't work with regard to database that have column level permssions. I tried setting a column level permission to see how my script would handle it. sp_helprotect reports the column level permssion but I haven't got around to modifiying my script to deal with it properly - currently it migrates the column level permssion in the source user/role to the target role as a table level permisson. In the loop that cursors through the temp table containing the sp_helprotect output, I presumably need to detect a column level permssion and branch to execute a grant statement that will apply a column level permission? Haven't had time to research this yet.

Thanks again,

Clive|||If you are comfortable with VB or VC, then SQL-DMO (http://msdn.microsoft.com/library/en-us/sqldmo/dmoref_con01_2yi7.asp) offers exactly what you want.

-PatP|||Pat,

I am comfortable with VB but haven't doen anything with dmo yet. Got any examples?

Cheers,

Clive|||Just follow the link. There are zillions of examples scattered on the appropriate pages.

-PatP|||Zillions ?
it's more like a Quintillion.|||Zillions ?
it's more like a Quintillion.Ok, ok, ok... So what's a few zeros here and there between friends, eh? ;)

-PatP

Scoped calulated measures for unrelated dimensions

Hi,

I am not sure if this is a correct way to design this,

Here is my scenario:

We are migrating an existing databwarehouse application from vb, which used to pull out records using the criteria provided in the input screen. The input conditions are many and multiple selections and combinations constitute the final data that is returned in excel format.

Most of the input conditions specified are filters or slicing of data, which we have modelled as dimensions, and they were existing tables in the database. other filters for dicing the measures which is modelled as the fact were also existing tables.

There are some input conditions that are not dimensions at all, meaning there are no tables which exists for them.

One for example, a collection of 4 values (A,B,C,D) , when A is selected the output excel comes in a paticular format, with calculations specific to a paticular region. and so on for B,C,D.etc

1. Now if I model this as a dimension in ssas 2005, I dont have related fact data for this. what should I do?

2. Anyway , I have created a dimension in ssas 2005 that contains this data. I now want to create calculations (either named or calc measures) that dynamically have formula x+y if the browsed value/queried value of the unrelated dimension is A and a different formula if the viewed dimensiion value is B,etc

meaning I want to have a dynamic calculated measure that changes its formula based on the unrelated dimension that is used in the query's row or column

Is this possible? is this the correct way to model this problem?

Regards

Yes, it is possible. The very related example how to do it - is the Time Intelligence Wizard. When you run it, it modifies DSV (you don't even need to create artificial table for A,B,C and D !) to add such factless attribute members, and then in MDX Script it assigns to them formulas. I.e., you MDX Script would look something like

ArtificialAttribute.A = formulaforA;

ArtificialAttribute.B = formulaforY;

...

etc.

|||

On this note, can I ask you a question.
Isn't using time intelligence an expensive operation during querying?

Rgds

Hari

|||

Sorry, but this question isn't posed in a way that it is possible to answer. It's like asking "Isn't running SQL queries expensive ?". And the answer to this question is also unrelated to the original problem.