Software

Question

Gravatar
0 Votes
Locked

Permissions to access SQL Enterprise manager

By Jacques.Gordon ·
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?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

gravatar
Collapse -

Any level of sqlserver access read onwards

by Tony Hopkinson In reply to Permissions to access SQL ...

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

gravatar
Collapse -

AD Perms

by Jacques.Gordon In reply to Any level of sqlserver ac ...

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?

gravatar
Collapse -

No idea whether sql server perms are in AD

by Tony Hopkinson In reply to AD Perms

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.

gravatar
Collapse -

The permissions location depends...

by cmiller5400 In reply to No idea whether sql serve ...

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.

Back to Software Forum

Start or search

Related Discussions

Related Forums