SA Account

Question

SA Account

suppiunna

SSC Veteran

Points: 225
More actions
December 31, 2014 at 10:38 am

#294040

Hi,
We are using .net application which is hitting the database using SA Account.. I am looking for any alternative to SA Account like whether can I use groups or roles so that my application can hit the database..

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

yes. You can create a SQL Server login just for your application to use. You just have to apply the appropriate permissions.

For example:

create myAppLogin

grant it access to myDatabase

the application needs to be able to read and write to the database, so grant it db_datareader and db_datawriter.

If you get errors trying to do something, it means you haven't granted the appropriate permissions and need to research what permissions are needed.

-SQLBill

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

On that same note, the application absolutely MUST NOT have SA or DBO privs. My personal preference should be apps that only have PUBLIC privs and the privs to run certain stored procedures but, in reality, having read and write privs normally needs to be included. Other than that, there should be no other privs required or the application is doing something incredibly wrong.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125561 More actions · Answer 3

suppiunna (12/31/2014)
Hi,
We are using .net application which is hitting the database using SA Account.. I am looking for any alternative to SA Account like whether can I use groups or roles so that my application can hit the database..

Like others here have said, it's best to have a dedicated login account for the application and also a dedicated role with minimal permissions.

That said, the 'SA' login is just a SQL Server authenticated account that has sysadmin membership by default. If the application developers don't really need sysadmin permissions but still argue that they can only connect as 'SA' for technical reasons (ex: the connection credentials are hard coded in a module for which they don't have source code, or there are too many connection strings and they don't have time to change them all), then you as the DBA do have the option of altering the permissions for the account.

use MASTER

go

alter login [sa] DISABLE;

go

alter login [sa] with name = [sa_bak];

go

create login [sa] with PASSWORD = 'wh@t3v3r1210', DEFAULT_DATABASE = master;

go

use Accounting;

go

drop user [sa];

go

create USER [sa] for login [sa];

go

exec sp_addrolemember 'db_datareader', 'sa';

exec sp_addrolemember 'db_datawriter', 'sa';

go

When all else fails, or when the king's men don't know what good for them, then we have to assume the role of the wizard Merlin and take care of things in the background.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Let me cast another vote for not letting applications connect with sysadmin privs - it's just plain dangerous and you're asking for trouble. Ask yourself who's responsible for the server and then take measures to protect it from everyone - internal and external.

Nice approach, Eric. This could cause problems in SQL 2008 if you upgrade, but I don't know if the bug persisted to the newer versions. Just make sure you remember (or save the code) and you should be all set. http://blogs.msdn.com/b/psssql/archive/2008/09/10/upgrade-for-sql-server-2008-can-fail-if-you-have-renamed-the-sa-account.aspx

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Rajneeshyadav18 SSC Rookie Points: 48 More actions · Answer 5

This has worked for me:

IF you are Giving Group Permission

GRANT CONTROL ON SCHEMA::[dbo] TO [GroupLogin];

GRANT IMPERSONATE ON USER::DBO TO [GroupLogin];

IF you want to give permission to the user only

GRANT CONTROL ON SCHEMA::[dbo] TO [UserLogin];

GRANT IMPERSONATE ON USER::DBO TO [userlogin];

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Rajneeshyadav18 (1/6/2015)
GRANT IMPERSONATE ON USER::DBO TO [userlogin];

????????

Why would you give an application account permission to impersonate the database owner. That essentially gives then DB_owner permissions.

Not exactly adhering to the principle of least privilege

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass