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

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 -

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 -