Re: when to use sa and when not to..

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Hari Prasad (hari_prasad_k_at_hotmail.com)
Date: 06/02/04 

Date: Wed, 2 Jun 2004 21:31:38 +0530

Hi,

Database Backup and Restore can be done by a user which has gor db_owner
role. The user with db_owner role can almost all the tasks in the database.
he will not be able to do administrative stuffs in other database/system
databases.

SA will be having previlages to all the databases, plus

1. Adding logins/users
2. Adding roles/ assigning server wide fixed roles
3. Process administration
4. database creation
5. replication
etc...

In general SA can do any activity SQL server wide on all databases.

Note:

As DBA / owner of sql server you can use SA user to do all activity, but for
application accessing provide only the db_owner user to
developers/application users

Thanks
Hari
MCDBA

"newbie" <anonymous@discussions.microsoft.com> wrote in message
news:73466013-2037-4B92-876B-38F4AC0019CB@microsoft.com...
> Hi,
>
> My shrink wrap app uses one database and has 1 login mapped to the same
user (login and user have same name), so
> database: MyDB
> Login: MyAppUser (sa is kept in the list of logins as it is with a
password assigned)
> User: MyAppUser
> Password for login: MyPassword
> Authentication: SQL
> SQL Instance: MSSQL$MyApp
>
> MyAppUser is not part of sysadmin group but is a dbo for MyDB.
>
> My confusion is that when should I log into the MSSQL$MyApp as sa and when
as MyAppUser. I know that while doing the database restore I should be
logged in as sa into the Master db. But in general, which are the other
operations where it is best to login as sysadmin. I want to keep all the
application related tasks limited to MyAppUser and the administration part
to sa.. or what do you recommend.
>
> Thanks

Relevant Pages

  • Re: System Administrator Implied Permissions
    ... > sa login, it assigns it the System Administrator fixed ... > Now, given this, why does SQL Server ... in each database is always a member of the public and db_owner roles. ... Other sysadmin role members have the exact same ...
    (microsoft.public.sqlserver.security)
  • Re: No db access after publishing web site
    ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: cannot login to the db after...
    ... Jasper Smith (SQL Server MVP) ... I have created a new database, "db_1", using the "sa" ... I then created a new login, "sqluser1" and gave ...
    (microsoft.public.sqlserver.security)
  • Re: Cant view merge agent properties (trying again)
    ... In the List of Actions for the Snapshot Agent History I see this repeated: ... every single database listed. ... So, just now, I went to computername\Administrator Login ID (because it's ... On the computer running SQL Server, ...
    (microsoft.public.sqlserver.replication)
  • Re: Database security design with ASP.net and form-based authentication
    ... Since you already have forms-based security, why not use a single SQL login ... for all database access? ... data entry, guest/view only, admin, report viewer. ... so I'm using SQL Server authentication. ...
    (microsoft.public.sqlserver.security)