March 27, 2013 at 2:02 pm
Per msdn, there is a permissions restrictions where,,,
Permissions
--------------------------------------------------------------------------------
Any user can see their own user name, the system users, and the fixed database roles. To see other users, requires ALTER ANY USER, or a permission on the user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role.
Since I don't know who the db_users area, I need a query that I can run to see ALL db_users? Anyone know?
March 27, 2013 at 3:05 pm
Probably should have kept this with the other thread as it is related, but I have to ask; who is going to be running this procedure, normal users or DBAs?
March 27, 2013 at 3:10 pm
I debated keeping it to same thread. Only I will use it to produce a list and ultimately a report. Whether someone will ask me to run it all the time, remains to be heard, and doubtful. Do you know a way other than
SELECT name, type, type_desc FROM sys.database_principals
March 27, 2013 at 3:12 pm
Does this mean you don't see all the users when you run the query?
March 27, 2013 at 3:20 pm
correct. I see my own, I see a few other people's, some windows usernames, and I used to think that was all of them until I read this article...
http://msdn.microsoft.com/en-us/library/ms187328.aspx
where it says "Any user can see their own user name, the system users, a sql user without login, and the fixed database roles. To see other users, requires ALTER ANY USER, or a permission on the user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role. ". But, if I don't know what I don't know, I don't have a username to ALTER...
March 27, 2013 at 3:29 pm
Are you the DBA? If not, I would talk to your DBA.
March 27, 2013 at 3:32 pm
Lynn, would you please not reply if you don't have an answer? I am looking for an answer to the question of how to search system tables or views to return a result set of all db users of a database.
March 27, 2013 at 3:34 pm
KoldCoffee (3/27/2013)
Lynn, would you please not reply if you don't have an answer?
I have a possible answer, but I'm not going to give it to just anyone. If you aren't the DBA, then I encourage you to go talk to your DBA regarding this instead of trying to find ways around him or her.
March 27, 2013 at 3:36 pm
I suspected as much. No,I am not the dba but I talked to the dba. The dba doesn't know. The dba gave me this task.
March 27, 2013 at 3:40 pm
Your DBA can create a signed stored procedure that runs with necessary permissions to allow the procedure to return to the user that data requested. I don't have the necessary info handy but I can get to it at home. I will try to post more tonight but not making promises as I have my daughter this week.
March 27, 2013 at 3:42 pm
I will tell my dba. if you just show me a link where I can read about it I will be happy. Please enjoy your time with your daughter. I also have a daughter!
March 27, 2013 at 4:02 pm
The MSDN page you quoted above has the information you're asking for.
To see other users, requires ALTER ANY USER, or a permission on the user.
So, to view all the users, you either need the ALTER ANY USER permission or need to be in a role that grants that permission, (eg database owner, security admin, sysadmin) or someone with the required permissions need to create a procedure that runs the required query and needs to give it the EXECUTE AS OWNER clause and give you rights to run that procedure.
Without the permission, there's not much you can do, you can't grant yourself the required permissions and there's no secret escalation of privileges hacks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2013 at 4:12 pm
As I said, I don't know who the other users are so I can't alter permissions on user.
Any link will be appreciated. I would like to be clearly informed before I tell my dba that someone on the forum told me....and as I am apparently in an environment where there's still a lot to learn.
March 27, 2013 at 4:28 pm
KoldCoffee (3/27/2013)
As I said, I don't know who the other users are so I can't alter permissions on user.
You don't need to alter permissions on a user (and you won't be able to anyway, you won't have permissions to do that). You need to be granted the permission 'ALTER ANY USER'. As in...
GRANT ALTER ANY USER TO <database user name>
Now, you won't be able to do that, you can't grant permissions to yourself, so you will need to either ask your DBA to grant you that permission (if the extra permissions are acceptable and within the company's security policy) or ask your DBA to write a procedure that runs the required query against sys.database_principals, add the EXECUTE AS OWNER clause to that procedure and to grant you permission to execute it.
The link you referenced earlier (the MSDN page) is the best reference I know offhand.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2013 at 4:51 pm
Thank you!!!!!!
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply