AMO Security Scripting Problem

Hi!

I have a little program that creates roles for a catalog and cubes. The roles is created fine and all the read permissions on the different cubes as well. Also adding different users to the roles is no problem.

Then when i look in BIDS( open analysis services database) everything looks exactly as it should. However, the different roles does not work for the users that are added through the script. But, if i manually add a user to one of the created roles, it works for that user. Anybody who knows what is going on?

The roles both have users and user groups in them.

You say "However, the different roles does not work for the users that are added through the script."

What script you are talking about? How do you generate the script? What doesnt work exactly?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||If you 'script' a role, the script just contains the names of the role members, NOT the permissions. Those permissions are stored in the cubes and dimensions themselves. So be carefull by changing permissions with scripts. It was not clear from your description if you just wanted o add users, or you created new roles as well. In the latter case, this can be an explanation, in the former case, you must have bumped onto another problem as well.|||

Hi!

This is basically what i do.

Role role = _oDb.Roles.Add(_sRoleID);

role.Members.Add(new RoleMember("domain\user");

role.Update();

Cube cube = _oDb.Cubes.FindByName(sCube);

if (cube != null)
{
CubePermission cubeperm = cube.CubePermissions.Add(_sRoleID);
cubeperm.Read = ReadAccess.Allowed;
cubeperm.ReadSourceData = ReadSourceDataAccess.Allowed;
cubeperm.Update();
}

So it′s not any advanced type of security, just read or not read a cube. If i then look at this role, by opening it with BIDS(opening the cube online, not a project), it looks fine. All the users are there and the read permissions and so on. But the users can′t access the cubes. If i then delete a user from a created role, and then add it again by hand, they can access the cube. So the role works, but it is like it does not like the programmatically added user names, even though they are exactly the same as when i add it by hand. And i don′t think it is all that different from this bol entry

http://msdn2.microsoft.com/en-us/library/ms345081.aspx

However, i just saw something in that entry that i missed. And it was the DatabasePermission which i don′t do anything with. It might be that, i will check. But that has Role as input so the deleting and adding a user by hand on a role should not affect that or BIDS does that under the covers or something?

|||

Full of shame and have a appointment whith the eye doctor.

It was the databasepermission that was missing. Thanks!

Alternative to sp_delete_Backuphistory

I have a SQL 2000 server that has 300 databases. There is a requirement tha
t
these databases have their logs backed up every 30 minutes. This creates a
huge number of entries in the backup/restore tables in the msdb database.
I know I can delete backup history with the sp_delete_backuphistory command.
However, this command takes forever. Even deleting 30 minutes worth of
backups takes 15 or more minutes. I find I am creating entries in the
backup/restore tables faster than I could possible delete them.
Is there an alteranate way to clear out the backup/restore tables? Could is
simply truncate the tables?
Thanks for any info.
LehrSJYes, I ran into the same problem when I discovered the backup tables in
msdb were HUGE... instead of running the sp, I just truncated. The only
thing you lose by doing this is the ability to use Enterprise Manager
of Mgmt Studio to do your restore, as it relies on the information in
those tables when suggesting which backups to apply. If you can afford
to lose this, no big deal. The information will just get repopulated on
your next backups.
Also, once the tables are empty, set up a nightly job to run the
sp_delete_backuphistory, keep as many days worth of history as you
specify.
LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement t
hat
> these databases have their logs backed up every 30 minutes. This creates
a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory comman
d.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could
is
> simply truncate the tables?
> Thanks for any info.
> --
> LehrSJ|||Yes, you can truncate the tables. See the system table map to see the
relationships btwn the backup tables as there are dependencies between
them. All you lose by truncating the tables is the ability to use EM or
Mgmt Studio to do a restore, as it relies on the information in those
tables when suggesting which backups to apply. If you can afford to
lose it, no big deal. The information just gets repopluated on your
next backup.
Meanwhile, you might want to set up a nightly job to run
sp_delete_backuphistory so the table growth is kept in check.
LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement t
hat
> these databases have their logs backed up every 30 minutes. This creates
a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory comman
d.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could
is
> simply truncate the tables?
> Thanks for any info.
> --
> LehrSJ|||LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement t
hat
> these databases have their logs backed up every 30 minutes. This creates
a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory comman
d.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could
is
> simply truncate the tables?
> Thanks for any info.
>
The problem is that there are no useful indexes on the backup history
tables. Create these indexes, then sp_delete_backuphistory will fly:
CREATE INDEX IDX_temp on restorefile ( restore_history_id )
CREATE INDEX IDX_temp on restorefilegroup ( restore_history_id )
CREATE INDEX IDX_temp on backupset ( backup_finish_date )
CREATE INDEX IDX_temp2 ON backupset ( media_set_id )
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you for this information. I didn't see why I couldn't truncate but I
was worried that there might be something that I didn't know about. I
understand what I will lose the ability for Enterprise manager to easily do
restores.
--
LehrSJ
"tootsuite@.gmail.com" wrote:

> Yes, you can truncate the tables. See the system table map to see the
> relationships btwn the backup tables as there are dependencies between
> them. All you lose by truncating the tables is the ability to use EM or
> Mgmt Studio to do a restore, as it relies on the information in those
> tables when suggesting which backups to apply. If you can afford to
> lose it, no big deal. The information just gets repopluated on your
> next backup.
> Meanwhile, you might want to set up a nightly job to run
> sp_delete_backuphistory so the table growth is kept in check.
> LehrSJ wrote:
>

Alternative to sp_delete_Backuphistory

I have a SQL 2000 server that has 300 databases. There is a requirement that
these databases have their logs backed up every 30 minutes. This creates a
huge number of entries in the backup/restore tables in the msdb database.
I know I can delete backup history with the sp_delete_backuphistory command.
However, this command takes forever. Even deleting 30 minutes worth of
backups takes 15 or more minutes. I find I am creating entries in the
backup/restore tables faster than I could possible delete them.
Is there an alteranate way to clear out the backup/restore tables? Could is
simply truncate the tables?
Thanks for any info.
LehrSJ
Yes, I ran into the same problem when I discovered the backup tables in
msdb were HUGE... instead of running the sp, I just truncated. The only
thing you lose by doing this is the ability to use Enterprise Manager
of Mgmt Studio to do your restore, as it relies on the information in
those tables when suggesting which backups to apply. If you can afford
to lose this, no big deal. The information will just get repopulated on
your next backups.
Also, once the tables are empty, set up a nightly job to run the
sp_delete_backuphistory, keep as many days worth of history as you
specify.
LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement that
> these databases have their logs backed up every 30 minutes. This creates a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory command.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could is
> simply truncate the tables?
> Thanks for any info.
> --
> LehrSJ
|||Yes, you can truncate the tables. See the system table map to see the
relationships btwn the backup tables as there are dependencies between
them. All you lose by truncating the tables is the ability to use EM or
Mgmt Studio to do a restore, as it relies on the information in those
tables when suggesting which backups to apply. If you can afford to
lose it, no big deal. The information just gets repopluated on your
next backup.
Meanwhile, you might want to set up a nightly job to run
sp_delete_backuphistory so the table growth is kept in check.
LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement that
> these databases have their logs backed up every 30 minutes. This creates a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory command.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could is
> simply truncate the tables?
> Thanks for any info.
> --
> LehrSJ
|||LehrSJ wrote:
> I have a SQL 2000 server that has 300 databases. There is a requirement that
> these databases have their logs backed up every 30 minutes. This creates a
> huge number of entries in the backup/restore tables in the msdb database.
> I know I can delete backup history with the sp_delete_backuphistory command.
> However, this command takes forever. Even deleting 30 minutes worth of
> backups takes 15 or more minutes. I find I am creating entries in the
> backup/restore tables faster than I could possible delete them.
> Is there an alteranate way to clear out the backup/restore tables? Could is
> simply truncate the tables?
> Thanks for any info.
>
The problem is that there are no useful indexes on the backup history
tables. Create these indexes, then sp_delete_backuphistory will fly:
CREATE INDEX IDX_temp on restorefile ( restore_history_id )
CREATE INDEX IDX_temp on restorefilegroup ( restore_history_id )
CREATE INDEX IDX_temp on backupset ( backup_finish_date )
CREATE INDEX IDX_temp2 ON backupset ( media_set_id )
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thank you for this information. I didn't see why I couldn't truncate but I
was worried that there might be something that I didn't know about. I
understand what I will lose the ability for Enterprise manager to easily do
restores.
LehrSJ
"tootsuite@.gmail.com" wrote:

> Yes, you can truncate the tables. See the system table map to see the
> relationships btwn the backup tables as there are dependencies between
> them. All you lose by truncating the tables is the ability to use EM or
> Mgmt Studio to do a restore, as it relies on the information in those
> tables when suggesting which backups to apply. If you can afford to
> lose it, no big deal. The information just gets repopluated on your
> next backup.
> Meanwhile, you might want to set up a nightly job to run
> sp_delete_backuphistory so the table growth is kept in check.
> LehrSJ wrote:
>