August 13, 2015 at 5:27 am
I need to automate the db users to get the data for every month on all servers , can any one help on this and also provide the necessary script on this ..
August 13, 2015 at 5:45 am
You need to provide a little more detail. It sounds like you said that you want to send a copy of all the data on all the servers to all the people. That's a pretty crazy request.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2015 at 6:51 am
yes ..
August 13, 2015 at 8:04 am
my requirement is I need to generate the db acess of all the users and roles on all the databases on a server as monthly . how can I automate this task ?
August 13, 2015 at 8:38 am
I'm sorry. I'm still not understanding. Now you're saying that you need to automate access to the databases. First you said you needed to automate sending the data. I'd like to help, but the requests are different and very vague, so I really don't know what to suggest.
When you said "yes" to copying all databases on all servers to all users, that's just not a practical thing. Let's say you have 100 users (a very small number). Let's say you have 100 databases (not a very large number). Let's say you have databases that are 100gb in size (again, small databases). You're suggesting that we move 100*100*100, or 1,000,000gb of data. That's a petabyte of data. That's going to be a much more serious undertaking involving massive amounts of storage, mail servers, networks, etc. It's much more than having me suggest a script for automation. See what I mean?
In order to make meaningful suggestions to you, the questions need to be focused and clear so that we can help out. Right now, I just can't.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2015 at 9:11 am
Just to take a stab at this, it sounds like maybe you're wanting a sort of audit, either of which users/roles have what permissions, or of which users/roles have accessed what data in the last month? Is that correct?
August 13, 2015 at 9:12 am
Quick suggestion, hire a consultant.
😎
August 13, 2015 at 9:16 am
ramyours2003 (8/13/2015)
my requirement is I need to generate the db acess of all the users and roles on all the databases on a server as monthly . how can I automate this task ?
You can start with this
http://jasonbrimhall.info/2010/03/19/security-audit/
If I have understood what you need, you want a security audit report to be generated.
To automate it:
1. Create and SSRS report and a subscription to the report
2. Create an agent job that runs it periodically and then emails the results to everybody
3. Write a powershell script that runs this script and emails it, then schedule that in windows task scheduler
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 13, 2015 at 9:19 am
Jacob Wilkins (8/13/2015)
Just to take a stab at this, it sounds like maybe you're wanting a sort of audit, either of which users/roles have what permissions, or of which users/roles have accessed what data in the last month? Is that correct?
If it is the last bit (who has accessed what), then I sure hope they had some sort of audit in place before this request.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 13, 2015 at 9:20 am
If you happen to need some other sort of audit, there are plenty of examples and scripts here...
http://jasonbrimhall.info/?s=audit
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 13, 2015 at 9:23 am
SQLRNNR (8/13/2015)
Jacob Wilkins (8/13/2015)
Just to take a stab at this, it sounds like maybe you're wanting a sort of audit, either of which users/roles have what permissions, or of which users/roles have accessed what data in the last month? Is that correct?If it is the last bit (who has accessed what), then I sure hope they had some sort of audit in place before this request.
Indeed!
August 13, 2015 at 9:30 am
I think the bottom line is that we need more information about what you're trying to do to be able to help you.
August 13, 2015 at 9:48 am
Ed Wagner (8/13/2015)
I think the bottom line is that we need more information about what you're trying to do to be able to help you.
No this is the bottom line
d'oh and here I went and typed below it. Dang it!
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 13, 2015 at 10:54 am
SQLRNNR (8/13/2015)
Ed Wagner (8/13/2015)
I think the bottom line is that we need more information about what you're trying to do to be able to help you.No this is the bottom line
d'oh and here I went and typed below it. Dang it!
Jason
you have
crossed
the line
😎
August 13, 2015 at 11:19 am
Eirikur Eiriksson (8/13/2015)
SQLRNNR (8/13/2015)
Ed Wagner (8/13/2015)
I think the bottom line is that we need more information about what you're trying to do to be able to help you.No this is the bottom line
d'oh and here I went and typed below it. Dang it!
Jason
you have
crossedthe line
😎
Well, now that the line has been crossed and obliterated, we will still need some level of detail in order to offer any help at all.
Unless, of course, the links to that fine security audit solved the problem and there's no need for anything further.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply