Post reply

Help with permissions for all databases

  • July 12, 2012 at 10:41 am

    #258974

    I've been tasked with finding the logins that have DBO or db_datawriter on all the databases across all my servers. I found sp_helpuser but I'm having trouble finding a good way to iterate through all the databases on an instance.

    Before writing or using a bevy of dynamic SQL I'd prefer to use something like sp_msForEachDB or even better sp_ForEachDB (Aaron Bertrand). Unfortunately I can't determine how to output the database name along with the results of the sp_helpuser command. I intend to insert the results into a temp table and cull the results.

    Does anyone know how to make this work?

    Thanks in advance for the help.

  • July 12, 2012 at 10:51 am

    #1512188

    typically, you want to insert into a temp table, so you get everything in a single table for ease of review.

    this seems to have worked for me:

    USE master;

    GO

    IF (SELECT OBJECT_ID('Tempdb.dbo.#TBHELPUSER')) IS NOT NULL

    DROP TABLE #TBHELPUSER

    CREATE TABLE #TBHELPUSER (

    [DATABASENAME] NVARCHAR(128) NULL,

    [USERNAME] NVARCHAR(66) NULL,

    [ROLENAME] NVARCHAR(20) NULL,

    [LOGINNAME] NVARCHAR(66) NULL,

    [DEFDBNAME] NVARCHAR(12) NULL,

    [DEFSCHEMANAME] NVARCHAR(56) NULL,

    [USERID] CHAR(10) NULL,

    [SID] VARBINARY(85) NULL)

    EXEC sp_MSforeachdb 'USE [?];

    INSERT INTO #TBHELPUSER(UserName,RoleName,LoginName,DefDBName,DefSchemaName,UserID,SID)

    EXEC sp_helpuser;

    UPDATE #TBHELPUSER SET [DATABASENAME] = ''?'' WHERE [DATABASENAME] IS NULL;'

    SELECT * FROM #TBHELPUSER;

    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!

  • July 12, 2012 at 11:04 am

    #1512197

    WOW thanks for the quick solution and reply. I can't believe it never dawned on me to go back and update the temp table with the database name after insert. DUH!

  • July 12, 2012 at 12:10 pm

    #1512230

    glad I could help a little bit Jerry; you probably had 99% of it, except for that last part, right?

    it's always the little things!

    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!

  • July 12, 2012 at 1:16 pm

    #1512273

    I did indeed!

Viewing 5 posts - 1 through 4 (of 4 total)

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