August 2, 2011 at 10:40 am
I have about 30 archived databases that is setup like Read_only.
Now I would like to add a user to access these 30 databases, but because it is read only, it cannot be added.
Then I have to go to each database, and make it to non-readonly, then I can add the user, then make it read only again. I have to do this for 30 databases.
Is there a quick and easy way to do this?
Thanks
August 2, 2011 at 10:47 am
Do it smart...
Make a windows group. Grant access to the dbs to the group. Then add the users to the group as needed.
Doing that manually or through a script won't make much difference time-wise.
Couple hints to build a cursor if you chose that route :
SELECT * FROM sys.databases WHERE is_read_only = 1
ALTER DATABASE [PROD-FORDIA] SET READ_ONLY ...
grant...
alter...
August 2, 2011 at 10:53 am
Yes, I think a windows group is a smart way.
But it seems these users have been already setup like that in the existing databases and they are passed by from another database on another server, so it is kind of tricky now to change it.
I think I will write a script like you mentioned using cursor.
I would like try first the EXEC sp_MSForEachDB , but it will go loop each db on the server, what I want is only databases started with Archive_, like Archive_082001, Archive_092001. etc.
Is there a way to filter that useing sp_MSForEachDB?
Thanks
August 2, 2011 at 10:57 am
msforeach db is just a loop through the list of dbs. This is only 5 lines of code to type.
You can add another filter on dbname on top of read-only in my query.
August 2, 2011 at 10:58 am
Just to show how to filter... and how overly complicated it can be in ForEachDB >>
SET STATISTICS IO, TIME OFF
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS ON
GO
USE [master]
GO
IF OBJECT_ID('dbo.spaceused', 'U') IS NULL
BEGIN
CREATE TABLE dbo.spaceused (
DbName sysname DEFAULT(''),
tblName sysname,
Row_count INT ,
Reserved VARCHAR(50),
data VARCHAR(50) ,
index_size VARCHAR(50),
unused VARCHAR(50),
PRIMARY KEY CLUSTERED (DbName, tblName)
);
END
ELSE
BEGIN
--DROP TABLE dbo.spaceused
TRUNCATE TABLE dbo.spaceused
END
COMMIT
GO
DECLARE @Cmd VARCHAR(8000)
SET @Cmd = 'USE [?];
IF ''?'' NOT IN (''tempdb''
--, ''master'', ''model'', ''msdb''
)
BEGIN
--PRINT ''?''
DECLARE @InnerCmd VARCHAR(8000)
SET @InnerCmd = ''
EXEC sp_spaceused '''''' + CHAR(63) + ''''''''
INSERT INTO master.dbo.spaceused(tblName, Row_count,Reserved,data,index_size,unused)
EXEC sp_MSforeachtable @InnerCmd
UPDATE master.dbo.spaceused SET DbName = ''?'' WHERE DbName = ''''
END
'
--PRINT @Cmd
EXEC sp_MSforeachdb @Cmd
DELETE FROM dbo.spaceused WHERE Row_count = 0
SELECT
DbName
, tblName
, Row_count
, CONVERT(BIGINT, REPLACE(Reserved, ' KB', '')) / 1024 AS MB_Reserved
, CONVERT(BIGINT, REPLACE(data, ' KB', '')) / 1024 AS MB_data
, CONVERT(BIGINT, REPLACE(index_size, ' KB', '')) / 1024 AS MB_index_size
, CONVERT(BIGINT, REPLACE(unused, ' KB', '')) / 1024 AS MB_unused
FROM
dbo.spaceused
ORDER BY
DbName
, MB_Reserved DESC
, Row_count DESC
COMMIT
August 2, 2011 at 11:03 am
I missed a point, even I use windows group, when I add the group to the users to the Read_only databases, I still have to make it to not readonly, then add it, then change it back to Read only again.
Now actually I only have one new user to add to those databases. All other users have been copied from the original database users.
August 2, 2011 at 11:05 am
Well if the group exists already in the db, just add the user to the group in the AD and you're done.
Of course this all assumes windows authentication.
August 2, 2011 at 11:06 am
Just to show how to filter... and how overly complicated it can be in ForEachDB >>
So do you mean Cursor in this case is easier?
Thanks
August 2, 2011 at 11:08 am
The group is not in the AD though, now I think they use individual users instead of group is because each person in the group has different access to the dbs.
August 2, 2011 at 11:15 am
Make a new AD group call ArchiveRead or something like that.
Go through the pain of the setup once and then forget about it.
August 2, 2011 at 11:16 am
sqlfriends (8/2/2011)
Just to show how to filter... and how overly complicated it can be in ForEachDB >>So do you mean Cursor in this case is easier?
Thanks
You tell me. Took me 2 hours to write the code I posted (15 tries later).
Cursor, for admin work is really nice tool to have / use.
August 2, 2011 at 11:19 am
Thank you so much for the time you spent.
I just heard before is to avoid using Cursor, so now I understand in some way cursor is good for admin use.
August 2, 2011 at 11:25 am
sqlfriends (8/2/2011)
Thank you so much for the time you spent.I just heard before is to avoid using Cursor, so now I understand in some way cursor is good for admin use.
JUST for admin use. That would be true 99.9999999999% of the time. So I can leave you with that for the time being ;-).
August 2, 2011 at 12:59 pm
I have written a stored procedure like below in an admin database on the server,
but there are some errors:
CREATE PROCEDURE sp_AddUserToArchivedbs
@username varchar(20)
as
DECLARE @databasename nvarchar(80)
DECLARE @Mycursor CURSOR
SET @Mycursor = CURSOR FAST_FORWARD
FOR
Select name FROM sys.databases WHERE NAME LIKE 'Archive_%'
OPEN @Mycursor
FETCH NEXT FROM @Mycursor
INTO @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
USE master
ALTER DATABASE @databasename SET READ_WRITE
USE @databasename;
Go
IF EXISTS
(SELECT name FROM sys.database_principals WHERE type='r' AND name='Business_reader')
EXEC sp_addrolemember 'Business_reader', @username
ELSE
EXEC sp_addrolemember 'db_reader', @username
END
ALTER DATABASE @databasename SET READ_Only
Go
FETCH NEXT FROM @Mycursor
INTO @databasename
END
CLOSE @Mycursor
DEALLOCATE @Mycursor
GO
The errors are :
1.use database statement is not allowed in a procedure, function.
2. Incorrect syntaxt near '@databasename', expecting Audit...
2. must declare the scalor variable @username
3. Must declare the scalar variable "@Mycursor".
I'm new to programming, could you help me how to fix these errors
August 2, 2011 at 1:08 pm
You must use dynamic sql
SET @cmd = 'ALTER DATABASE [' + @Db + '] SET READ_WRITE'
EXEC (@cmd)
You can just concatenate everything and run in 1 batch per db.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply