Re: securityadmin

Look , actually the sysadmin is responsible for all tasks on the server.
I have not seen companies that have assigned to a security admin server
role someone.

Well, now you have seen one that separates the two. We have a department
that is responsible for IT Security in general (Oracle, DB2, SQL Server,
etc.), and they are called ITSEC. We DBAs are only responsible for database
objects - we even have positions called Data Administrators, so we aren't
responsible for data either. Just the objects, but not the permissions to
the objects.

I think I may have found the issue. By Government standards, we have to
REVOKE access to all objects from Guest and Public. This causes problems,
as fixed server roles inherit certain EXECUTE rights on system stored
procedures (sp_addgroup, sp_addrole, etc.) If you look at the sproc called
sp_addrole, you will see the following statement:

if (not is_member('db_securityadmin') = 1) and
(not is_member('db_owner') = 1)

That is only part of the equation, however. being a member of
db_securityadmin also assumes you have the inerent right to execute certain
system stored procedures (inherited from the public role). The above is
merely an added filter to ensure you are a member of that fixed server role.

In short, I have to explicitly grant access to the database object for the
fixed server roles so they not only pass the above statement, but so they
also have permissions to the object.

I think I've found my issue, and many headaches that go along with it.




"Uri Dimant" <urid@xxxxxxxxxxx> wrote in message
news:O2sVhpkpGHA.4196@xxxxxxxxxxxxxxxxxxxxxxx
Hi
However, that is not sufficient to connect to EM or Query Analyzer. If I
add the database user to db_datareader, then all is okay. Is that normal
to have to do that?

Yes


Look , actually the sysadmin is responsible for all tasks on the server.
I have not seen companies that have assigned to a security admin server
role someone
The securityadmin has permission to execute the sp_password stored
procedure for all users other than members of the sysadmin role.






"A McGuire" <allen.mcguire@xxxxxxxxxxxxxxxxx> wrote in message
news:eILylvcpGHA.4116@xxxxxxxxxxxxxxxxxxxxxxx
To expand on my question a bit:

1) I created a new SQL login called 'securityadmin' and added it to the
fixed server role Security Administrators.
2) I changed my SQL registration to use that new account
3) EM returned an error: Execute permission denied on object
'sp_MSSQLDM080_version', database 'master', owner 'dbo'
4) Now, I can go ahead and add that login as a database user and add them
to the database fixed role of db_securityadmin

However, that is not sufficient to connect to EM or Query Analyzer. If I
add the database user to db_datareader, then all is okay. Is that normal
to have to do that? To me, I would think that the security administrator
server role should inherit access to each database, otherwise I wouldn't
put them in that server role! Rather I would add them to the fixed
database roles, as I see fit.

Hope that clears up my question a bit.

"Uri Dimant" <urid@xxxxxxxxxxx> wrote in message
news:ONU8JtyoGHA.4152@xxxxxxxxxxxxxxxxxxxxxxx
Hi
After moving them to the logical choice of securityadmin, EM reports
errors about not having access to select data from syslogins and such.
Can you elaborate a little bit what does it mean?

Might I ask how any of you have addressed this sort of scenario, where
you have DBA's and security administrators as separate entities?


No at our shop, however, there is security admin server role.
Read about Fixed Server Roles in the BOL




"A McGuire" <allen.mcguire@xxxxxxxxxxxxxxxxx> wrote in message
news:%23k$rJZeoGHA.4404@xxxxxxxxxxxxxxxxxxxxxxx
At the company I work for, we have security personnel that handle
assigning privileges to databases and object. The security personnel
themselves used to be in the sysadmin fixed server role. After moving
them to the logical choice of securityadmin, EM reports errors about
not having access to select data from syslogins and such.

Might I ask how any of you have addressed this sort of scenario, where
you have DBA's and security administrators as separate entities?

Allen









.

Relevant Pages

  • Re: SceCli Error 1202 filling up the Event Log!
    ... > after restarting the Win2003 server, the secedit.sdb database does not get ... >>> security database and have it recreated. ... >>> configuration\windows settings\security settings, you should inventory ...
    (microsoft.public.win2000.advanced_server)
  • security-basics Digest of: get.123_145
    ... VPN to ASP a security risk? ... Re: Multiple IPSec tunnels? ... Subject: Security NT Server ... VPN to ASP a security risk? ...
    (Security-Basics)
  • << SBS News of the week - Sept 26 >>
    ... And he points to the info you need to put the file on the server in the ... at the network perimeter. ... The Symantec Firewall/VPN and the Gateway Security ... by the firewall at risk. ...
    (microsoft.public.backoffice.smallbiz)
  • << SBS News of the week - Sept 26 >>
    ... And he points to the info you need to put the file on the server in the ... at the network perimeter. ... The Symantec Firewall/VPN and the Gateway Security ... by the firewall at risk. ...
    (microsoft.public.backoffice.smallbiz2000)
  • Re: << SBS News of the week - Sept 26 >>
    ... > And he points to the info you need to put the file on the server in the ... > at the network perimeter. ... The Symantec Firewall/VPN and the Gateway Security ... An attacker can exploit these flaws in tandem via specially ...
    (microsoft.public.backoffice.smallbiz2000)