Script User Rights

Is there an easy way to capture existing user rights from a SQL Server
instance in a scripted format and simply run the script in a new SQL Server
instance to set all of the user privileges? Thank you.Hi ,
You could use the Enterprise Manager to Script user Rights , Right click on
the database Click on all tasks -> Generate SQL Scripts -> goto Last Tab and
look at the security options.
Hope this helps
Cheers
Vishal Gandhi
"CR" wrote:

> Is there an easy way to capture existing user rights from a SQL Server
> instance in a scripted format and simply run the script in a new SQL Serve
r
> instance to set all of the user privileges? Thank you.
>
>|||Thanks. However, I get "[SQL-DMO]CreateFile error on
"servername.databasename.DP1' error when I try to generate script?
"Vishal Gandhi" <VishalGandhi@.discussions.microsoft.com> wrote in message
news:0C04E96C-CDD9-4F54-B5CA-C213CAD97127@.microsoft.com...
> Hi ,
> You could use the Enterprise Manager to Script user Rights , Right click
on
> the database Click on all tasks -> Generate SQL Scripts -> goto Last Tab
and[vbcol=seagreen]
> look at the security options.
> Hope this helps
> Cheers
> Vishal Gandhi
>
> "CR" wrote:
>
Server[vbcol=seagreen]

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)