July 26, 2012 at 11:30 pm
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
July 27, 2012 at 3:13 am
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: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