strange issue with is_member
-
Indeed, membership of sysadmins-role is tricky !!
It switches context to 'sa' at connect and run-time.
This clarifies the "dbo" !
A sysadmin is Lord and Master for the whole and complete, .... sqlserver instance !
Also tricky may be "builtin\administrators".
If "builtin\administrators" has not been removed from your logins, everyone that is member of the local-administrators group of the windows server is by default member of the sqlserver sysadmins !!
Thats why many of us drop that login at install-time, after they authorized the windows-service-account and the sysadmins-windows-group.
exec sp_helpsrvrolemember @srvrolename = 'sysadmin' -- =SA
go
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
and there is off course the IS_SRVROLEMEMBER ('sysadmin')
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Well it looks like the solution is to remove my users from the sysadmins server role.
I only recently added the users to this role, the reason being that I wanted the users to have permissions to run SETUSER so I could run jobs as another user ('batch'). If this screws up is_member then it's not going to be a satisfactory solution as my application uses is_member to determine the users permissions, menu layouts etc. As this was returning 0, in my application I wasn't seeing many of my menu items.
Looking at the SETUSER command in BOL I see that I can also run the command if my user is a member of the 'db_owner' group so I'll see if that works a little better.
-
maybe this may be a valid workaround :
help to tighten use of cmdshell or sp_start_job
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply