August 16, 2018 at 5:35 am
I can see the user name, but do not know by which 'group' the user gets his/her permissions.
The user has only read access.
I would like to see from within SSMS what the group is the user belongs to.
I would like to see which permission the user has in SSMS.
The user has only limited permissions and can not access all security items.
We have no control over the 'system', 'windows' on that specific machine.
Ben
August 16, 2018 at 1:33 pm
ben.brugman - Thursday, August 16, 2018 5:35 AMI can see the user name, but do not know by which 'group' the user gets his/her permissions.The user has only read access.
I would like to see from within SSMS what the group is the user belongs to.
I would like to see which permission the user has in SSMS.
The user has only limited permissions and can not access all security items.
We have no control over the 'system', 'windows' on that specific machine.Ben
Do you mean Windows Groups and just SSMS by clicking and not using any t-sql?
You would need to use t-sql for Windows Groups.
Sue
August 16, 2018 at 3:08 pm
Sue_H - Thursday, August 16, 2018 1:33 PMDo you mean Windows Groups and just SSMS by clicking and not using any t-sql?You would need to use t-sql for Windows Groups.
Sue
T-sql would be excellent.
Problem, a user which normally could do all sort of things was not allowed to make changes, because of read-only restrictions. Stupid Me, that was because the user was logged on to the mirrored database and not to the actual database.
I did not notice that. (It's so obvious that such a thing is automatically dismissed).
Because access was restricted for that user (and I didn't have an account to that system), I thought that having a view into the permissions of that user, that this would give some insight. Hence my question.
Problem solved.
But if there is a T-sql which shows the user (and group(s)) and shows the permissions that would be nice. But remember the account has only limited access and not other account is available. In this instance the solution was simple, sorry to have missed such an obvious situation.
Thanks for your time and attention.
Ben
August 16, 2018 at 3:43 pm
ben.brugman - Thursday, August 16, 2018 3:08 PMT-sql would be excellent.Problem, a user which normally could do all sort of things was not allowed to make changes, because of read-only restrictions. Stupid Me, that was because the user was logged on to the mirrored database and not to the actual database.
I did not notice that. (It's so obvious that such a thing is automatically dismissed).
Because access was restricted for that user (and I didn't have an account to that system), I thought that having a view into the permissions of that user, that this would give some insight. Hence my question.Problem solved.
But if there is a T-sql which shows the user (and group(s)) and shows the permissions that would be nice. But remember the account has only limited access and not other account is available. In this instance the solution was simple, sorry to have missed such an obvious situation.Thanks for your time and attention.
Ben
If it's any consolation, just about everyone gets hit with the permissions issue when the user is different Windows groups. Or server/database roles.
You would want to get familiar xp_logininfo for windows groups. A couple of ways to use it would be ---get all the windows groups for a Windows Login
EXEC xp_logininfo 'Domain\LoginName', 'all';
--get all the group members for a windows group
EXEC xp_logininfo 'Domain\GroupName','members';
That usually gets you all the info you need. But sometimes you need to see more than that such as any possible group they may be part of or any role. The roles being the important one. At the server level, you can impersonate the user and check sys.logins to see other groups they may be in but those don't necessarily have access to SQL Server. I use it more for the roles and kind of ignore the rest since you can get that groups that have access that they are members of. Adding a lot of other groups can be confusing so I mainly roles - you can filter that if you want. You would use something like:
EXECUTE AS LOGIN = 'Domain\LoginName';
SELECT * FROM sys.login_token;
REVERT
Pay attention to the roles and you should at least see membership in the public role since everyone is a member of public.
You can do the same thing at the database level to check the roles a user is a member of in a database. You just need to execute as user (instead of login) and then check the user_tokens instead of login_tokens. So change to the database execute something like:EXECUTE AS USER = 'Domain\LoginName';
SELECT * FROM sys.user_token;
REVERT;
You can of course do more with joins on the token views and get more information. Hopefully those help get you started on some things to track down group and role membership a bit easier.
Sue
August 20, 2018 at 3:03 am
Thanks,
Ben
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply