Creating SQL database "Users" pulled from Active directory OU

Is there a tool, or does anyone have a script format that might automaticall
y
pull in AD accounts from an OU and put them in the Users group of a specific
database.
If there is an article or existing document on this I have not been able to
find it.
I would appreciate any help anyone can give whereas if I can't find this, I
will have to type in about 2000 users into my database from a hard copy.
There has to be a way. Even if someone can tell me what table and field the
users are listed in, I can probably script it from that.
Thanks in advance
gordonJust so I understand, when you create a database, you want SQL to
automatically pull in users AD and grant them access to the database?
"gordon" wrote:

> Is there a tool, or does anyone have a script format that might automatica
lly
> pull in AD accounts from an OU and put them in the Users group of a specif
ic
> database.
> If there is an article or existing document on this I have not been able t
o
> find it.
> I would appreciate any help anyone can give whereas if I can't find this,
I
> will have to type in about 2000 users into my database from a hard copy.
> There has to be a way. Even if someone can tell me what table and field t
he
> users are listed in, I can probably script it from that.
> Thanks in advance
> gordon|||Yes. Actually the database is already created. Right now, I have to
manually add every user in an AD OU to the "Users" group in SQL 2000. Then
I
have to assign them either Public or another role, manually. As you might
imagine, this is very time consuming. I have seen ADSI scripts pull
information from OU's in AD to tables in SQL, but not to the USERS group (yo
u
know, where you have diagrams, views, tables, users, roles etc. ) I want to
add the AD user to the USers in SQL.
Thanks for the response!!!!
"John Barr" wrote:
> Just so I understand, when you create a database, you want SQL to
> automatically pull in users AD and grant them access to the database?
> "gordon" wrote:
>|||I understand, and yes, it is possible, but it will taking some coding.You
will have to create a VBScript to read AD based on an OU name that you pass
and access SQL Server to execute a stored procedure to do dynamic SQL to add
the user if it is not existing, and grant it the rights you will need. It
will be time consuming to create, but it will eliminate the manual addtions.
You can get the VBScript to access AD in the Script Center on Microsofts
Site, and write those users into a table using ADO, then execute a Stored
Procedure. Seems a little hectic, but it would work.
"gordon" wrote:
> Yes. Actually the database is already created. Right now, I have to
> manually add every user in an AD OU to the "Users" group in SQL 2000. The
n I
> have to assign them either Public or another role, manually. As you might
> imagine, this is very time consuming. I have seen ADSI scripts pull
> information from OU's in AD to tables in SQL, but not to the USERS group (
you
> know, where you have diagrams, views, tables, users, roles etc. ) I want t
o
> add the AD user to the USers in SQL.
> Thanks for the response!!!!
>
> "John Barr" wrote:
>|||I appreciate it. I knew it would be that much work, was wondering ifanyone
had done it and I could use their code and modify it for my OU's. Or if
there were any Microsoft Technical articles showing how.
Thanks for the responses.
"John Barr" wrote:
> I understand, and yes, it is possible, but it will taking some coding.You
> will have to create a VBScript to read AD based on an OU name that you pas
s
> and access SQL Server to execute a stored procedure to do dynamic SQL to a
dd
> the user if it is not existing, and grant it the rights you will need. It
> will be time consuming to create, but it will eliminate the manual addtion
s.
> You can get the VBScript to access AD in the Script Center on Microsofts
> Site, and write those users into a table using ADO, then execute a Stored
> Procedure. Seems a little hectic, but it would work.
> "gordon" wrote:
>|||Hi
What are you not using roles and permissioning via AD group membership? It
is the recommenced way to manage users in SQL Server.
Add the users in AD to a group, then add the group the SQL Server. Then when
a new user arrives, you just add the person to the AD group and then the
user has the permission in the DB. When the user leaves, there is no
maintenance to be done at SQL Server level, only at AD level.
Books online has a lot of information DB roles and AD group membership.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"gordon" <gordon@.discussions.microsoft.com> wrote in message
news:03540F8D-A377-4207-8093-4DA0F6225D81@.microsoft.com...
> Yes. Actually the database is already created. Right now, I have to
> manually add every user in an AD OU to the "Users" group in SQL 2000.
> Then I
> have to assign them either Public or another role, manually. As you might
> imagine, this is very time consuming. I have seen ADSI scripts pull
> information from OU's in AD to tables in SQL, but not to the USERS group
> (you
> know, where you have diagrams, views, tables, users, roles etc. ) I want
> to
> add the AD user to the USers in SQL.
> Thanks for the response!!!!
>
> "John Barr" wrote:
>

Creating Resolution Rate Report

I am creating a report that will show the resolution rate for our helpdesk.
I would like to create something that says if this group opened it and they
closed it then count that request as one that the helpdesk closed. But If the
helpdesk opened it and did not close it then count the request as escalated.
And maybe show this in a graph or something
I have 3 tables. act_log, call_req, and ctct
The call_Req is all the tickets that are put into the system
The act_log is houses all events that happen with the ticket. Say for
example the ticket was open, then the ticket was closed.
The ctct table is a list of all the contacts within the system.
I think I would key this off of the act_log table since this says who opened
the request and who closed the request. But I am not forsure who to go about
this in MRS.
go here for a screenshot of the tables and relationships
http://www.nkyapartments.com/temp/resrate.jpg
Can someone helpme step through this? This is a important report I need to
finish and my skills are not to that level yet.
Thanks
EricThis is interesting. use the join you have outlined then filter by log
event. Only bring in the events for opening and closing of the ticket. Only
bring in events for the help desk. The report you are looking for is a
matrix. The column will be the event type. this should just give you open
and close for columns. The row grouping will be the helpdesk rep. The data
will be ticket number and you want to do a count of these. It will give you
a table that looks like this.
OP CL
Rep1 9 7
Rep2 5 5
Rep4 10 8
From here you should be able to build a graph to show closure rate by rep
and overall closure rate.
"Eric Lovelace" wrote:
> I am creating a report that will show the resolution rate for our helpdesk.
> I would like to create something that says if this group opened it and they
> closed it then count that request as one that the helpdesk closed. But If the
> helpdesk opened it and did not close it then count the request as escalated.
> And maybe show this in a graph or something
>
> I have 3 tables. act_log, call_req, and ctct
> The call_Req is all the tickets that are put into the system
> The act_log is houses all events that happen with the ticket. Say for
> example the ticket was open, then the ticket was closed.
> The ctct table is a list of all the contacts within the system.
> I think I would key this off of the act_log table since this says who opened
> the request and who closed the request. But I am not forsure who to go about
> this in MRS.
> go here for a screenshot of the tables and relationships
> http://www.nkyapartments.com/temp/resrate.jpg
> Can someone helpme step through this? This is a important report I need to
> finish and my skills are not to that level yet.
>
> Thanks
> Eric

Creating Report Server 2005 Report Totals

I have a table in my report. In it, I have 2 headers, a group and 1 footer at the bottom.

How do I sum my columns in the footer?

so nobody has tried adding totals in a table before or what?|||Uhm. Why don't you try
=sum(Fields!MyFieldToSum.Value)
typed in the footer field? Or do I miss something here?

Creating Parent/Child table with Sum for each group

Hi,
i've been working on this report for the whole day now and have come
to the conclusion that this can't be accieved with Reporting Services
SP1.
Or maybe i'm wrong...
I'm trying to display a report like this:
-- Group 1
-- SubGroup 1,1
-- SubGroup 1,2
-- SubGroup 1,3
-- SubSubGroup 3,1
-- SubSubSubGroup 3,1,1
-- Totals for SubSubSubGroup 3,1,1
-- Total for SubSubGroup 3,1
-- Totals for SubGroup 1,3
-- Total for Group 1
My data looks like this:
Text, Amount, Group, ParentGroup
I have done it like described here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_structure_objects_v1_3cok.asp
The problem is getting the Total to display correctly. I have tried
adding a table footer but this will only display the Total for the
entire table and not for each group.
Anybody know a solution to making this kind of report?
-MartinI had a similar problem. In the end I got around the problem by
creating a query that returned the hierarchised sub totals in the
dataset...this is relatively easy if your datasource is OLAP -
admittedly not so ideal if you are using relational datasources ...|||I found a way to work around it, and almost getting the desired result.
The result looks like this:
-- Group # 1, Total Amount
-- SubGroup # 1.1, Total Amount
-- SubGroup # 1.1.1, Total Amount
-- Element # 1.1.1.1, Amount
-- Element # 1.1.1.2, Amount
-- SubGroup # 1.1.2, Total Amount
-- Element # 1.1.2.1, Amount
I have added a Columns, "Headline", which tells me if a Row is a group
or an Element.
Then when i want the Amount displayed i use this expression:
=IIF(Fields!Headline.Value = 0, Fields!Amount.Value,
(Sum(Fields!Amount.Value, "table2_Details_Group", recursive)))
This seems to be the only way to calculate the Totals for a recursive
table...
-Martin

creating new tables on diff file group using EM

Hi,
How do i acutally create a new table that i can specify to a different
file group using Enterprise Manager ?
this is how i done in TSQL :
create table test
( myname char(20)
)
on MYOWNFILEGROUP
appreciate any advice
tks & rdgs
In Enterprise Manager, Right Click on "Tables" node and select "New Table"
from the context menu. Click on "Table and Index Properties" button (Second
button from left to right) and use "Table Filegroup" dropdown list to select
"MYOWNFILEGROUP".
Cristian Lefter, SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
> Hi,
> How do i acutally create a new table that i can specify to a different
> file group using Enterprise Manager ?
> this is how i done in TSQL :
> create table test
> ( myname char(20)
> )
> on MYOWNFILEGROUP
> appreciate any advice
> tks & rdgs
|||tks Cristian , i got it
"Cristian Lefter" wrote:

> In Enterprise Manager, Right Click on "Tables" node and select "New Table"
> from the context menu. Click on "Table and Index Properties" button (Second
> button from left to right) and use "Table Filegroup" dropdown list to select
> "MYOWNFILEGROUP".
> Cristian Lefter, SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
>
>

creating new tables on diff file group using EM

Hi,
How do i acutally create a new table that i can specify to a different
file group using Enterprise Manager ?
this is how i done in TSQL :
create table test
( myname char(20)
)
on MYOWNFILEGROUP
appreciate any advice
tks & rdgsIn Enterprise Manager, Right Click on "Tables" node and select "New Table"
from the context menu. Click on "Table and Index Properties" button (Second
button from left to right) and use "Table Filegroup" dropdown list to select
"MYOWNFILEGROUP".
Cristian Lefter, SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
> Hi,
> How do i acutally create a new table that i can specify to a different
> file group using Enterprise Manager ?
> this is how i done in TSQL :
> create table test
> ( myname char(20)
> )
> on MYOWNFILEGROUP
> appreciate any advice
> tks & rdgs|||tks Cristian , i got it
"Cristian Lefter" wrote:
> In Enterprise Manager, Right Click on "Tables" node and select "New Table"
> from the context menu. Click on "Table and Index Properties" button (Second
> button from left to right) and use "Table Filegroup" dropdown list to select
> "MYOWNFILEGROUP".
> Cristian Lefter, SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
> > Hi,
> >
> > How do i acutally create a new table that i can specify to a different
> > file group using Enterprise Manager ?
> >
> > this is how i done in TSQL :
> >
> > create table test
> > ( myname char(20)
> > )
> > on MYOWNFILEGROUP
> >
> > appreciate any advice
> >
> > tks & rdgs
>
>

creating new tables on diff file group using EM

Hi,
How do i acutally create a new table that i can specify to a different
file group using Enterprise Manager ?
this is how i done in TSQL :
create table test
( myname char(20)
)
on MYOWNFILEGROUP
appreciate any advice
tks & rdgsIn Enterprise Manager, Right Click on "Tables" node and select "New Table"
from the context menu. Click on "Table and Index Properties" button (Second
button from left to right) and use "Table Filegroup" dropdown list to select
"MYOWNFILEGROUP".
Cristian Lefter, SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
> Hi,
> How do i acutally create a new table that i can specify to a different
> file group using Enterprise Manager ?
> this is how i done in TSQL :
> create table test
> ( myname char(20)
> )
> on MYOWNFILEGROUP
> appreciate any advice
> tks & rdgs|||tks Cristian , i got it
"Cristian Lefter" wrote:

> In Enterprise Manager, Right Click on "Tables" node and select "New Table"
> from the context menu. Click on "Table and Index Properties" button (Secon
d
> button from left to right) and use "Table Filegroup" dropdown list to sele
ct
> "MYOWNFILEGROUP".
> Cristian Lefter, SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
>
>

Creating new File groups on an existing db

Out production database is around 10G and it is all in one file group
(Primary).
I would like to create new file groups and split the database into multiple
file groups for better manageablity.
I have identified the tables that can be moved into new file groups.
What is the best approach to spilt an existing production database into
multiple file groups?
Thanks,
S.KumarSuresh
If your table whose are going to be place on separate filegroup have
clustered indexes then you can easily re-create the clustered index with
specify file group .For details please refer to BOL.
"Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
news:#A3i3KWbDHA.2548@.TK2MSFTNGP09.phx.gbl...
> Out production database is around 10G and it is all in one file group
> (Primary).
> I would like to create new file groups and split the database into
multiple
> file groups for better manageablity.
> I have identified the tables that can be moved into new file groups.
> What is the best approach to spilt an existing production database into
> multiple file groups?
> Thanks,
> S.Kumar
>|||You should start changing the filegroups for the specified tables, if there
aren´t many tables you could work with EM, edit table, property of the
tables/ indexes.
Jens Süßmeyer.|||Multiple files in a single filegroup allows SQL to do parallel IO on a
single query...
Using multiple filegroups allow you to.
1. Backup/Restore subsets of tables with different recovery needs.
2. Balance IO if you beleive you can do better than striping
3. Limit the space allocation which is used by a subset of tables...
The 1st is the best reason for filegroups...It does not improve
manageability, but causes you to have to watch for disk space utilization on
EACH filegroup...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corp (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
news:OxBFFmWbDHA.3360@.tk2msftngp13.phx.gbl...
> My aim is to split the database into multiple files.
> I could have multiple files referring to the same File Group (Primary) or
> multiple files going to multiple File groups.
> Which way is better and what are the pros and cons?
> Thanks,
> S.Kumar
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eki5qUWbDHA.1580@.tk2msftngp13.phx.gbl...
> > Suresh
> > If your table whose are going to be place on separate filegroup have
> > clustered indexes then you can easily re-create the clustered index with
> > specify file group .For details please refer to BOL.
> >
> >
> >
> > "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> > news:#A3i3KWbDHA.2548@.TK2MSFTNGP09.phx.gbl...
> > > Out production database is around 10G and it is all in one file group
> > > (Primary).
> > > I would like to create new file groups and split the database into
> > multiple
> > > file groups for better manageablity.
> > >
> > > I have identified the tables that can be moved into new file groups.
> > > What is the best approach to spilt an existing production database
into
> > > multiple file groups?
> > >
> > > Thanks,
> > > S.Kumar
> > >
> > >
> >
> >
>|||Wayne,
Did you use to work for Unisys ?
I use to know one Wayne Snyder when I worked for them in Charlotte 10 yrs
ago.
If you are the one, please send me a note to my personal email address.
Thanks,
Suresh Kumar
"Wayne Snyder" <wsnyder@.ikon.com> wrote in message
news:OyKlrSXbDHA.2672@.tk2msftngp13.phx.gbl...
> Multiple files in a single filegroup allows SQL to do parallel IO on a
> single query...
> Using multiple filegroups allow you to.
> 1. Backup/Restore subsets of tables with different recovery needs.
> 2. Balance IO if you beleive you can do better than striping
> 3. Limit the space allocation which is used by a subset of tables...
> The 1st is the best reason for filegroups...It does not improve
> manageability, but causes you to have to watch for disk space utilization
on
> EACH filegroup...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Computer Education Services Corp (CESC), Charlotte, NC
> (Please respond only to the newsgroups.)
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> news:OxBFFmWbDHA.3360@.tk2msftngp13.phx.gbl...
> > My aim is to split the database into multiple files.
> > I could have multiple files referring to the same File Group (Primary)
or
> > multiple files going to multiple File groups.
> >
> > Which way is better and what are the pros and cons?
> > Thanks,
> > S.Kumar
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:eki5qUWbDHA.1580@.tk2msftngp13.phx.gbl...
> > > Suresh
> > > If your table whose are going to be place on separate filegroup have
> > > clustered indexes then you can easily re-create the clustered index
with
> > > specify file group .For details please refer to BOL.
> > >
> > >
> > >
> > > "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> > > news:#A3i3KWbDHA.2548@.TK2MSFTNGP09.phx.gbl...
> > > > Out production database is around 10G and it is all in one file
group
> > > > (Primary).
> > > > I would like to create new file groups and split the database into
> > > multiple
> > > > file groups for better manageablity.
> > > >
> > > > I have identified the tables that can be moved into new file groups.
> > > > What is the best approach to spilt an existing production database
> into
> > > > multiple file groups?
> > > >
> > > > Thanks,
> > > > S.Kumar
> > > >
> > > >
> > >
> > >
> >
> >
>

Creating Group Eliminates Records

Forgive the novice nature of this question.

I have a report that I want to create four groups for. Prior to grouping, I've created a Sum for an amount field. I get a Total of $1000.00 (for simplicity sake I'm making up a number).

