Re: securityadmin

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: securityadmin
    ... grant that role access to each database, ... No at our shop, however, there is security admin server role. ... Read about Fixed Server Roles in the BOL ...
    (microsoft.public.sqlserver.security)
  • securing data in asp.net
    ... I am new to asp.net development, and need help on security, I am developing ... the second layer of security is to ensure that the data is ... database user id and password in the web.config file but this is a text file ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • What permissions do I need to manage users?
    ... I have a user that I have granted the server role "security ... as this user I can create new logins but not run sp_adduser to add the ... however run sp_revokedbaccess to get rid of a user from the database. ...
    (comp.databases.ms-sqlserver)
  • Re: setting a password on a button on the switchboard
    ... Could you send me the sample database for the fourth option (4. ... > Security in an Access database can probably be broken down into two big ... > points about being easier than User Level Security, ... > What type of data are you trying to protect? ...
    (microsoft.public.access.forms)
  • Re: access 2003
    ... security in access 2003. ... The data will go on the server and the program database ... than the alternative of creating an mde file. ... MDW file from the written record. ...
    (microsoft.public.access.conversion)