Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

can't view all db_users when select * from sys.databaseprincipals Expand / Collapse
Author
Message
Posted Wednesday, March 27, 2013 2:02 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 9:00 PM
Points: 680, Visits: 1,598
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?
Post #1436149
Posted Wednesday, March 27, 2013 3:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 20,734, Visits: 32,499
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1436178
Posted Wednesday, March 27, 2013 3:10 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 9:00 PM
Points: 680, Visits: 1,598
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
Post #1436182
Posted Wednesday, March 27, 2013 3:12 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 20,734, Visits: 32,499
Does this mean you don't see all the users when you run the query?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1436184
Posted Wednesday, March 27, 2013 3:20 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 9:00 PM
Points: 680, Visits: 1,598
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...
Post #1436187
Posted Wednesday, March 27, 2013 3:29 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 20,734, Visits: 32,499
Are you the DBA? If not, I would talk to your DBA.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1436191
Posted Wednesday, March 27, 2013 3:32 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 9:00 PM
Points: 680, Visits: 1,598
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.
Post #1436195
Posted Wednesday, March 27, 2013 3:34 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 20,734, Visits: 32,499
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1436196
Posted Wednesday, March 27, 2013 3:36 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 9:00 PM
Points: 680, Visits: 1,598
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.
Post #1436198
Posted Wednesday, March 27, 2013 3:40 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 20,734, Visits: 32,499
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1436199
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse