Windows Groups to Support SQL Server 2005 Application Security

By: Jeremy Kadlec | Read Comments | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: More

Problem
Have you had an issue trying to find out how to assign rights for some of the new applications (SQL Server Integration Services, Full Text, OLAP, Express, Reporting Services, etc.) in SQL Server 2005?  If so, you are not alone.  With SQL Server 2005, some of the new security is assigned via Windows Groups that can be managed directly via Computer Manager.  Unfortunately, these are no where to be found or configured in SQL Server 2005 Management Studio (SSMS) in the same light as database engine permissions.

Solution
With security migrating away from direct management in SQL Server 2005 Management Studio, create a short cut on your desk to Computer Management in order to manage the new groups.  The new groups to support some of the new SQL Server 2005 applications are created as the application is installed on the SQL Server.  Below is a screen shot of Computer Management with some of the new Windows groups to support SQL Server 2005:

How can I add or remove windows accounts for these new groups?

To manage the groups, follow these steps:

1. Open Computer Management by clicking on Start | Control Panel | Administrative Tools | Computer Management
2. Once Computer Management loads, navigate to System Tools | Local Users and Groups | Groups
3. Select the group you would like to add or remove a login by double clicking on the group
4. Click the 'Add ' button and enter the login on the subsequent screen
5. To delete a login or group, click the 'Remove' button and review the subsequent screens

How can I assign SQL Server logins?

The SQL Server 2005 Windows groups use just domain or local accounts, so SQL Server logins are not an option.

Next Steps

• As you begin to deploy these new applications be sure to verify the Windows Group rights for these users and do not feel obligated to assign Windows Administrator rights by default.
• Since assigning rights at the Windows Group level may not be a portion of the typical developer or DBA daily tasks, be sure to coordinate with your Network or System Administrators as needed.
• Stay tuned for additional security options with these SQL Server 2005 applications.

Related Tips: More | Become a paid author

Last Update: 8/28/2006

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Try the free performance monitoring tool from Idera! Time-strapped database professionals use SQL Monitor to look after their servers. Try it online. What grade do you think your SQL Servers get? Find out with Edgewood's SQL Server Health Check starting at $995. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Demystify TempDB Performance and Manageability