March 10, 2015 at 6:30 am
SqlSanctum (3/9/2015)
jasona.work (3/9/2015)
SQLRNNR (3/9/2015)
jasona.work (3/7/2015)
Turns out, Public by default does not have CONNECT SQL. So I was thinking I could grant the privilege to Public, then revoke it from the individual accounts.
Did you use the public / "everyone" role, or did you end on creating a new role with the requisite permissions?
The public role should have as few permissions as possible.
As most of our servers are still SQL2008R2, we couldn't create a custom server role to handle this. As of right now, this is first privilege that isn't a default that's been added to Public, so we're keeping the priveleges low.
On our new SQL2012 boxes, I'll have to think about how to handle this. A custom role would be the better way (even if it is just for CONNECT SQL,) but would also result in more paperwork (yes, to add a server role to satisfy a mandatory STIG check, I'd have to get approval...) Plus having to remember to add any new users to that custom role any time I add one...
Pluses and minuses either way, it's just a matter of totalling them up and picking which way to jump.
STIG compliance woes! We use a custom Connect SQL role for 2012 boxes and public has Connect SQL for 2008 boxes just like your idea. Initial creation is handled by scripts each time a new server gets built then checked by policy daily.
So, thanks to your comment, now I've started poking around in Policy-Based-Management to keep an eye on this...
Actually have the basis of an executeSQL check, but it runs against all logins, so I'm starting to lean towards just using an Agent job. Or, seeing as we don't have that much "churn" in our logins, just manually checking once a month or so...
March 10, 2015 at 7:51 am
jasona.work (3/10/2015)
SqlSanctum (3/9/2015)
jasona.work (3/9/2015)
SQLRNNR (3/9/2015)
jasona.work (3/7/2015)
Turns out, Public by default does not have CONNECT SQL. So I was thinking I could grant the privilege to Public, then revoke it from the individual accounts.
Did you use the public / "everyone" role, or did you end on creating a new role with the requisite permissions?
The public role should have as few permissions as possible.
As most of our servers are still SQL2008R2, we couldn't create a custom server role to handle this. As of right now, this is first privilege that isn't a default that's been added to Public, so we're keeping the priveleges low.
On our new SQL2012 boxes, I'll have to think about how to handle this. A custom role would be the better way (even if it is just for CONNECT SQL,) but would also result in more paperwork (yes, to add a server role to satisfy a mandatory STIG check, I'd have to get approval...) Plus having to remember to add any new users to that custom role any time I add one...
Pluses and minuses either way, it's just a matter of totalling them up and picking which way to jump.
STIG compliance woes! We use a custom Connect SQL role for 2012 boxes and public has Connect SQL for 2008 boxes just like your idea. Initial creation is handled by scripts each time a new server gets built then checked by policy daily.
So, thanks to your comment, now I've started poking around in Policy-Based-Management to keep an eye on this...
Actually have the basis of an executeSQL check, but it runs against all logins, so I'm starting to lean towards just using an Agent job. Or, seeing as we don't have that much "churn" in our logins, just manually checking once a month or so...
You can make a Target Condition that will only include the logins you need to check, or exclude the logins you never want to check. I'd say that since you are probably monitoring new logins that are created too, you could monitor this less, but verifying everything regularly saves sanity when audits come up. Jobs would probably work just as well, and are easier to make. We went with PBM I think just to force us to learn it.
March 10, 2015 at 8:14 am
ChrisM@Work (3/10/2015)
For those of you who don't know, Grant stepped up to the plate when Benjamin Nevarez was caught up in transit somewhere and gave an off-the-cuff talk at about 3 minutes notice.
Cojones of steel.
Not the first time Grant has done that. He's stepped up to fill-in sessions at a couple of SQLSaturday's I've been involved in.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 10, 2015 at 8:17 am
SQLRNNR (3/9/2015)
Jack Corbett (3/9/2015)
Grant Fritchey (3/9/2015)
By the way, Gianluca is a giant.So this means if and when I meet Gianluca he'll have to be seated for us to see eye to eye.:-P
Well, he might have to be laying down for that to happen...:-D
Ouch, that's a low blow 😀
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 10, 2015 at 12:18 pm
Jack Corbett (3/10/2015)
SQLRNNR (3/9/2015)
Jack Corbett (3/9/2015)
Grant Fritchey (3/9/2015)
By the way, Gianluca is a giant.So this means if and when I meet Gianluca he'll have to be seated for us to see eye to eye.:-P
Well, he might have to be laying down for that to happen...:-D
Ouch, that's a low blow 😀
Operative word being low :hehe:;-):w00t:
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
March 10, 2015 at 12:26 pm
SQLRNNR (3/10/2015)
Jack Corbett (3/10/2015)
SQLRNNR (3/9/2015)
Jack Corbett (3/9/2015)
Grant Fritchey (3/9/2015)
By the way, Gianluca is a giant.So this means if and when I meet Gianluca he'll have to be seated for us to see eye to eye.:-P
Well, he might have to be laying down for that to happen...:-D
Ouch, that's a low blow 😀
Operative word being low :hehe:;-):w00t:
Take the high road, Jack. Just take the high road.
(duck)
March 10, 2015 at 3:17 pm
Brandie Tarvin (3/10/2015)
SQLRNNR (3/10/2015)
Jack Corbett (3/10/2015)
SQLRNNR (3/9/2015)
Jack Corbett (3/9/2015)
Grant Fritchey (3/9/2015)
By the way, Gianluca is a giant.So this means if and when I meet Gianluca he'll have to be seated for us to see eye to eye.:-P
Well, he might have to be laying down for that to happen...:-D
Ouch, that's a low blow 😀
Operative word being low :hehe:;-):w00t:
Take the high road, Jack. Just take the high road.
(duck)
Tsk tsk, don't belittle Jack.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 10, 2015 at 5:18 pm
Koen Verbeeck (3/10/2015)
Brandie Tarvin (3/10/2015)
SQLRNNR (3/10/2015)
Jack Corbett (3/10/2015)
SQLRNNR (3/9/2015)
Jack Corbett (3/9/2015)
Grant Fritchey (3/9/2015)
By the way, Gianluca is a giant.So this means if and when I meet Gianluca he'll have to be seated for us to see eye to eye.:-P
Well, he might have to be laying down for that to happen...:-D
Ouch, that's a low blow 😀
Operative word being low :hehe:;-):w00t:
Take the high road, Jack. Just take the high road.
(duck)
Tsk tsk, don't belittle Jack.
He can't help but be little.
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
March 10, 2015 at 6:12 pm
Brandie Tarvin (3/10/2015)
SQLRNNR (3/10/2015)
Jack Corbett (3/10/2015)
SQLRNNR (3/9/2015)
Jack Corbett (3/9/2015)
Grant Fritchey (3/9/2015)
By the way, Gianluca is a giant.So this means if and when I meet Gianluca he'll have to be seated for us to see eye to eye.:-P
Well, he might have to be laying down for that to happen...:-D
Ouch, that's a low blow 😀
Operative word being low :hehe:;-):w00t:
Take the high road, Jack. Just take the high road.
(duck)
He has trouble reaching it...
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 10, 2015 at 6:25 pm
Wow! I have little to say.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 10, 2015 at 6:37 pm
Jack Corbett (3/10/2015)
Wow! I have little to say.
😀
You rock!
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
March 10, 2015 at 7:48 pm
SqlSanctum (3/10/2015)
jasona.work (3/10/2015)
SqlSanctum (3/9/2015)
jasona.work (3/9/2015)
SQLRNNR (3/9/2015)
jasona.work (3/7/2015)
Turns out, Public by default does not have CONNECT SQL. So I was thinking I could grant the privilege to Public, then revoke it from the individual accounts.
Did you use the public / "everyone" role, or did you end on creating a new role with the requisite permissions?
The public role should have as few permissions as possible.
As most of our servers are still SQL2008R2, we couldn't create a custom server role to handle this. As of right now, this is first privilege that isn't a default that's been added to Public, so we're keeping the priveleges low.
On our new SQL2012 boxes, I'll have to think about how to handle this. A custom role would be the better way (even if it is just for CONNECT SQL,) but would also result in more paperwork (yes, to add a server role to satisfy a mandatory STIG check, I'd have to get approval...) Plus having to remember to add any new users to that custom role any time I add one...
Pluses and minuses either way, it's just a matter of totalling them up and picking which way to jump.
STIG compliance woes! We use a custom Connect SQL role for 2012 boxes and public has Connect SQL for 2008 boxes just like your idea. Initial creation is handled by scripts each time a new server gets built then checked by policy daily.
So, thanks to your comment, now I've started poking around in Policy-Based-Management to keep an eye on this...
Actually have the basis of an executeSQL check, but it runs against all logins, so I'm starting to lean towards just using an Agent job. Or, seeing as we don't have that much "churn" in our logins, just manually checking once a month or so...
You can make a Target Condition that will only include the logins you need to check, or exclude the logins you never want to check. I'd say that since you are probably monitoring new logins that are created too, you could monitor this less, but verifying everything regularly saves sanity when audits come up. Jobs would probably work just as well, and are easier to make. We went with PBM I think just to force us to learn it.
In our case, we'd need to be checking every login, every time, so I'm thinking an Agent job. Possibly set it to run once a week or so, and dump its' results to a table in a DB with a date stamp so we could, if needed, get an idea *when* a login got flipped...
March 11, 2015 at 2:24 am
SQLRNNR (3/10/2015)
Jack Corbett (3/10/2015)
Wow! I have little to say.😀
You rock!
I'd say he's clearly the bigger man 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 11, 2015 at 2:38 am
Jack Corbett (3/10/2015)
Wow! I have little to say.
Quick question, this is a normal size Strat in your picture then?
😎
March 11, 2015 at 2:48 am
Brandie Tarvin (3/10/2015)
SQLRNNR (3/10/2015)
Jack Corbett (3/10/2015)
SQLRNNR (3/9/2015)
Jack Corbett (3/9/2015)
Grant Fritchey (3/9/2015)
By the way, Gianluca is a giant.So this means if and when I meet Gianluca he'll have to be seated for us to see eye to eye.:-P
Well, he might have to be laying down for that to happen...:-D
Ouch, that's a low blow 😀
Operative word being low :hehe:;-):w00t:
Take the high road, Jack. Just take the high road.
(duck)
And now I'm going to have Loch Lomond playing in my head all day. Thank you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 47,776 through 47,790 (of 66,819 total)
You must be logged in to reply to this topic. Login to reply