January 21, 2010 at 4:57 am
I would like the ability to give new contractors read-only access to an SQl Server so that they can look at all the configurations/settings, databases, tables, diagrams, data, etc., but cannot change anything.
It would be nice if there were a fixed server role of "sysguest" or something that did this, but I don't think there is.
The way I plan to do it is to grant the contractor's login access to all databases on the server and assign the associated user the "db_datareader" role in all databases. Do you think this is the best way of accomplishing this?
Cheers,
Steve
January 21, 2010 at 8:42 am
It would be easier to have a Windows Group setup and then make all the contractors a member of that Group. Within SQL Server, give that group access to the databases that you want, and assign db_datareader to the group.
That way, it is easy when a contractor leaves, you have have to do nothing to remove them from the database.
Andrew SQLDBA
March 8, 2010 at 11:43 am
Hi Andrew, thanks for the reply.
That's what I did in the end. I created 6 NT security groups, as follows:
G_SQL_PROD_SYSADMIN G_SQL_PROD_READONLY
G_SQL_DEV_SYSADMIN G_SQL_DEV_READONLY
G_SQL_UAT_SYSADMIN G_SQL_UAT_READONLY
SQL logins for the G_SQL_PROD_SYSADMIN and G_SQL_PROD_READONLY groups were added to the Production SQL Servers; G_SQL_PROD_SYSADMIN was granted sysadmin, and G_SQL_PROD_READONLY was assigned to the db_datareader in each db.
And similarly for the DEV and UAT SQL Servers.
March 11, 2010 at 7:21 am
I'm not sure about SS 2005, but in SS 2000 any valid Logins would be able to see any of the databases with the permissions of the 'guest' user. That is why I routinely assign the guest user to the roles db_denydatareader and db_denydatawriter.
Am currently moving to SS 2008 but have not checked it out there yet.
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply