Post reply

Script to identify fixed database roles for a Login in all Databases

  • February 10, 2012 at 8:18 am

    #251355

    Hi,

    I need a script to identify fixed database roles for a specific User in all Databases.

    I tried the following and get an error"

    DECLARE @DBuser_sql VARCHAR(4000)

    DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), Authentication_type VARCHAR(250),AssociatedRole VARCHAR(200))

    SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType, CASE a.authentication_type

    WHEN 0 THEN ''No Authencication''

    WHEN 1 THEN ''Uncontained User - Instance Level''

    WHEN 2 THEN ''Contained User - Database Level''

    WHEN 3 THEN ''Windows Login User'' END As AuthenticationType,

    USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a

    RIGHT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id

    WHERE a.sid IS NOT NULL AND a.type NOT IN (''C'')

    AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND a.name NOT LIKE ''NT%''

    AND a.name NOT IN (''public'',''dbo'',''guest'')

    AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY DBName'

    INSERT @DBuser_table

    EXEC sp_MSforeachdb @command1=@dbuser_sql

    SELECT * FROM @DBuser_table ORDER BY DBName

    Msg 207, Level 16, State 1, Line 1

    Invalid column name 'authentication_type'.

    Any help would be greatly appreciated.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • February 10, 2012 at 9:07 am

    #1445641

    well the aliased table a in your query is sys.database_principals , and that does not have a column named [authentication_type ],

    the only dmv i could find with that exact column name is [sys.dm_cryptographic_provider_properties], which is not what yo are after.

    what column should you be trying to use the case statement on?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply