Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DavidMcD
Starting Member
3 Posts |
 Posted - 2005-07-05 : 11:08:38
|
| I have an application that uses sp_HelpUser, with a parameter calculated from suser_sname(), to determine the SQL Server roles that the current user is in. I then implement "front end security" by disabling and enabling buttons depending upon their roles.This works fine when the users (in this case derived from NT users) are explicitly added. However, I wish to base all users for the database on NT groups.When all SQL users are based on NT groups and sp_HelpUser is run for the current user, the function returns,"The name supplied (DOMAIN_Name\User_Name) is not a user, role, or aliased login." This of course makes sense because DOMAIN_Name\User_Name is no longer a user, but is a member of a NT group that is.How can I return the roles for the current user, without explicitly creating the SQL user?Thanks,David |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-07-05 : 11:31:58
|
| if a user is implicitly granted db access via a fixed server role such as sysadmin, then sp_helpuser won't be aware of that user. If you want to use your current functionality, sounds like you'll need to explicitly call sp_grantdbaccess and sp_addrolemember for each User/Database combination.For security purposes that is the way to go anyway. Users should not be granted general access to sql server via fixed server roles like sysadmin.Be One with the OptimizerTG |
 |
|
|
DavidMcD
Starting Member
3 Posts |
Posted - 2005-07-05 : 13:00:34
|
| Hi TG,Thanks for your response.The roles in this instance are database specific roles that were created for the application. I would like the only members of these roles to be NT Groups so that once the user is added to the NT group there is no work to do (or stored procedures to run) to administer the SQL Server.I thought Microsoft encouraged this as the quickest, lowest maintenance approach? (Running sp_grantdbaccess and sp_addrolemember for each User/Database combination is not exactly low maintenance.)If the approach I'm using is "Microsoft preferred" I'm surprised there is not an easy to use, system stored procedure to retreive the information I need.Any thoughts?David |
|
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 2005-07-05 : 13:26:59
|
Hi,Users should be associated with "user-defined roles", and those roles should have the appropriate privileges - just like you are doing now. A specific user should be "insultated" from specific privileges via an association with one or more roles. I've got to disagree with the recommendation that users be granted specific privileges.To answer your question though: I believe that if you execute "sp_heluser user_name" - with the domain name omitted, you will get a result. It may not be the result you expect though.Take a look at the code behind "sp_helpuser". sp_helptext 'sp_helpuser" This may give you some insight into the catalog relationships that support users, groups, and roles. I don't have time right now to give you code, but if you get stuck, post again and I'll help if I can. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-07-05 : 13:49:03
|
| >>I believe that if you execute "sp_heluser user_name" - with the domain name omitted, you will get a resultI don't think that's right. If the NT User (of an NT Group) was never added via sp_addlogin or sp_grantlogin then that user won't exist in sysLogins, sysUsers (or sysmembers). After the NT Group is added and granted db access, Sql Server will correctly handle security for all NT Group members but sp_helpUser won't be able to return role information for NT User.Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
sp_HelpUser