Difference between Server and System Administrators

Hi!
I was just wondering that what is the difference between Server
Administrator and System Administrator roles in SQL server. Can you
please guide me with the list of the tasks that one can do and other
cannot.
Thanks in advanceLookup "serveradmin fixed server role" in BooksOnLine.
Andrew J. Kelly SQL MVP
<sajid_yusuf@.yahoo.com> wrote in message
news:1126285490.756823.272090@.g44g2000cwa.googlegroups.com...
> Hi!
> I was just wondering that what is the difference between Server
> Administrator and System Administrator roles in SQL server. Can you
> please guide me with the list of the tasks that one can do and other
> cannot.
> Thanks in advance
>sql

difference between Owned schemas and Roles members

Hi,
I want to create a login (for account ASPNET from the Active Directory) in
sql server express 2005 for a specific database.
When addidng a new user to a specific database, i see:
Owned schemas, where i take db_datareader and db_datawriter
Roles memmbers: also db_datareader and db_datawriter
What's the difference between both and are they both required fpr account
ASPNET?
Tbanks
BartThis is answered
"Bart" <b@.sdq.dc> schreef in bericht
news:%23buSVSjbHHA.1400@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I want to create a login (for account ASPNET from the Active Directory) in
> sql server express 2005 for a specific database.
> When addidng a new user to a specific database, i see:
> Owned schemas, where i take db_datareader and db_datawriter
> Roles memmbers: also db_datareader and db_datawriter
> What's the difference between both and are they both required fpr account
> ASPNET?
> Tbanks
> Bart
>

Development - Production Data Access

Hello All,
I have been searching for a published document for Best Practices
concerning access levels based on roles. Should developers have more
than (if at all) select level access to production data? If I
understand (from multiple postings) that it is best to have:
1. Development (developers have extensive access levels)
2. Test (developers have restriced access levels)
and
3. Production (developers have none or select level access)
Our environment and budget only allows for items 1 and 3.
If any body could point me to a document from a 'reputable' source, I
would greatly appreciate it.

TIA
BillI don't have a reputable source for you, only some more opinions.

Practices obviously vary from place to place and will depend partly on
the size and complexity of your dev operation, your toolset, and on how
much support your developers need to do. If your developers have to
support systems in production then they may need some extra level of
access to the production environment.

One thing I would not want to compromise on: do not test only in a dev
environment. That's because it's important to have a separate a
deployment process for testing that mirrors the way you will deploy
changes to production. In my opinion that's the best way to ensure that
you only release to production exactly what is tested. That doesn't
necessarily mean you need physically separate servers - whether that's
necessary depends on what components are under test. In the case of SQL
Server it does mean you ought to at least have separate instances for
development and testing.

--
David Portas
SQL Server MVP
--|||Bill Willyerd (bwillyerd@.dshs.wa.gov) writes:
> I have been searching for a published document for Best Practices
> concerning access levels based on roles. Should developers have more
> than (if at all) select level access to production data? If I
> understand (from multiple postings) that it is best to have:
> 1. Development (developers have extensive access levels)
> 2. Test (developers have restriced access levels)
> and
> 3. Production (developers have none or select level access)
> Our environment and budget only allows for items 1 and 3.
> If any body could point me to a document from a 'reputable' source, I
> would greatly appreciate it.

I think it's difficult to come with a best practice here, because it
is likely to business-dependent.

If developers have full access to the production database, this means
that they address critical issues directly, and don't have to spend
half a day to get some sort of access.

On the the other hand, this also means that developers are able to
all sorts of silly stuff in production, and also get access to data
that is sensitive.

So here is obviously a trade-off. The more availability you need, the
more in security you need to sacrifice - or invest in procedures so
that when a developer needs to debug in production, he can get access
easily by some sort of approval procedure.

I fully agree with David's view that you need a test environment
separate from development. I'll chime in here and add that the
process of transferring code from different environments should
be performed through version control, and the source-countrol system
is the master for all code to test and production environments. (As
well as to development environment to some extent as well.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Bill Willyerd" <bwillyerd@.dshs.wa.gov> wrote in message
news:1123690516.767246.211860@.o13g2000cwo.googlegr oups.com...
> Hello All,
> I have been searching for a published document for Best Practices
> concerning access levels based on roles. Should developers have more
> than (if at all) select level access to production data? If I
> understand (from multiple postings) that it is best to have:
> 1. Development (developers have extensive access levels)
> 2. Test (developers have restriced access levels)
> and
> 3. Production (developers have none or select level access)
> Our environment and budget only allows for items 1 and 3.
> If any body could point me to a document from a 'reputable' source, I
> would greatly appreciate it.
> TIA
> Bill

In addition to David and Erlands' comments, you might want to consider
Sarbanes-Oxley compliance. As a general comment, SOX compliance requires a
separation of duties (and therefore permissions) between development and
production. As a result, it's often not even an option to allow to
developers change access in the production environment.

But as I understand it, what you have to do to comply with SOX is negotiated
with your external auditors, and it depends heavily on your internal
environment. So you may want to investigate what (if any) legal obligations
you have to consider, and what the precise implementation details are for
your situation. For what it's worth, in my environment developers have no
change access to UAT or production (db_datareader only), so all code and
scripts are deployed via an Operations team - this is great for SOX
purposes, but obviously it adds both cost and time.

Simon

Developers on Other Machines (formerly: Problem installing SQL Server Reporting Services )

Bruce:
Sorry for the misposting.
I don't understand what you mean by "you haven't setup your roles for
the developers on other machines." Once a user role (developer or
otherwise) is set up on the server, shouldn't it be in effect no
matter where a user logs in from?
Jody
"Bruce Loehle-Conger (bruce_lcNOSPAM@.hotmail.com)" wrote:
> Your problem seems different. He was having a problem installing. Your
> problem seems to be accessing it. It looks to me like you haven't setup your
> roles for the developers on other machines.
Bruce L-C
>"JodyT" <datagal@.msn.com> wrote in message
>news:f9d864c3.0408111251.61c4d16f@.posting.google.com...
>> I'm having a similar problem...
>>
>> Enterprise and Developer set up on one server machine, Developer on
2
>> other desktops. If you access Report Manager from the Server
machine,
>> everything looks fine. But Report Manager accessed from the
>> non-server machines shows the top banner only... No tabs, no
folders,
>> nothing below the line, so a developer can't upload without
switching
>> over to the server, and even then there are often errors.
>>
>> Is there some configuration subtlety we missed?
>>
>> Otherwise this thing is giving me problems with OutOfMemory errors
on
>> local debugs that I know aren't too big.
>>
>> Ideas?
>> Jody
>>I suspect one of two things are happening. You have anonymous access on
which ends up treating everyone as the same regardless of how they are
logged in and they end up not having the permissions you wish them to have.
Or, the developers are either not assigned to a role, or are assigned to a
role that does not have the correct rights, or are assigned to a role with
the correct rights but that role has not been applied to the appropriate
part of the website. Unless you override it, any folder/report inherits the
role assignments from further up the tree. So if you assign the role from
above
My guess is when they are on the server they are not logged in as themselves
but are logged in as the admin for the local machine.
If they log into the server as themselves and the website does not look the
same as when they try to access it from their computers then my reasoning
falls apart.
So, check the the following.
1. Do you have anonymous access turned on?
2. What happens if you log into the server as one of the developers that are
having difficulties.
Bruce L-C
"JodyT" <datagal@.msn.com> wrote in message
news:f9d864c3.0408120929.8a3b6d7@.posting.google.com...
> Bruce:
> Sorry for the misposting.
> I don't understand what you mean by "you haven't setup your roles for
> the developers on other machines." Once a user role (developer or
> otherwise) is set up on the server, shouldn't it be in effect no
> matter where a user logs in from?
> Jody
> "Bruce Loehle-Conger (bruce_lcNOSPAM@.hotmail.com)" wrote:
> > Your problem seems different. He was having a problem installing. Your
> > problem seems to be accessing it. It looks to me like you haven't setup
your
> > roles for the developers on other machines.
> Bruce L-C
> >"JodyT" <datagal@.msn.com> wrote in message
> >news:f9d864c3.0408111251.61c4d16f@.posting.google.com...
> >> I'm having a similar problem...
> >>
> >> Enterprise and Developer set up on one server machine, Developer on
> 2
> >> other desktops. If you access Report Manager from the Server
> machine,
> >> everything looks fine. But Report Manager accessed from the
> >> non-server machines shows the top banner only... No tabs, no
> folders,
> >> nothing below the line, so a developer can't upload without
> switching
> >> over to the server, and even then there are often errors.
> >>
> >> Is there some configuration subtlety we missed?
> >>
> >> Otherwise this thing is giving me problems with OutOfMemory errors
> on
> >> local debugs that I know aren't too big.
> >>
> >> Ideas?
> >> Jody
> >>|||Actually I am the developer, with very minimal network learnin'. I
finally got the guy who installed to look at the research I've been
doing and he ended up reinstalling "paying closer attention to the
configuration options". He did have to do some monkeying around with
the anonymous thing, but he seems to have it working now. We'll see
how it goes if we ever put some actual users on it.
Thanks all for the input. It was a big help.
Jody
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<ur$lBPJgEHA.556@.tk2msftngp13.phx.gbl>...
> I suspect one of two things are happening. You have anonymous access on
> which ends up treating everyone as the same regardless of how they are
> logged in and they end up not having the permissions you wish them to have.
> Or, the developers are either not assigned to a role, or are assigned to a
> role that does not have the correct rights, or are assigned to a role with
> the correct rights but that role has not been applied to the appropriate
> part of the website. Unless you override it, any folder/report inherits the
> role assignments from further up the tree. So if you assign the role from
> above
> My guess is when they are on the server they are not logged in as themselves
> but are logged in as the admin for the local machine.
> If they log into the server as themselves and the website does not look the
> same as when they try to access it from their computers then my reasoning
> falls apart.
> So, check the the following.
> 1. Do you have anonymous access turned on?
> 2. What happens if you log into the server as one of the developers that are
> having difficulties.
> Bruce L-C
> "JodyT" <datagal@.msn.com> wrote in message
> news:f9d864c3.0408120929.8a3b6d7@.posting.google.com...
> > Bruce:
> >
> > Sorry for the misposting.
> >
> > I don't understand what you mean by "you haven't setup your roles for
> > the developers on other machines." Once a user role (developer or
> > otherwise) is set up on the server, shouldn't it be in effect no
> > matter where a user logs in from?
> >
> > Jody
> >
> > "Bruce Loehle-Conger (bruce_lcNOSPAM@.hotmail.com)" wrote:
> > > Your problem seems different. He was having a problem installing. Your
> > > problem seems to be accessing it. It looks to me like you haven't setup
> your
> > > roles for the developers on other machines.
> >
> > Bruce L-C
> >
> > >"JodyT" <datagal@.msn.com> wrote in message
> > >news:f9d864c3.0408111251.61c4d16f@.posting.google.com...
> > >> I'm having a similar problem...
> > >>
> > >> Enterprise and Developer set up on one server machine, Developer on
> 2
> > >> other desktops. If you access Report Manager from the Server
> machine,
> > >> everything looks fine. But Report Manager accessed from the
> > >> non-server machines shows the top banner only... No tabs, no
> folders,
> > >> nothing below the line, so a developer can't upload without
> switching
> > >> over to the server, and even then there are often errors.
> > >>
> > >> Is there some configuration subtlety we missed?
> > >>
> > >> Otherwise this thing is giving me problems with OutOfMemory errors
> on
> > >> local debugs that I know aren't too big.
> > >>
> > >> Ideas?
> > >> Jody
> > >>