SQL Server 2012 :: Restore Database And Create Users Using Minimal Elevated Privilege
Apr 29, 2015
What I want to do is :
- restore a backup of a 3rd party database onto one of our servers
- this has no users that I can use
- there is some ETL processing so we're using Control-M to manage the process
- create a database user and grant it db_reader.
I'd like to do this without granting any users elevated privileges if possible.
What I've done so far is grant the Control-M user (this is a domain user) dbcreator rights and made it owner of our copy of the database that is being refreshed.
The refresh is completing, but Control-M is not able to log onto the database to create the user.
What is the best way to accomplish this task without granting the control-m user sysadmin rights?
Would I be able to do it if I used a SQL Agent job for the restore and user creation?
View 1 Replies
ADVERTISEMENT
Oct 12, 2015
I have a process that restores a production DB, overwriting the existing copy each night. I'd like to keep the solution "up" for as long as possible. And this'll be more important if I want to update it in the day (where there are more queries) too. The nature of queries thrown at the system is that there are about 20 per hour, it's underpinning a reporting system, it's not an OLTP system.
It seems to me I could restore the fresh DB copy into a holding DB, then rename it to the production DB name at the end of the process. The rename process should be pretty much instant.
But I need to think about detecting and waiting for queries to complete on the prod DB, before removing/demoting it (actually, I though to rename it, then reusing it as the next copy to update).
View 5 Replies
View Related
Jul 22, 2014
I have a requirement to allow a user to restore a database and then create database users and add them to the db_owner database role. The user must not have sysadmin rights on the server.
The database restore works ok by placing the user in the dbcreator role.
There is a stored procedure to create the database user and alter role membership, I want the user to execute the sp as a different, higher privilege account so as not to give the user underlying permission to create users in the database.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_create_db_users] Script Date: 22/07/2014 13:54:46 ******/
SET ANSI_NULLS ON
GO
[Code] ....
The user has execute permission on the stored procedure but keeps getting the error:
Msg 916, Level 14, State 1, Line 2
The server principal "Mydomainadmin1" is not able to access the database "Mydatabase" under the current security context.
Mydomainadmin1 has dbowner to Mydatabase and sysadmin rights for server. If the 'execute as' is changed to 'caller' and run by mydomainadmin1 it works so the issue is between the execute sp and the actual running of the procedure.
View 1 Replies
View Related
Nov 3, 2010
We use Netbackup for our SQL servers to backup and restore databases. I would like the service account used by Netbackup to have as limited permissions as possible. The account should be able to backup and restore a db without being able to read any of the content. Right now the account jobs fail if the service account is not in the sysadmin role.
I removed the account from sysadmin and limited it to dbcreator and public but the job fail.
How to setup an account so that people who know the service account password can't log in with that account and read db information?
View 9 Replies
View Related
May 31, 2014
I have a db to manage the creation of invoice number designed for a web application.
My problem is how to manage the concurrency when the users need to create an invoice number.
View 9 Replies
View Related
May 26, 2015
An old website I inherited uses sa to connect to SQL SessionState and had the details in the web.config. This is bad for security.The session state database is of -sstype "t" which is defined as:Temporary. Session state data is stored in the SQL Server tempdb database. Stored procedures for managing session state are installed in the SQL Server ASPState database. Data is not persisted if you restart SQL. This is the default.What kind of WIndows user, SQL Login, role and permissions do I need to create to make Session State secure? (Windows Server 2012 and SQL Server 2012 mixed mode authentication, Webfarm).
View 4 Replies
View Related
Feb 2, 2007
Our company has 2 Database Roles (DBE and DBA). The DBE creates
database schema, performs SQL Server Administration, and manages server
security. The DBA writes data access, ETL, and manages database
security. In 2005, we're struggling with how to allow the DBA to see
all of the logins on the server in order to add them as users of their
database. What permissions does the DBA need to select from any of the
logins on the server to add them to their database?
Michelle
View 1 Replies
View Related
Mar 10, 2014
I am migrating a database TESTDB from SQL 2008R2 to a new server running SQL Server 2012.
Management has decided the current sql users should have "better" user names. So the login and username "BadUsername" on the old server should be called "GoodUserName". Goodusername should have the same permissions as Badusername.
I have now restored a backup from the old server to the new server.
I used the following script for creating the login:
CREATE LOGIN [GoodUserName] WITH PASSWORD=N'difficultpassword', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
Then I run the following script:
ALTER USER Badusername WITH LOGIN = GoodUserName, NAME = GoodUserName
But the results are not what I wanted. I now have two database users: Badusername and GoodUserName. I would have preferred if BadUserName was "replaced" by GoodUserName, but it won't be a problem if I have to delete badusername manually. Worser is that GoodUserName have NOT "inherited" any permissions from Badusername.
Is there an easy way to transfer permissions or do I need to loop through the permissions of badusername and apply those to badusername?
View 4 Replies
View Related
Jun 25, 2015
I need to restore test DB from production backup but once it is restored I would need all the permissions of sql logins and windows AD account intact in test Db as it was before.
View 4 Replies
View Related
Feb 16, 1999
Using SQL 6.5 SP4.
TIA.
Zak
View 2 Replies
View Related
Nov 25, 2005
Third question:
I'm backing up a database which has some users.
When I try to restore it to another machine which has the same users and already has an old version of the database the database cannot be accessed. I must drop the users from the database by using the stored procedures sp_dropuser <username> and then add it again to the database from the Enterprise Manager.
Why this happens??
Regards,
Manolis
View 1 Replies
View Related
Jun 19, 2014
I am having issues with Restoring the Backup of same Database on to the same server , as i know like many of you will be asking y i need to restore on same server.. Well the need came in that way , now i think i know the problem (i.e) The Orginla DB is there and also i am restoring the same DB again on that server, so .mdf and .ldf will be same .
View 8 Replies
View Related
Jun 4, 2015
We have a production 2012 R2 SQL server that is not part of a domain. I have a database called CSSDC. I need to backup this database and make the BAK file available to a user on a different SQL server. Security is a concern. In order for this user to restore the database, they will have to be in the fixed server role sysadmins. I know I am going to have to use the Move with replace for the filegroups for the restore. Once the database is restored it will have to be altered to read-only. I would also like to remove the existing security and only have this new user access the read-only database.
View 4 Replies
View Related
May 7, 1999
Anybody figure a way around this:
In a development server you take a backup and you want to move the entire database to a production server. The production server does not contain the database, users, or logins in master.
When you restore the backup to production in 7.0, the users get moved to DBO because the logins do not exist and then you cannot delete them.
We are having to drop the users from the database on development, back up the database and restore it to production, then recreate the users on production.
This is bogus and did not happen in 6.5 because of the aliases.
Anybody come up with a better way?
View 1 Replies
View Related
Jul 26, 2014
I need to create a procedure with Restore Database command in many applications in differente platforms. But I can't "use master". How do I do it?
View 1 Replies
View Related
Sep 11, 2007
Hi,
We are using SQL Server 2005 Management Studio.
I created a Minimal User in an application DB. The user will access tables through stored procedures.
I do not want this user to view any other objects including objects in the Master DB.
I can prevent the minimal user from viewing objects from our application DB.
How do you prevent the minimal user from viewing objects in the Master DB?
Thanks.
Tim.
View 7 Replies
View Related
Apr 8, 2014
I'm currently working on a project at work to test the effects of database compression, trying to obtain measurable data on the impact of the compression on other server resources, and therefore whether the reduction in space used is worth the extra overhead. This has involved taking a trace of a production customer's workload for a period of time and replaying it against a backup using Distributed replay in synchronised mode.
I'm then taking a trace of that replay, as well as using perfmon to record useful data about the server, before and after compression is enabled. Finally, I'm loading the traces into a tool called Qure to analyse the impact of the compression on reads, writes, CPU, overall duration etc.
What I'm finding is that even across 2 different 'baseline' runs, which are replaying the exact same workload against the exact same database, performance etc differs to a significant enough degree that it calls into question the validity of the test. I can only put this down to the fact this server is on a VM, which is affecting available resources, which in turn affects execution plans the workload is generating and causes different replays of the same workload. I'm therefore looking at doing this on a standalone server, but I still can't be sure the differences will go away.
How to make tests such as this as similar as possible on multiple runs, when elements outside of SQL Server are in effect out of my control?
View 0 Replies
View Related
Jul 30, 2015
I have a table (named table1) with 20million rows. It takes around 11 minutes to apply the primary key to this table. There are some tables with over 100 million rows so based on the previous time if my calculations are correct it will take close to an hour apply this primary key for tables with around 100 million rows.
My current solution is to create another table (named table2) with no indexs or primary keys. Pump over only like 5 days worth of data, then apply the primary key. Then have a script that will eventually populate table2 with the rest of the data gradually. When I say gradually I mean like insert like every 100k per hour or something. Keep in mind this table2 is heavily updated with new records.
View 2 Replies
View Related
Jul 9, 2014
I understand that minimal logging can occur on a non clustered indexed heap as long as [URL] ...
*not replicated
*tablock is used
*table is empty
The following test seems to contradict this
In the test I create a non indexed heap, insert some record and check the log, then repeat the test on an indexed heap.
The results suggest that even though the conditions for minimal logging into a indexed heap are met, minimal logging is not happening although it does happen on an non indexed heap. What am I doing wrong?
CREATE DATABASE logtest
GO
USE logtest
GO
CREATE TABLE test (field varchar(100))
GO
CHECKPOINT
[Code] ....
View 2 Replies
View Related
Dec 12, 2014
How can I create secondary database on the server i.e. .ndf file?
View 2 Replies
View Related
Nov 5, 2014
I have a database which uses "Database Mirroring", and I need to write stored procedure and pull data from "Principal Server".
My Current Logic:
CREATE PROCEDURE abc123
as
BEGIN
IF Server01 = 'ONLINE'
BEGIN
[Code] .....
The problem I am facing is: Stored procedure is not created because "One of the server is not Online"...
View 4 Replies
View Related
Feb 21, 2014
We build up a new website in .net mvc 4.0 on a w2012 server with MSSQL 2012 database.We use windows autentification and normal anonymous access through NT AUTHORITYIUSR who is also a login in the database.But we make extra login possibility for users who are registered and they can insert and modify data in some tables in the database. And I'am afraid of giving NT AUTHORITYIUSR insert and update for some column in tables.I think of set up a new login for the database. Run a new connection string on login and set up special permission for that extra user on the database, and let the user become a member of the ordinary user on the database.
View 0 Replies
View Related
May 15, 2014
Where is located the hash password for the contained database users?I have a script that prints all creating statement so that a Dev environment security can be reapply after a prod data refresh but I can't find the table containing the hash password when the user is "with login" for contained database.
View 4 Replies
View Related
Jun 3, 2014
I have been using the software, and it has been working fine (on windows user A). Now, I have created another windows user (User B), and would like to use the same software/database. The software launches fine (User B), but cannot access the created SQL database (created with user A)
How do I setup the database to allow access from all users on the same PC?
View 2 Replies
View Related
Nov 26, 2014
I am trying to clean up security. When I check tables in a specific database I see a list of users with select access. There are 1000+ tables in the database. I know I can do 'revoke select on table_name to user_name' ....
View 3 Replies
View Related
Sep 28, 2015
I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
View 2 Replies
View Related
Jul 27, 2000
The transaction log takes up a lot of space on my database, and even after I try truncating the log, doing a transaction log backup, and then shrinking it, I am not allowed to reduce the size of the transaction log to less than 250MB. Is there some reason why this space is required?
View 1 Replies
View Related
Jun 30, 2004
Can somebody please tell me how to go about creating a backup file of a MS SQL database, then import it into another server?
View 2 Replies
View Related
Jul 23, 2005
hi, can anyone help me out with this report.I need to produce a report for the database level user permission.like for a database: DB_A, I want to output as such:[SELECT] [INSERT] [UPDATE] [DELETE] [CREATE TABLE] .... otherpublic G Dguest D D D D Ddbo G G G G Guser_A G D D D GG stands for Grant and D stands for Deny.I need not only to look at direct grant/deny but also whether that useris a member of fixed database role and fixed server role. like dbo willhave G on all.I am trying to look into sysprotects table and looking at lines with id= 0 but I just can't get to know what different action and protecttypenumbers means!Many thanks
View 1 Replies
View Related
May 29, 2015
1.can we restore the db if it is offline?
2.restore information stores in table 'msdb..restorehistory' if we restore the db when the db is offline?
View 1 Replies
View Related
Jun 24, 2015
I am on SQL 2012 standard version and I am writing a script to restore database from .bak files on a network.
ALTER DATABASE DB1
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
----Restore Database
RESTORE DATABASE DB1
FROM DISK = 'N:SQLBackupDailyDB1_backup_2015_06_22_194002_0500494.bak'
WITH REPLACE
ALTER DATABASE db1 SET MULTI_USER
GO
Since I have to restore about 100 databases , I am planning to put the script in a cursor. However my problem is how can I get the bak file name dynamically .
View 1 Replies
View Related
May 26, 2008
Hi friends,
I have created a database DB1 using CREATE DATABASE DB1 command. Then i created login name using CREATE LOGIN login1 WITH PASSWORD = 'password1' command and created user name using CREATE USER user1 FOR LOGIN login1 command. Now i have to assign the user1 to the database DB1.
Any one please tell me how to assign DB1 access privilege to user1?
Thanks in Advance
Sathish kumar D
View 1 Replies
View Related
Jul 29, 2014
Sometime during the night last night some user account permissions were "lost". Am I right to think that restoring the master database would be the way to go? We have a 2 node 2012 cluster and I stop the cluster resource and start the db in single user mode from the active node. Somehow the sharepoint farm is still trying to connect so I can't get logged in single user. What method could I use to stop users from connecting when I don't have access to the sharepoint farm.
View 8 Replies
View Related
