Powershell script to check if the db owner is 'sa'? Please help!!!!

  • Hi Guys,

    I am looking for a powershell script to check if the db owner of the sqlserver database is 'sa' or not?Can anyone help me or provide the link which can help me to do this.

    Thanks,

    Mithra

  • I know nothing about Powershell, but the SQL command you need is:

    SELECT suser_sname(owner_sid)

    FROM sys.databases d

    WHERE d.name = 'DBNAME'

    -- OR d.database_id = 'DB_ID';

    Use Powershell to execute this command, swapping out DBNAME or DB_ID as you see fit.

    EDIT:

    Alternatively, the stored-procedure version:

    CREATE PROCEDURE checkForSaOwnership ( @dbname VARCHAR(100) )

    AS BEGIN

    BEGIN TRY

    DECLARE @isSA BIT, @dbOwner VARCHAR(100)

    SET @dbOwner = (SELECT suser_sname(owner_sid) FROM sys.databases d WHERE d.name = @dbname)

    IF (LTRIM(RTRIM(@dbOwner))) = 'sa' BEGIN

    RETURN 1 END

    ELSE BEGIN

    RETURN 0 END

    END TRY

    BEGIN CATCH

    PRINT 'There was a problem executing the procedure. Did you pass in a valid DB name?'

    RETURN 0

    END CATCH

    END

    Then from Powershell, set a new variable = EXEC checkForSaOwnership @dbname = 'DBNAME'.

    Return value of 1 = yes, owned by SA.

    Return value of 0 = no.

    ---

    Note to developers:
    CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
    So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

    Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
    My blog: http://uksqldba.blogspot.com
    Visit http://www.DerekColley.co.uk to find out more about me.

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

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