August 29, 2013 at 7:01 am
That's one of the reasons I color code my instances' query windows in SSMS (green for Dev, yellow for Test, orange for QC, and red for Production). The colors help me remember what environment I'm currently connected to. Also, I stop before I hit any okay buttons and verify everything in the windows to make sure I'm pointing to the correct instance.
How to do the color code on the instance. can you please provide the steps on SSMS how to do.
August 29, 2013 at 7:05 am
Here's another vote for multiple logins.
How about creating a new SA account with SA rights then revoke SA
August 29, 2013 at 7:10 am
You can't revoke or deny to a sysadmin account. The login named 'sa' cannot have any permissions changed, it can only be disabled.
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
August 29, 2013 at 7:11 am
Hi Brandie Tarvin & K. Brian Kelley,
can you please let me know how to change colour of the new query window for prod(red) and dev(green), it can be a great help for this kind of situation and can it be as permanent for the server. where will change or it can only for the persion who changed it ?
August 29, 2013 at 7:13 am
das.saroj09 (8/29/2013)
That's one of the reasons I color code my instances' query windows in SSMS (green for Dev, yellow for Test, orange for QC, and red for Production). The colors help me remember what environment I'm currently connected to. Also, I stop before I hit any okay buttons and verify everything in the windows to make sure I'm pointing to the correct instance.
How to do the color code on the instance. can you please provide the steps on SSMS how to do.
Open SSMS. Click the button to open a new query window with a new connection (to the right of the New Query button) or navigate to File -> New -> Database Engine Query (CTRL - O also opens this window).
When the connection window pops up, click OPTIONS. It will flip you over to the Connection Properties tab. Check the "Use custom color:" box. Then hit the SELECT button to the right of it. A color palette will open up for you to choose your colors. Choose your color, click OK. Then hit Connect.
From now on (until SSMS resets itself due to weird errors or sometimes installs of service packs), any queries using that specific instance connection will be that color across the bottom bar of the SSMS window.
August 29, 2013 at 7:14 am
I just use the regular OPTIONS available with native SSMS. But then, it only color codes the bottom of the window and can be reset to default if something strange happens to SSMS.
When you have the login screen open click on options on the bottom. Then click on the second tab (connection properties). There is a checkbox at the bottom for "use custom color". Once you check the box there will be a select button on the right. Pick the color you want and off you go.
--EDIT--
Looks like Brandie posted the same while I was typing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2013 at 7:17 am
das.saroj09 (8/29/2013)
it can be a great help for this kind of situation and can it be as permanent for the server. where will change or it can only for the persion who changed it ?
It is not a permanent thing as it can be reset to any color (or no color at all) at any time, including things that cause SSMS to reset to default settings.
As far as I know, all OPTIONS for SQL Server are user sensitive only and do not translate to all people touching the server.
August 29, 2013 at 7:24 am
This is why I love Mladen's tool, especially if your servers follow a naming convention to define the environments. It allows you to build RegEx to color code the instances. 🙂
K. Brian Kelley
@kbriankelley
August 29, 2013 at 7:32 am
Although color can be a reminder, it is only a reminder.
And it is a personal setting, not universal, So if you have more than 1 sysadmin, it is much less effective.
We used to extend the concept even to the Windows environment.
Even a Domain Admin, or a Server Admin, would use a separate account for admin activities.
This has the benefit of easier auditing and tracability.
As you can see, convenience and speed has some danger.
Just think if you have a large production environment, and someone truncates a table.
Then suppose you cannot just restore the table selectively.
Could be a huge problem, even if the table is small.
August 29, 2013 at 7:50 am
The two accounts is a bit different than maintaining a separate admin account in development.
The main reason for the two accounts is to help reduce the risk of drive-by attacks (where you hit a web site and an exploit automatically fires, taking advantage of some security weakness in the browser or a linked app, such as Adobe Acrobat Reader) and emails which effectively do the same thing.
Your non-privileged account is used for email, web browsing, etc., and if it has any admin rights, it's only over the workstation. Therefore, other systems can't be infected with the credentials. That would require administrative rights on those systems, which is what the second account is for.
With the different accounts in different environments, they are all admin accounts. Just one works for dev and one works for prod and dev never has the ability to work in prod.
K. Brian Kelley
@kbriankelley
August 29, 2013 at 7:59 am
K. Brian Kelley (8/29/2013)
With the different accounts in different environments, they are all admin accounts. Just one works for dev and one works for prod and dev never has the ability to work in prod.
Unless the same person is doing both and happens to have different SSMS windows open. In which case, it doesn't matter what that person does to prevent these things from happening in the future, they still will happen unless that person double-checks and pays attention.
August 29, 2013 at 8:04 am
Multi-tasking is bad! 🙂
K. Brian Kelley
@kbriankelley
August 29, 2013 at 8:27 am
I used to do something like this to make sure I wasn't running an operation on live.
Not sure if it'd work for restore (does it have to be the only statement in a batch?)
IF @@SERVERNAME = 'Live Server'
BEGIN
PRINT 'You''re on the Live Server!'
END
ELSE
BEGIN
RESTORE DATABASE...
END
August 29, 2013 at 5:36 pm
Brandie Tarvin (8/29/2013)
K. Brian Kelley (8/29/2013)
With the different accounts in different environments, they are all admin accounts. Just one works for dev and one works for prod and dev never has the ability to work in prod.Unless the same person is doing both and happens to have different SSMS windows open. In which case, it doesn't matter what that person does to prevent these things from happening in the future, they still will happen unless that person double-checks and pays attention.
But you won't be able to restore prod over dev or vice versa because the boxes themselves cannot see the backups of the other.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 30, 2013 at 10:39 am
Jeff Moden (8/29/2013)
Brandie Tarvin (8/29/2013)
K. Brian Kelley (8/29/2013)
With the different accounts in different environments, they are all admin accounts. Just one works for dev and one works for prod and dev never has the ability to work in prod.Unless the same person is doing both and happens to have different SSMS windows open. In which case, it doesn't matter what that person does to prevent these things from happening in the future, they still will happen unless that person double-checks and pays attention.
But you won't be able to restore prod over dev or vice versa because the boxes themselves cannot see the backups of the other.
Okay, I think you've taken this conversation up a level.
The boxes will and can see backups saved to disk assuming the Agent account (which is not the accounts we were talking about earlier) can see the backup paths. I know this because this is how it works in our environment. We have different agent accounts in prod and dev, yet because they do have perms on the backup path, we can restore without issue.
But the conversation (the way I understood it) was that the users should have different accounts for the boxes (which still won't prevent restoring over stuff).
Did I misunderstand something here?
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply