February 17, 2006 at 3:05 pm
When using Windows authentication to allow users to connect to the database and underlying account is used - in our case, NTAUTHORITY\NETWORK SERVICE. This user shows up in SSMS under DB\DBNAME\Security\User and the default schema is db_datareader. There are no "owned schemas" and no "role membership". The only permission granted is "connect"; but because this is the same user that was used to create the database, all users have dbo and their "effective permission" is everything.
How do I restrict them to some subset of permissions?
February 17, 2006 at 6:13 pm
Can you remove the NTAUTHORITY\NETWORK SERVICE group from logins and add other global user groups that you would be able to assign more granular permissions to based on the requirements of each groups required functions (i.e. Create Databases\Tables, issue DDL statements, etc..)
February 20, 2006 at 5:38 pm
sounds way too complicated to me for something as simple as basic security. It's constantly impressed on us how Windows authentication is the way to go; yet, if you create the DB that way, whoever is logged on is dbo, and can therefore do anything. I tried reverting to SQL authentication and creating the DB as sa, and then turning off SQL authentication and going back to Windows authentication; but the default logged on user can still change everything. This does not strike me as something that needs to be complicated. I always viewed Windows authentication as a pain in the rear; but finally convinced myself that it was more difficult because it was more secure - and therefore worth the trouble; but it seems like, from lack of a dozen responses, that nobody is doing it -- is everybody here using SQL authentication?
February 21, 2006 at 9:35 am
I'm not great with Windows Auth since most installations I've worked on actually were SQL Auth only, but I'm having to work under Windows Auth now myself and was thinking that using sp_changedbowner should allow me to change the created DBs to a different DBA, and therefore take DBO away from the orignial creators.
How did a datareader-only create a DB in the first place?
February 21, 2006 at 12:19 pm
February 21, 2006 at 12:42 pm
I did a quick test in my sandbox db and created an account to become the DBO "NewDBO". Then I created a datareader-only account tied to window's auth and sp_changedbowner 'NewDBO'. The NewDatareader account couldn't do anything to the Sandbox db becsides read data after that. So if you change the DBO for the database, you won't have to give up on windows authentication. And unless the datareader account actually has other permissions/roles that you haven't noticed, it should be effectively locked out of being able to change the db.
The most common mistake I see (generally in Dev/QA environments) is that the domain user accounts are associated with SA and therefore have DBO for everything. I'me dealing with that right now.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply