Software

Ask something
Question Locked

Permissions to access SQL Enterprise manager

0 helpful
5 replies

by jacques.gordon 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi, I've slowly been trimming unnessecary admin perms from our AD domain.

We occasionally have outside contractors come and work on our sql databases.

If I take their admin perms away they can't see our DBs in SQL Enterprise manager. I don't really want to give them full admin perms.

What else can I do to give them access to the enterprise manager without giving them full admin perms?

5 replies

by tony hopkinson

will work, a large number of options will be denied/disabled/invisible depending on how clever MS were when they wrote it.

by jacques.gordon

So are there any out of the box perms in AD I can use like backup operators or such or will I have to custom make one?

If so what perms will they need to what?

by tony hopkinson

What permissions depends on waht they need to do.

Create database is one, create table, alter tables if they are it the owner.

The one you really want to watch is grant permissions.

For instance if you gave them their own database, they could have full access within it as they are the owner.
An other database then none of just select... etc.

Roles tend to be broad, and there are some implicit permisisons attached to them that you might not want to be global about.

If it were me I'd create a user Contractor in the db, map their login and start with public on thoe databases they are allowed to look at.
After that it's what they says they want and what you say they need.

by cmiller5400

On the authentication mode. If it is set to "Windows Authentication Mode" then SQL server uses the AD accounts to grant permissions depending on which groups are added to the roles in SQL Server. By default, "BUILTIN\Administrators" is a member of the "System Administrators" role in SQL Server. So if a user/ad group is part of the machine's administrator's group, they will have full access to the SQL server. You also can specify which accounts/groups have access at the database level as well. You can make them members of the db_owners role in the database, and that gives them access to only that specific database.

If "Mixed Mode" is enabled, you also have the ability to login as an account that is set up in SQL server (such as SA) as well as the windows authentication.

This discussion is closed to new replies.