Showing posts with label authenticate. Show all posts
Showing posts with label authenticate. Show all posts

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...