One of the niggles that you encounter when documenting your Deployment Server installation is the job permissions.
The only way to get all that valuable data is to navigate the job tree, examine all the permissions, and write them all down. Apart from the fact that this can take hours, mistakes can be made which kinda makes the whole exercise a bit dispiriting ;-)
Well, not any more. In this download, you'll find a T-SQL Script which will, with seconds, give you all permissions which have been configured across your Deployment Server job tree.
To get this working, all you have to do is,
- Download the script (Ennumerate_DS_Job_Permissions.sql_.txt) attached to this article to the SQL Server housing your Deployment Server eXpress database
- Strip the _.txt characters from the end of the file name, leaving just the .sql extension
- Double-click the file, or paste the contents into SQL Management Studio query window
- Click 'Execute' and wait for all those lovely permissions to drop out.
Not bad for a few seconds work. For those who want to know how it works so that they can modify it for their own needs read on....
Taking Apart the T-SQL
The core permissions for Deployment Server are stored in the securitypermissionlink table in the express database. Everytime you configure a security permission through the console, it gets stored here referencing the user object, and whether the permission is an allow or a deny.
There are two additional tables we therefore need to take into account when enumerating permissions -the securitygroup which stores users and groups and the securitypermission table which stores the lookup for permission IDs to their more human readable values.
So, the first T-SQL task of enumerating the permissions for Job and Folder database objects will use a cunning join of these three tables,
select sp.name 'Permission',sg.name 'User_Group', spl.object_id 'Object_id',allow
from securitypermissionlink spl
join securitypermission sp on spl.permission_id = sp.permission_id
join securitygroup sg on spl.user_id = sg.group_id
where Object_id between 1000001 and 3000000
Where I am only looking for Object_IDs between 1000001 and 3000000 as this is the ID range holding job and job folder objects.
At this point, we actually have all the data we need.1000001 But at the moment is isn't exactly human friendly as the folders and jobs are referenced by ID. What we really want is to know the full path to that object for the purposes of documentation.
This is the point where the SQL explodes giving the full T-SQL script attached to this article. What we need to do is store the table generated from the above SQL and scan through it line by line so that we can transform the folder or Job ID into a full path.
On way to do this is through SQL Cursors,
DECLARE JobCursor CURSOR FOR
select sp.name 'Permission',sg.name 'User_Group', spl.object_id 'Object_id',allow
from securitypermissionlink spl
join securitypermission sp on spl.permission_id = sp.permission_id
join securitygroup sg on spl.user_id = sg.group_id
where Object_id between 1000001 and 3000000
OPEN JobCursor
/* Grab first row from Cursor */
FETCH NEXT
FROM JobCursor
INTO @Permission,
@UserGroup,
@ObjectID,
@Allow
WHILE @@FETCH_STATUS=0
BEGIN
/* ......
WORK DONE ON EACH LINE RETURNED BY
CURSOR FETCH
......
*/
/* Fetch next row from cursor and loop while more rows exists */
FETCH NEXT
FROM JobCursor
INTO @Permission,
@UserGroup,
@ObjectID,
@Allow
END
/* Clean Up */
CLOSE JobCursor
DEALLOCATE JobCursor
So here the cursor is T-SQL select whose execution is delayed, and invoked row-by-row through the use of the FETCH NEXT statement which then inserts the resulting columns into variables. These variables can then be acted on (in my case I iterate up the object tree to establish the full patch of the folder of job which has permissions applied to). This programming is in the area I've dubbed out with "WORK DONE ON EACH LINE RETURNED BY THE FETCH".
The final piece is storing our refreshed data. I've done this by creating a new temporary table at the beginning of the script,
CREATE TABLE #JOBTABLE
( Permission varchar(1024),
UserGroup varchar(1024),
FullPath varchar(1024),
ObjectID INTEGER,
Allow INTEGER
)
where temporary tables exist only for the lifetime of SQL connection session and are prefixed with '#'.
As I iterate through each row of the cursor, I then insert into this table rows using the values extracted from the fetch,
INSERT INTO #JOBTABLE VALUES
(@Permission,@UserGroup,@PATH,@ObjectID,@Allow)
And finally, once the cursor is complete and I have my temporary table filled, I then display it.
select FullPath,UserGroup,Permission,Allow from #JOBTABLE where FullPath is not Null
order by FullPath,UserGroup
The ordering by FullPath and then UserGroup just provides a logic order to the output by presenting all permissions on each object in one continuous chunk, which is then ordered by the user/group for whom permission have been set.
You might ask (or might not...) why I am deliberately not displaying Null paths. After all, I shouldn't get a Null path should I? Well, yes, in the perfect world we would not need to declare this exception. However, sometimes the permissions table can get a bit dirty, and still reference objects which don't exist anymore. This 'not Null' condition just ensures you miss out such dirty entries.
Hope this was helpful,
Happy Christmas,
Ian./