Restricting a user in a group

[jmickael]

I have a windows group assigned to sysadmin. This group, "SQLAdminDB1" has 4 domain users in it. There is a human resourches database on this instance of SQL Server 2000. We want to allow this person to opperate as a DBA but deny ALL access to the HR database.

Can I restrict the access of one group member in the sysadmin role?

 

[ptheriault]

no, members of the sysadmin role can not have their access restricted.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[jmickael]

Is it possible to put the one user account in the HR database and "DENY ALL" permissions?

 

[ptheriault]

no that won't work. you will have to remove that person from the sysadmin role. The sysadmin has full contol over the instance.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

You can not deny rights to a member of the sysadmin role. Fixed server roles override all database permissions.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com