April 17, 2024 at 6:46 pm
Hi I want to create a copy of a DB on the same server. The account that creates the copy DB should NOT be able to read from the original DB but have full rights to the copy DB.
Cannot use a snapshot, Changes will be made to the copy and the copy will be backed up.
There are some excellent solutions here https://www.sommarskog.se/grantperm-appendix.html
I need to keep as much of the logic in script files rather than SPs, agent jobs etc. Although I've added a new SP dbo.sp_MakeDBO_TstSVC
Setup script to create user: "tstSVC" and a LiveDB: "PListDB". The user tstSVC has only rights to Backup LiveDB and Create new DBs.
use master
go
CREATE LOGIN [tstSVC] WITH PASSWORD=N'tstSVC', DEFAULT_DATABASE=[master], CHECK_POLICY=OFF
GRANT CREATE ANY DATABASE TO [tstSVC]
GO
CREATE DATABASE PListDB
GO
USE PListDB
GO
-- Create a table
SELECT top 10 name as colname, id INTO dbo.somedata from syscolumns
GO
CREATE USER [PLAdmin] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
ALTER ROLE [db_owner] ADD MEMBER [PLAdmin]
CREATE USER [tstSVC] FOR LOGIN [tstSVC]
ALTER ROLE [db_backupoperator] ADD MEMBER [tstSVC]
GO
CREATE PROC dbo.sp_MakeDBO_TstSVC
-- Stored proc to disable all triggers and make tstSVC dbo role
WITH EXECUTE as 'PLAdmin'
AS
if db_name() = 'PListCOPY' -- Its the copy don't run on the Live
BEGIN
ALTER ROLE [db_owner] ADD MEMBER tstSVC;
EXEC sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all';
disable trigger all on DATABASE;
END
GO
GRANT EXECUTE ON [dbo].sp_MakeDBO_TstSVC TO [tstSVC]
GO
use master
go
Code to make the copy: RUN this as tstSVC
--EXECUTE AS Login = 'tstSVC'
GO
use master
go
-- STEP 1: Backup LiveDB
BACKUP DATABASE PListDB TO DISK = N'c:\temp\Plist.bak' WITH COPY_ONLY, NOFORMAT, INIT, NAME = N'Product List DB', SKIP, NOREWIND, NOUNLOAD, STATS = 100
GO
-- STEP 2: Drop CopyDB
if exists (select * from sys.databases where name = 'PListCOPY')
BEGIN
-- Make tstSVC db_owner in case it fell over last time without making it dbo
EXEC PListCOPY.dbo.sp_MakeDBO_TstSVC
ALTER DATABASE PListCOPY SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE PListCOPY;
END
GO
-- STEP 3: Make CopyDB
RESTORE DATABASE PListCOPY FROM DISK = N'C:\temp\Plist.bak' WITH FILE = 1, MOVE N'PListDB' TO N'C:\Temp\PListCopy.mdf', MOVE N'PListDB_log' TO N'C:\Temp\PListCopy_log.ldf', NOUNLOAD, REPLACE, STATS = 100
GO
--STEP 4: Make TstSVC have full rights to Copy DB
exec PListCOPY.dbo.sp_MakeDBO_TstSVC
GO
-- This will work TstSVC should be able to do anyhting with the copy
UPDATE PListCOPY.dbo.somedata SET colname = UPPER(colName)
-- These fail as it should
SELECT * FROM PListDB.dbo.somedata
UPDATE PListDB.dbo.somedata SET colname = UPPER(colName)
The owner of the PListCopy DB was tstSVC as this user restored the DB but it has no rights. What can a Database Owner do?
What are the probs with this approach. Concerned if Step 3 falls over tstSVC would not be able to drop the PListCopy because it's not dbo yet. Will this fall over in the real world
Thanks
P.S. My Original SP had "WITH EXECUTE as owner" this is the owner of the module. How do I find the owner of the SP? The owner of its schema "dbo" is dbo. To avoid confusion here I created a user, without logon: PLAdmin and made it member of db_owner.
April 17, 2024 at 8:47 pm
With regards to your "what can a database owner" do, google is your friend there. But to summarize, DBO can do anything on the database level. You can access and modify all objects and data as well as do backups/restores and get into the dangerous side of things like dropping the database.
It can't do instance level things like create new logins or shutdown the instance, but it is still a bit risky.
My opinion (and approach) is that ONLY DBA's should be doing database restores or automated systems for testing backups. End users should NOT have dbo access and should NOT be doing restores unless you are comfortable with the "oh crap" moments. What if they restore multiple times with multiple locations? 10 copies of the database means 10 times the disk usage. Or worse - what if they just make a new database with a size of 100 TB?
Giving end users too much access is likely to get abused at some point either intentionally or accidentally.
My approach is that I would create the logins on the instance for the users who can access copy but not live, then create a script to create the users after a refresh with the appropriate access and have that as part of my "refresh" script. I have multiple "restore" scripts for moving data from live to test so end users can test against near-live data without impacting production in my system. One problem that comes up is we do this with a new instance of SQL and don't restore system DB's. This means that logins don't always have the same SID (too lazy to fix it after the fact and the problem is easy to script-fix), so I have a script to map the user to a login post refresh.
Just my 2 cents... not sure what anyone else would think, but that'd be my concern.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 17, 2024 at 10:13 pm
Thanks for replying Brian - I didn't explain well
I'm not testing backup/restore. This is part of a Monthly Automated Release Process. "tstSVC" is a domain account the release process runs under. The process does:
It would be easier to have this Release process run on another dedicated instance, where the tstSVC account is a Sysadmin. All this "Release" instance would do is produce the ProductsDB i.e. would only be used one day a month. Seems a waste of a SQL licence.
To save costs wanted to do the Release on same Instance hosting the Live DB but protect the Live PList DB from being erroneously updated/selected from/Dropped. This could happen in one of the scripts in step 3, but by not allowing the tstSVC account rights I will avoid this.
You are right the Release process could go rogue and make many backups and create loads of DBs, but couldn't drop any DBs apart from the PListCopy?
I'm confused about, if I run this...
Select suser_sname(owner_sid) as owner from sys.databases where name = 'PListCopy'
When I looked at properties of PListCOPY it was created by tstSVC (restored) and its "owner" was tstSVC, however tstSVC has only the original backup rights.
April 19, 2024 at 3:16 pm
I think I misrepresented the "Database Owner" role and the database "owner". "Database Owner" is a role. The database "owner" is the person who created the database. Multiple people can be in the database owner role, but only 1 person can be the "owner" (aka creator) of the database.
But if licensing is a concern, (depending on your licensing) you could spin up a new instance on the same server and give that account the permissions you want. Advantages to this is that you can do additional testing (such as instance upgrades) and you can spin up and shut down the instance as you see fit. If this process only runs once per month, you can spin it up the day before, let things run and then shut things down when you are done. AND if/when you do updates or upgrades, you can run them on the test system to make sure it doesn't break your load.
Since it is for an automated process, I suspect that account password is secure and the users who COULD use it are limited, so the risk is low there.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 20, 2024 at 1:54 pm
Thanks Brain I mix the terms up - <<"owner" (aka creator)>> is clearer.
If I take 2 backups of PList
use master
go
RESTORE DATABASE PList_copy FROM Disk = N'c:\temp\PList_tstSVCEXists.bak' ........
GO
use PList_copy
go
select [name] AS DBName, suser_sname( owner_sid ) AS Owner , USER_NAME() as [User_Name()], sUSER_NAME() as [sUSER_NAME()] FROM sys.databases where [name] = DB_NAME();
DBName Owner User_Name() sUSER_NAME()
PList_copy tstSVC tstSVC tstSVC
I get same results from restoring PList_NoUsers.bak i.e. tstSVC is the Owner of the db but not dbo or member of the db_owner role. I thought the Owner was always going to be dbo.
Playing around...
set nocount on
-- Loggin as SA
select [name] AS DBName, suser_sname( owner_sid ) AS Owner , USER_NAME() as [User_Name()], sUSER_NAME() as [sUSER_NAME()] FROM sys.databases where [name] = 'PList_copy';
DBName Owner User_Name() sUSER_NAME()
PList_copy tstSVC dbo sa
-- Set owner from tstSVC to sa - this works
ALTER AUTHORIZATION ON DATABASE::PList_copy TO sa
select [name] AS DBName, suser_sname( owner_sid ) AS Owner , USER_NAME() as [User_Name()], sUSER_NAME() as [sUSER_NAME()] FROM sys.databases where [name] = 'PList_copy';
DBName Owner User_Name() sUSER_NAME()
PList_copy sa dbo sa
-- Set owner from sa to tstSVC- FAILs
ALTER AUTHORIZATION ON DATABASE::PList_copy TO tstSVC
Msg 15110, Level 16, State 1, Line 14
The proposed new database owner is already a user or aliased in the database.
Since it is for an automated process, I suspect that account password is secure and the users who COULD use it are limited, so the risk is low there.
Risk is the scripts might mix up PList and PList_copy in their SELECT queries. This would be a very easy mistake to make. That's why I wanted tstSVC not even to have read rights to PList.
I'm concerned on my approach that if the restore to PList_Copy failed and left PList_copy in a state of Limbo tstSVC wouldn't have rights to drop PList_Copy because dbo.sp_MakeDBO_TstSVC hadn't been called.
Unless this is an unfounded worry?
I'm tempted to try the separate instance on SQLExpress, PList is <10GB and see what performance is like.
April 24, 2024 at 9:36 pm
Bear in mind that the user who owns the database maps in via the dbo database user and can do all that members of db_owner role can do
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply