asked
Stuck with TSQL..
Hi everyone hope some one could point me in the right direction with this SQL query --Script to find database users and roles assigned DECLARE @DBuser_sql VARCHAR(4000) DECLARE @DBuser_table TABLE (Servername VARCHAR(200),DBName VARCHAR(200),UserName VARCHAR(250), LoginType VARCHAR(500), CreateDate DATETIME , AssociatedRole VARCHAR(200)) SET @DBuser_sql='SELECT @@Servername AS Servername, ''?'' AS DBName, a.name AS Name, a.type_desc AS LoginType,a.create_date as CreateDate,USER_NAME(b.role_principal_id) AS AssociatedRole FROM [?].sys.database_principals a LEFT OUTER JOIN [?].sys.database_role_members b ON a.principal_id=b.member_principal_id WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') AND a.name not like ''%ASP%'' AND b.role_principal_id IS NOT NULL ORDER BY Name' INSERT @DBuser_table EXEC sp_MSforeachdb @command1=@dbuser_sql SELECT * FROM @DBuser_table ORDER BY DBNam Want to know how query should be in order to filter out on AssocaitedRole column.
This site runs on votes. Please indicate all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
If you want to filter by role name, place the USER_NAME(b.role_principal_id) into the where clause. For example, here is a filter on db_datawriter and db_backupoperator: and USER_NAME(b.role_principal_id) not in (''db_datawriter'', ''db_backupoperator'')
Another thing to note when using sp_msforeachdb (beside the fact that it sometimes misses databases -
http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/29/a-more-reliable-and-more-flexible-sp-msforeachdb.aspx) is that some functions are scoped to the current database. using select * from [?].dbo.table_name will get you the table contents from the [?] database, but select object_name(object_id) from [?].dbo.table_name will get you the object name of the object_id from the database you were in when you executed sp_msforeachdb, not the [?] database. If you want to be sure all of the function calls in sp_msforeachdb run in the [?] database, add a USE [?] statement to the beginning of your query.
AssociatedRole is an alias. You can't directly filter based on an alias within a straight T-SQL statement. So you can filter using the base column, role_principal_id, or you need to build a derived table. To do that, you put your initial SELECT statement inside parenthesis and then treat it like a table. Something along the lines of: SELECT * FROM (SELECT @@Servername AS Servername, ''?'' AS DBName, a.name AS Name, a.type_desc AS LoginType, a.create_date as CreateDate, USER_NAME(b.role_principal_id) AS AssociatedRole FROM [?].sys.database_principals a 5.LEFT OUTER JOIN [?].sys.database_role_members b ON a.principal_id=b.member_principal_id WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') 10.AND a.name not like ''%ASP%'' AND b.role_principal_id IS NOT NULL ORDER BY Name) AS dt WHERE dt.AssociatedRole = 'some value' I understand that you're trying to run this across all databases, but it'll be a lot easier for you if you build the query and get it to run on a single database, then figure out how to run it on all databases. Right now you're dealing with multiple variables that could mess up the query. Just a side note, 'a' and 'b' and I'll assume 'c' and 'd' are really awful ways to alias tables. It's so unclear which table is being referred to. If you go to another query a given table could be 'b' when it was 'a', further reducing clarity. Instead, I'd suggest something like 'dp' for database_principals. Then it'll be 'dp' in every query. Then you can have 'drm' for database_role_members, etc.
16 People are following this question.
