Script for sa rights and db settings

1. I want to create a script to know who has sa rights.
2. I also want to create a script to montor some certain db settings such as
"Use qouted identifier" etc. and notify me if someone turns them off.
Do you know how?
Thanks,
Zrichsp_dboption master, 'quoted identifier'
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:CF5ED5AF-578C-4680-A70D-BB9AEE29D3FB@.microsoft.com...
> 1. I want to create a script to know who has sa rights.
> 2. I also want to create a script to montor some certain db settings such
> as
> "Use qouted identifier" etc. and notify me if someone turns them off.
> Do you know how?
> Thanks,
> Zrich|||There is no something "sa rights" SA is a special and built-in login which
has "sysadmin" role right.
You can list logins which has sysadmin right by using the following command
EXEC sp_helpsrvrolemember 'sysadmin'
--
Ekrem Ã?nsoy
"Zrich" <Zrich@.discussions.microsoft.com> wrote in message
news:CF5ED5AF-578C-4680-A70D-BB9AEE29D3FB@.microsoft.com...
> 1. I want to create a script to know who has sa rights.
> 2. I also want to create a script to montor some certain db settings such
> as
> "Use qouted identifier" etc. and notify me if someone turns them off.
> Do you know how?
> Thanks,
> Zrich|||Zrich,
Here is a script I use at work that includes some of the database options. I
only run it manually, but it should be easy enough to modify it to do what
you want (on database options).
DECLARE @.DB sysname
DECLARE @.SQL nvarchar(255)
if exists ( select * from tempdb..sysobjects where name LIKE
'#FileStats__%' ) drop table #FileStats
if exists ( select * from tempdb..sysobjects where name LIKE
'#DBoption__%' ) drop table #DBoption
CREATE TABLE #DBoption (OptName VARCHAR(35), OptValue VARCHAR(10))
CREATE TABLE #FileStats(
[FileId] INT,
[FileGroup] INT,
[TotalExtents] INT,
[UsedExtents] INT,
[Name] sysname,
[Filename] varchar(255)
)
DECLARE @.FileStats TABLE (
[FileId] INT
, [FileGroup] INT
, [TotalExtents] INT
, [UsedExtents] INT
, [Name] SYSNAME
, [Filename] VARCHAR(255)
, [AutoShrink] VARCHAR(10)
, [AutoClose] VARCHAR(10)
, [QuotedID] VARCHAR(10)
)
DECLARE cDatabases CURSOR FOR
SELECT QUOTENAME(sdb.name)
FROM master.dbo.sysdatabases sdb
WHERE status & 32 != 32
AND status & 64 != 64
AND status & 128 != 128
AND status & 256 != 256
AND status & 512 != 512
AND status & 1024 != 1024
AND status & 4096 != 4096
AND status & 32768 !=32768
OPEN cDatabases
FETCH FROM cDatabases INTO @.DB
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
DELETE FROM #FileStats
SET @.SQL = 'USE ' + @.DB + '; INSERT INTO #FileStats EXEC (''DBCC
SHOWFILESTATS'')'
EXEC (@.SQL)
UPDATE #FileStats SET name = @.DB
INSERT INTO @.FileStats([FileId], [FileGroup], [TotalExtents],
[UsedExtents], [Name], [Filename])
SELECT * FROM #FileStats
--
SET @.SQL = 'sp_dboption ' + @.DB + ', ''autoshrink'''
DELETE FROM #DBoption
INSERT INTO #DBoption
EXEC sp_executesql @.SQL
UPDATE @.FileStats
SET AutoShrink = (SELECT OptValue FROM #DBoption)
WHERE Name = @.DB
--
SET @.SQL = 'sp_dboption ' + @.DB + ', ''autoclose'''
DELETE FROM #DBoption
INSERT INTO #DBoption
EXEC sp_executesql @.SQL
UPDATE @.FileStats
SET AutoClose = (SELECT OptValue FROM #DBoption)
WHERE Name = @.DB
--
SET @.SQL = 'sp_dboption ' + @.DB + ', ''quoted identifier'''
DELETE FROM #DBoption
INSERT INTO #DBoption
EXEC sp_executesql @.SQL
UPDATE @.FileStats
SET QuotedID = (SELECT OptValue FROM #DBoption)
WHERE Name = @.DB
--
FETCH FROM cDatabases INTO @.DB
END
CLOSE cDatabases
DEALLOCATE cDatabases
SELECT
[Name]
,[TotalExtents]*64/1024. AS TotalExtInMB
,[UsedExtents]*64/1024. AS UsedExtInMB
,([TotalExtents] - [UsedExtents]) / 16. AS UnAllocExtInMB --* 64 / 1024. AS
UnAllocExtInMB
,CAST(FLOOR(ROUND([UsedExtents] * 100. / [TotalExtents], 0)) AS VARCHAR(3))
+ '%' AS Pct_Full
, Filename
, AutoShrink
, AutoClose
, QuotedID
FROM @.FileStats
ORDER BY TotalExtInMB DESC
DBCC sqlperf(logspace)