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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-01 : 23:26:27
|
| Kevin writes "How does NT Authentication resolve to an SQL SUSER_SID value when there is both a single user account and a group account on SQL? I am trying to grant limited access to DTS packages to a group of developers (I'm not crazy about DTS, but we're committed to trying it out - and yes, I know DTS is designed to be an admin function, but I'm messing with it anyway.) In trying to find a way through security, I attempted to assign a Windows NT Group login as the 'owner' of a specific DTS package. (The idea, is that any member of the group can edit and run the package, so I don't have to be at work at 6AM when some of these die-hards get in.)The method failed. The SUSER_SID Field in MSDB.sysDTSPackages matches the one assigned to the group. However, the DTS security model appears to check ONLY the current UserID. Since these users have valid NT logins on SQL in addition to belonging to the NT Group, they are being validated on their single user login, which has a different SUSER_SID value, hence DTS security fails to give them editing rights, even though they belong to the group that is the owner of the package. This made me curious as to how exactly SUSER_ID gets assigned to an authenticated user, and what the priorites and defaults would be in the question I described. Obviously, Database level user validation must ask for group information if the user's login is not found in its user list....but I have been unable to locate any documentation accurately describing in detail what the authentication and validation processes are and how they work. I wonder if removing the single user account would force the users SUSER_SID to resolve to the Group value, but I thought I would ask you first.By the way, I did find that I could rewrite the security by adding an IS_MEMBER check for a specified role in the MSDB.sp_addDTSPackage and sp_getDTSPackage procedures...but this makes everybody nervous (including me) and although it worked I do not intend to go down the path of customizing system stored procedures." |
|
|
|
|
|
|
|
NT Authentication