Assigning 100 users to "browser" quickly

Does anyone know of a way to assign alot of users to role assignment browser
quickly?
Anything would helpOn Sep 20, 8:36 pm, Ryan <R...@.discussions.microsoft.com> wrote:
> Does anyone know of a way to assign alot of users to role assignment browser
> quickly?
> Anything would help
I've never seen this request before; but something like this might
work.
BULK INSERT REPORTSERVER.DBO.UsersTest
FROM 'C:\SomeFile.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK,
MAXERRORS = 10,
ERRORFILE = 'C:\Errors.txt')
In this example, the C:\SomeFile.csv needs to have the following
fields:
SomeUniqueSID, 1, 1, SomeUserName
Then import these records into the real users table, via something
like:
INSERT INTO REPORTSERVER.DBO.Users
SELECT NEWID(), SID, UserType, AuthType, UserName
FROM REPORTSERVER.DBO.UsersTest
DECLARE @.ROLEID VARCHAR(200);
SET @.ROLEID = (SELECT ROLEID FROM REPORTSERVER.DBO.ROLES WHERE
ROLENAME = 'BROWSER');
INSERT INTO REPORTSERVER.DBO.POLICYUSERROLE
SELECT NEWID() AS ID, @.ROLEID AS RoleID, U.UserID, P.PolicyID
FROM REPORTSERVER.DBO.USERS AS U
FULL OUTER JOIN REPORTSERVER.DBO.POLICIES AS P ON 1 = 1
WHERE U.USERNAME NOT IN ('EVERYONE', 'NT AUTHORITY\SYSTEM', 'BUILTIN
\ADMINISTRATORS');
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||My suggestion would be to put them all in the same AD group and just
set the AD group to have browser permissions.|||When you say AD group ( are you referring to Active Directory).
I am using forms authenciation ... but i how do i do that? Meaning how to i
but them in an a group
"midas" wrote:
> My suggestion would be to put them all in the same AD group and just
> set the AD group to have browser permissions.
>|||Thanks Enrique, let me try this
"EMartinez" wrote:
> On Sep 20, 8:36 pm, Ryan <R...@.discussions.microsoft.com> wrote:
> > Does anyone know of a way to assign alot of users to role assignment browser
> > quickly?
> >
> > Anything would help
>
> I've never seen this request before; but something like this might
> work.
> BULK INSERT REPORTSERVER.DBO.UsersTest
> FROM 'C:\SomeFile.csv'
> WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK,
> MAXERRORS = 10,
> ERRORFILE = 'C:\Errors.txt')
> In this example, the C:\SomeFile.csv needs to have the following
> fields:
> SomeUniqueSID, 1, 1, SomeUserName
> Then import these records into the real users table, via something
> like:
> INSERT INTO REPORTSERVER.DBO.Users
> SELECT NEWID(), SID, UserType, AuthType, UserName
> FROM REPORTSERVER.DBO.UsersTest
> DECLARE @.ROLEID VARCHAR(200);
> SET @.ROLEID = (SELECT ROLEID FROM REPORTSERVER.DBO.ROLES WHERE
> ROLENAME = 'BROWSER');
> INSERT INTO REPORTSERVER.DBO.POLICYUSERROLE
> SELECT NEWID() AS ID, @.ROLEID AS RoleID, U.UserID, P.PolicyID
> FROM REPORTSERVER.DBO.USERS AS U
> FULL OUTER JOIN REPORTSERVER.DBO.POLICIES AS P ON 1 = 1
> WHERE U.USERNAME NOT IN ('EVERYONE', 'NT AUTHORITY\SYSTEM', 'BUILTIN
> \ADMINISTRATORS');
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||I can't do that because I can't use AD. I am using forms authenication with
an LDAP authenication... However my LDAP is not an AD
"Ryan" wrote:
> When you say AD group ( are you referring to Active Directory).
> I am using forms authenciation ... but i how do i do that? Meaning how to i
> but them in an a group
> "midas" wrote:
> > My suggestion would be to put them all in the same AD group and just
> > set the AD group to have browser permissions.
> >
> >|||On Sep 22, 6:46 am, Ryan <R...@.discussions.microsoft.com> wrote:
> Thanks Enrique, let me try this
> "EMartinez" wrote:
> > On Sep 20, 8:36 pm, Ryan <R...@.discussions.microsoft.com> wrote:
> > > Does anyone know of a way to assign alot of users to role assignment browser
> > > quickly?
> > > Anything would help
> > I've never seen this request before; but something like this might
> > work.
> > BULK INSERT REPORTSERVER.DBO.UsersTest
> > FROM 'C:\SomeFile.csv'
> > WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK,
> > MAXERRORS = 10,
> > ERRORFILE = 'C:\Errors.txt')
> > In this example, the C:\SomeFile.csv needs to have the following
> > fields:
> > SomeUniqueSID, 1, 1, SomeUserName
> > Then import these records into the real users table, via something
> > like:
> > INSERT INTO REPORTSERVER.DBO.Users
> > SELECT NEWID(), SID, UserType, AuthType, UserName
> > FROM REPORTSERVER.DBO.UsersTest
> > DECLARE @.ROLEID VARCHAR(200);
> > SET @.ROLEID = (SELECT ROLEID FROM REPORTSERVER.DBO.ROLES WHERE
> > ROLENAME = 'BROWSER');
> > INSERT INTO REPORTSERVER.DBO.POLICYUSERROLE
> > SELECT NEWID() AS ID, @.ROLEID AS RoleID, U.UserID, P.PolicyID
> > FROM REPORTSERVER.DBO.USERS AS U
> > FULL OUTER JOIN REPORTSERVER.DBO.POLICIES AS P ON 1 = 1
> > WHERE U.USERNAME NOT IN ('EVERYONE', 'NT AUTHORITY\SYSTEM', 'BUILTIN
> > \ADMINISTRATORS');
> > Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
Have you had a chance to try my suggestion?
Enrique Martinez
Sr. Software Consultant|||On Sep 24, 8:58 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Sep 22, 6:46 am, Ryan <R...@.discussions.microsoft.com> wrote:
>
>
> > Thanks Enrique, let me try this
> > "EMartinez" wrote:
> > > On Sep 20, 8:36 pm, Ryan <R...@.discussions.microsoft.com> wrote:
> > > > Does anyone know of a way to assign alot of users to role assignment browser
> > > > quickly?
> > > > Anything would help
> > > I've never seen this request before; but something like this might
> > > work.
> > > BULK INSERT REPORTSERVER.DBO.UsersTest
> > > FROM 'C:\SomeFile.csv'
> > > WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK,
> > > MAXERRORS = 10,
> > > ERRORFILE = 'C:\Errors.txt')
> > > In this example, the C:\SomeFile.csv needs to have the following
> > > fields:
> > > SomeUniqueSID, 1, 1, SomeUserName
> > > Then import these records into the real users table, via something
> > > like:
> > > INSERT INTO REPORTSERVER.DBO.Users
> > > SELECT NEWID(), SID, UserType, AuthType, UserName
> > > FROM REPORTSERVER.DBO.UsersTest
> > > DECLARE @.ROLEID VARCHAR(200);
> > > SET @.ROLEID = (SELECT ROLEID FROM REPORTSERVER.DBO.ROLES WHERE
> > > ROLENAME = 'BROWSER');
> > > INSERT INTO REPORTSERVER.DBO.POLICYUSERROLE
> > > SELECT NEWID() AS ID, @.ROLEID AS RoleID, U.UserID, P.PolicyID
> > > FROM REPORTSERVER.DBO.USERS AS U
> > > FULL OUTER JOIN REPORTSERVER.DBO.POLICIES AS P ON 1 = 1
> > > WHERE U.USERNAME NOT IN ('EVERYONE', 'NT AUTHORITY\SYSTEM', 'BUILTIN
> > > \ADMINISTRATORS');
> > > Hope this helps.
> > > Regards,
> > > Enrique Martinez
> > > Sr. Software Consultant
> Have you had a chance to try my suggestion?
> Enrique Martinez
> Sr. Software Consultant- Hide quoted text -
> - Show quoted text -
yeah, i am having one issue. I was able to get the users into the user
table by doing this
insert into ReportServer.dbo.Users
select userid = newid(), [sid]= NULL, usertype = 1,authtype = 1,
[Login]
from tbldataAccess a
where a.login COLLATE Latin1_General_CI_AS_KS_WS not in (select
username from ReportServer.dbo.Users)
but i can get it to populate to policyuserrole
Insert into reportserver.dbo.policyuserrole
select newid() as ID, RoleID = (select roleid from
Reportserver.dbo.roles where roleName = 'Browser' ), u.userID,
p.PolicyID
from reportserver.dbo.users as u full outer join
reportserver.dbo.policies as p on 1=1
where u.username not in ('Everyone', 'NY Authority\System', 'Builtin
\Administrators') or u.userid not in (select userid from
reportserver.dbo.policies)
I get the following error:
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.policyuserrole' with
unique index 'IX_PolicyUserRole'.
The statement has been terminated.
Any ideas|||I am about to add people to the reportserver names. but when they
login it says that they don't have the correct permissions. If i got
into report manager and add them as browser they have access. Any
ideas
On Oct 9, 12:30 pm, Ryan Swann <swannr...@.gmail.com> wrote:
> On Sep 24, 8:58 pm, EMartinez <emartinez...@.gmail.com> wrote:
>
>
> > On Sep 22, 6:46 am, Ryan <R...@.discussions.microsoft.com> wrote:
> > > Thanks Enrique, let me try this
> > > "EMartinez" wrote:
> > > > On Sep 20, 8:36 pm, Ryan <R...@.discussions.microsoft.com> wrote:
> > > > > Does anyone know of a way to assign alot ofusersto role assignment browser
> > > > > quickly?
> > > > > Anything would help
> > > > I've never seen this request before; but something like this might
> > > > work.
> > > > BULK INSERT REPORTSERVER.DBO.UsersTest
> > > > FROM 'C:\SomeFile.csv'
> > > > WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', TABLOCK,
> > > > MAXERRORS = 10,
> > > > ERRORFILE = 'C:\Errors.txt')
> > > > In this example, the C:\SomeFile.csv needs to have the following
> > > > fields:
> > > > SomeUniqueSID, 1, 1, SomeUserName
> > > > Then import these records into the realuserstable, via something
> > > > like:
> > > > INSERT INTO REPORTSERVER.DBO.Users
> > > > SELECT NEWID(), SID, UserType, AuthType, UserName
> > > > FROM REPORTSERVER.DBO.UsersTest
> > > > DECLARE @.ROLEID VARCHAR(200);
> > > > SET @.ROLEID = (SELECT ROLEID FROM REPORTSERVER.DBO.ROLES WHERE
> > > > ROLENAME = 'BROWSER');
> > > > INSERT INTO REPORTSERVER.DBO.POLICYUSERROLE
> > > > SELECT NEWID() AS ID, @.ROLEID AS RoleID, U.UserID, P.PolicyID
> > > > FROM REPORTSERVER.DBO.USERSAS U
> > > > FULL OUTER JOIN REPORTSERVER.DBO.POLICIES AS P ON 1 = 1
> > > > WHERE U.USERNAME NOT IN ('EVERYONE', 'NT AUTHORITY\SYSTEM', 'BUILTIN
> > > > \ADMINISTRATORS');
> > > > Hope this helps.
> > > > Regards,
> > > > Enrique Martinez
> > > > Sr. Software Consultant
> > Have you had a chance to try my suggestion?
> > Enrique Martinez
> > Sr. Software Consultant- Hide quoted text -
> > - Show quoted text -
> yeah, i am having one issue. I was able to get theusersinto the user
> table by doing this
> insert into ReportServer.dbo.Users
> select userid = newid(), [sid]= NULL, usertype = 1,authtype = 1,
> [Login]
> from tbldataAccess a
> where a.login COLLATE Latin1_General_CI_AS_KS_WS not in (select
> username from ReportServer.dbo.Users)
> but i can get it to populate to policyuserrole
> Insert into reportserver.dbo.policyuserrole
> select newid() as ID, RoleID = (select roleid from
> Reportserver.dbo.roles where roleName = 'Browser' ), u.userID,
> p.PolicyID
> from reportserver.dbo.usersas u full outer join
> reportserver.dbo.policies as p on 1=1
> where u.username not in ('Everyone', 'NY Authority\System', 'Builtin
> \Administrators') or u.userid not in (select userid from
> reportserver.dbo.policies)
> I get the following error:
> Msg 2601, Level 14, State 1, Line 1
> Cannot insert duplicate key row in object 'dbo.policyuserrole' with
> unique index 'IX_PolicyUserRole'.
> The statement has been terminated.
> Any ideas- Hide quoted text -
> - Show quoted text -