I create the first grouping and subtotal that group. No problem
I repeat for the second and third grouping. No problem.

However, on the final grouping, it changes my Grand Total to $988.50. I assume this means that records are being eliminated? The final grouping is different than groups 1, 2, and 3 in that it is in a different table. Does this have anything to do with it.

Your help is greatly appreciated.What type of join do you use when joining this table?
Inner or Left Outer?|||I've been taught to just click the smart button on the join screen. So I accepted what Crystal mapped.

It's set to an inner join. I changed it and it worked! Thank you so much.

Is there anyway that you can tell me in layman's terms why I needed to do that? What is the difference between those types of joins?

Also, do the joins get saved with the report, or do I need to set them each time I refresh the report with new data?

Thank you again!|||:) Yes, of course the joins get saved with the report.

I don't think I can give you better explanations then those you have in the 'Help' file.
Read help on 'inner join' and 'left outer join'.

Actually, here is one example of how it works, let's say we have 2 tables.
The 1st one is accounts:

acc_num acc_name state
-----------
acc1 name1 MN
acc2 name2 NJ
acc3 name3 NY
acc4 name4 OH
acc5 name5 AK

and the 2nd one is payments:

acc_num pay_date amount
------------
acc1 6/1/07 $30.00
acc3 7/4/07 $100.00
acc5 7/15/07 $40.00

If you use inner type of join to link them (accounts.acc_num=payments.acc_num), the result will be next:

acc_num acc_name state pay_date amount
--------------------
acc1 name1 MN 6/1/07 $30.00
acc3 name3 NY 7/4/07 $100.00
acc5 name5 AK 7/15/07 $40.00

2 records from the 1st table (acc2, acc4) are not selected because there are no such field values in the 2nd table.

But if you use left outer join, you will get all of the records from the primary (left) table:

acc_num acc_name state pay_date amount
--------------------
acc1 name1 MN 6/1/07 $30.00
acc2 name2 NJ
acc3 name3 NY 7/4/07 $100.00
acc4 name4 OH
acc5 name5 AK 7/15/07 $40.00

...probably, your smart button didn't get what you were trying to do ;)
but to be serious, I wouldn't use it. :)|||Duh, the help file. That was, helpful.

In all seriousness, thank you so much for your help.