March 17, 2006 at 2:09 pm
Good day,
I have been tasked with defining a best practice for securing the sql server(s). This is a two part question.
1. Where may I find any documents about the best way to secure a SQL server, either mixed mode or windows mode authentication?
2. How can I find out what users have what access to all databases? I want to know that domain\user1 has access to database1, and database 3, domain\groupA has access to database2 and database3, SQLUser3 has access to database2, and that userSA has access to all
Please help!
Cory
PS - so far, I have come up with this:
sp_msforeachdb "select l.name, '?' from master..syslogins l inner join sysusers u on u.sid = l.sid"
however, this only seems to get me users, not groups, and no SQLUsers.
-- Cory
March 17, 2006 at 2:45 pm
1. no best way, depends on your needs. Recommendation is to use Windows and manage with groups in AD. Less admnistration.
2. No good way. Use a few queries and I'd pull them together in some temp table and then query that.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 20, 2006 at 3:56 am
This maight help. Bare in mind that Windows security should be utilised above Mixed mode! Windows/SQL security can be defined by the following list (which I found somewhere on the net!),:
1. Users in each domain are assigned to Windows global groups.
2. The Windows global groups from the various domains are placed into a Windows local group.
3. The Windows local group is granted rights to log in to SQL Server 2000.
4. The Windows local group is granted access rights to the appropriate databases. This Windows local group may not be the same one as was used for granting login rights in Step 3. Therefore, Steps 1 and 2 are often repeated to group the users by access permissions required.
5. The Windows local group is assigned permissions on the specific database objects.
March 20, 2006 at 8:06 am
As others have said for security it all depends upon your environment. You may have a 3rd party applicaiton that may need SA rights or some other such specific circumstance.
As for the users, Brian Kelley posted about that on his blog last week. Perhaps he has made some progress that may not have made it back to the blog yet. http://sqlservercentral.com/cs/blogs/brian_kelley/default.aspx
-Luke.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply