Monday, March 19, 2012

Issues setting up users to authenticate via NT group into MSSQL

I have a NT Group of users that need to connect to MSSQL db using integrated auth and run a stored proc. I have got this concept to work for individual windows users but not a group.

Does anyone have any ideas?

(Win XP, MSSQL 2000 SP3, Windows Group = "SQLUsers", member is "User2")

-- TSQL --

USE master
GO
sp_grantlogin 'pc\SQLUsers'
GO
sp_defaultdb 'pc\SQLUsers', 'theDB'
GO
USE theDB
GO
sp_grantdbaccess 'pc\SQLUsers', 'SQLUsers'
GO
GRANT EXECUTE ON [dbo].[uspThing] TO SQLUsers
GO

-- Trying to connect with OSQL.EXE:

> osql -d theDB -E
Login failed for user 'pc\User2'.

BUT....

runing it for the individual user:

--TSQL

USE master
GO
sp_grantlogin 'pc\User2'
GO
sp_defaultdb 'pc\User2', 'theDB'
GO
USE theDB
GO
sp_grantdbaccess 'pc\User2', 'SQLUsers'
GO
GRANT EXECUTE ON [dbo].[uspThing] TO SQLUsers
GO

-- now trying to connect with OSQL.EXE as User2:

>osql -d theDB -E
1> exec uspThing
2> go
3> data...

all is fine.....

I can't use the user method - to much admin... any ideas!?

PKOk, get this... because I was using "runas /user:User2 cmd.exe" sessions for testing the different users integrated security (osql -E -d dbName) etc the group changes I was making were not reflected until I logged out of the command session.

I have been officially driven mad by this one...

No comments:

Post a Comment