Talking People Out Of SA

Brent Ozar walks through one way to reduce SA account usage.

Part one:  the nice way.

In a perfect world, you’d create a minimally-privileged AD login that only has limited access to specific databases.

However, when you’ve got a toddler running with scissors and razors, sometimes you’re happy just to get the razors out of their hands first, and then you’ll work on the scissors next. One step at a time. Preferably not running.

For now, create another SQL account with DBO permissions on all of the databases involved with the application. (If you’re dealing with multiple different tenants on the same server, give them each their own SQL login.) Let them be complete owners of their databases for now.

Part two:  the not-as-nice way.

Power User: “EVERYTHING IS DOWN! THE SA ACCOUNT PASSWORD ISN’T WORKING! DID YOU RESET IT?”

Me: “Of course not. You told me not to.”

Power User: “THEN WHO DID IT?”

Me: “Oh, I have no way of knowing. Anyone who uses the account can change the password with theALTER LOGIN command. And you said everyone has it, right?”

That’s a nice account you have; it’d be a shame if something…unfortunate…were to happen to it.

Related Posts

xp_cmdshell And Non-Sysadmin Accounts

Lucas Kartawidjaja shows us how you can grant a non-sysadmin user the right to run xp_cmdshell: Once we run the above T-SQL query, any account that is part of the sysadmin role in the SQL Server instance has the ability to run the xp_cmdshell extended stored procedure. On the background, when the user with sysadmin […]

Read More

Switching Azure Portal Accounts

John Morehouse is happy with a change to the Azure Portal: This means that I could have multiple email accounts that I have to use in order to sign into the portal.  Using a password manager such as 1Password, not usually a big deal and more of an annoyance rather than a headache.Within the past month […]

Read More

Categories

January 2016
MTWTFSS
« Dec Feb »
 123
45678910
11121314151617
18192021222324
25262728293031