SysAdmin Server Role

[JD1866DSC]

Hi,

I'm trying to secure a sql 2005 server and setup appropriate logins and permissions.

I can create a server login and map them to the databases I want.
But how do I prevent the users granting themselves sysadmin rights? At the moment I haven't added them to any server role but they can just login go to security and properties under their login name and add to sysadmin.

Basically I want them to be able to perform ddl and dml on certain databases only

 

[ptheriault]

If they can do that then they are already sysadmins.

Are these windows accounts?
Are they members of the Server Admin group?
Have you left the built-in Admin account mapped to sysadmin?

When you create a user the only role they are a member of is Public. The only thing a user can change about their account is the password.

- Paul

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

 

[JD1866DSC]

Thanks for advice Paul

Yes they're windws accounts. I've noticed its just one user now.

I removed BUILTIN\Administrators Login but I've noticed that a group that this user belongs to is within the server admin group ie under groups in computer management on the box.
However he needs to administer the server just not the SQL side of things.

 

[ptheriault]

By removing Builtin admin he will no longer be an 'sa' on any of the sql instances on that server. IMHO, you should always remove built-in admin for that very reason you stated. There will always be users you want as server admins but not SQL admins.

- Paul

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