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 |
|
bournvita
Starting Member
2 Posts |
 Posted - 2006-04-05 : 07:16:10
|
| Hi there,Have multiple SQL servers, all of which users have access to via group membership (ie no accounts created on the machine itself).When they log on, they are able to see the jobs on the server (via a change to the sysjobs_view to include their roles) and edit, start, enable, etc. as usual.The problem is when they edit a jobstep, they only get the option of chosing databases which have guest/public access enabled - the ones where this is disabled dont show. The user has datareader rights in the database as per role rights (via AD domain) so it's not like he isnt allowed in there.Did some profiling and seems that SQL populates that dropdown list by calling the sp_get_jobstep_db_username stored proc for each databaseHad to do the change as below to get this job to return the databases in the system (rest of the stored proc is available in MSDB) - when the Enterprise Manager calls this the parameter @login_name is passed through as N'' - so it's not a valid user in the DB nor does this get checked and changed during execution. -- Make sure login name is never NULL -- The OR statement added to allowing access via groups to system IF (@login_name IS NULL) OR (@login_name = '') SELECT @login_name = SUSER_SNAME() IF (@login_name IS NULL) RETURN(1) -- FailureSo just want to see if any other people have had problems like this and if there is another way of getting this done that does not involve changing system stored procs? This was tested on SQL2000SP4 and the SP4cummulative hotfix released end of March, they all exhibit this when accessing with Windows Group rights. |
|
|
bournvita
Starting Member
2 Posts |
Posted - 2006-04-10 : 10:46:11
|
| Since no one is replying, what is the recommended way to set up access to a system using windows group membership, while allowing full job managability to a specific non-sa role? |
 |
|
|
|
|
|
Database dropdown list in jobstep for non-sa users