SQL Security :: Log Shipped Database Server User SID Is Created Automatically

Jun 11, 2015

We have a production server "prod"  which has a user say 'test' that lets the users connect to the application and  a logon trigger which stops them from connecting to the server through SSMS. I  log ship 'prod' to the 'rep' database and the user 'test' is obviously created in the logshipped database 'rep' during the logshipping.

Now the login 'test' does not have any login from server 'rep', But people can still login to the 'rep' server and query the 'rep' db . I checked with SUSER_SNAME and found the SID of the user in rep server which I never created and which is not even present in the login names, Even If I create a new username in the 'prod' db, after logshipping the new user is replicated in the 'rep' server.

View 9 Replies


ADVERTISEMENT

SQL Security :: How To Create Database Specifications On Newly Created Database Automatically For Audit

Jul 15, 2015

I am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.

View 6 Replies View Related

SQL Security :: Windows Authentication Login Is Created Automatically In DB

Apr 24, 2015

In my DB (SQL 2012) one windows authentication is created automatically and a schema is created exactly automatically that this user is owner of this schema. (e.g the user is domainx and schema exactly domainx).

I created a DDL Trigger on my DB to find out who and how it is created , but I didn't get any useful information.

I change the owner of schema that I can delete the schema and after that delete the user, and sometimes because the user is owner of a service , I have to alter the owner of the service to DBO and delete the user , but again and again exactly this user and schema (e.g domaninx ) are created as soon as I delete it.

I have read these topics : [URL] .... and [URL] .... but can't solve my problem.

View 10 Replies View Related

SQL Security :: How To Copy Permission Of A User To Newly Created User

Oct 24, 2015

How can i assign permissions to a newly created users as of an existing user?

View 3 Replies View Related

Automatically Backup New Created Database Once

Dec 26, 2007

Hi,

i've sql 2005 std sp2,and i'm working with a program that create every view month's a new database automatically.

my question is - how can i make a full backup of the newly created database automatically only one time when it's created?

Thx

View 16 Replies View Related

SQL Server 2008 :: Setup Log Shipping From Log Shipped Database?

Mar 20, 2015

We have log shipping for databases from production to back office (BO) environments for users to run reports. In preparation for moving a back office environment to a different server, we want to setup an addition log shipping environment, let’s call it BO2, same as the BO. One of the challenges we had was the amount of time it would take to get the full backup from production to BO2 due to the size of the database. It would take days just to transfer the full backup.

Solution:Let’s just say the database being logged shipped is DB123. Instead of using the full backup of DB123 from production, we use the mdf and ldf of DB123 from current log shipped database on the BO to setup the additional log shipping on BO2.

Steps:1.Setup the directory structure for the undo file (tuf) on the BO2 server same as the BO server.

Note: The undo can be moved to different drive after the log shipping is setup.

2.Stop SQL services on the BO server.

3.Copy the undo file (tuf), mdf, and ldf files of the log shipped database, DB123, from server BO to server BO2.

Note: Keep these files as main copies because they will be used multiple times. Do NOT copy files to where you’ll put the new database files.

4.After the files are copied, start SQL services on BO.

5.On the BO2 server, create an empty database DB123 with database name, file_id, logical names, physical names exactly the same as the ones on the BO server.
Note: Make the size of mdf and ldf small so it doesn’t take long to create the database.

6.Stop SQL Services on the BO2 server.

7.Overwrite the new mdf and ldf files of the empty database DB123 with the mdf and ldf files that were copied over from BO.

8.Start SQL services on BO2.

Note: If the file_id, logical names and physical names are not matching up, the database will be in suspect mode.

9.Do a backup of DB123 with COPY_ONLY.

10.Drop DB123 on BO2 after backup is complete.

11.Use the full backup from previous step to setup log shipping of DB123 on BO2.

Note: Below was the LiteSpeed syntax that was used

exec master.dbo.xp_restore_database @database = N'DB123' ,
@filename = N'F:SQL BackupsDB123_FULL.slsfull',
@filenumber = 1,@with = N'STATS = 10',
@with = N'STANDBY = N''C: MSSQLDataROLLBACK_UNDO_DB123.trn''',
@affinity = 0,@logging = 0

12.Stop SQL services on BO2.

13.Again, overwrite the mdf, ldf and undo file (tuf) of DB123 on server BO2 with mdf, ldf, and undo file (tuf) from BO.

14.Start SQL services on BO2.

15.Replay transaction log from PRD

Note: You can change the location of the undo file by copy the current undo file to the new location and point to new location in “STANDBY” parameter. DO NOT do cut and paste of undo file but do copy and paste.

It works on Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64) . I have not tried it on SQL Server 2012 yet.

In steps that stop/start SQL services, you can bring the database offline/online.

In a way, this is doing detach/attach a log shipped database.

View 0 Replies View Related

SQL Security :: Maximum Number Of Users That Could Be Created For Any Database

Jun 16, 2015

Is there any maximum cap on the number of users that could be created in a given database of MS-SQL Server?

View 4 Replies View Related

SQL Security :: How To Restrict User From Seeing Available Database On Server

May 22, 2015

We can restrict users from accessing the databases from the security. But is there anyway , we can restrict users from seeing the available databases on the server, user can access and see the database he or she has access to other databases will not be visible?

View 13 Replies View Related

How Do I Add An Importer As A User In My Sql Server Database Security Folder.

Dec 28, 2007

How do I add an Importer as a user in my sql server database security folder.
Regards
Karen

View 1 Replies View Related

SQL Security :: How To Find Who Alter Database User Access Changes In Server

Oct 6, 2015

someone was alter user  databases owner access.

how to find  who alter evoke  the database user access changes in sql server.

View 5 Replies View Related

SQL 2012 :: Database Schema Creation Date And User Who Created It

Nov 12, 2014

Query to find the date/time when a database schema was created and who created it.

View 2 Replies View Related

Added Users To A Logged Shipped Database

Dec 20, 2007

I have 2 sql server 2000 database that participate in log shipping.Server A is the source database, which log ships to a database onServer B. The database on Server B is in a warm stand by/read onlymode. Is there a way that I can add users to the database on ServerB, without having to add the users to the database in server A? I amtrying to use the database on Server B as a reporting only databaseand add more users to have permissions to run reports.Please let me know if anyone has any suggestions. Thank you.

View 2 Replies View Related

SQL 2000 - Restore From Log Shipped Database After Disaster.

Jul 21, 2007

Is there any need to re-apply SP4 to sql server 2000 if a user database has been restored to it from a non-service packed sql 2000 server.

We inadvertantly did this after a flood and later realised that the log shipped server at the other site was not not service packed.

Does service pack 4 update user databases in any way?

We have not had any major issues with this but I need to rule this out when considering performance issues and so on that have been raised since the recovery.

Any help would be appreciated.



View 3 Replies View Related

SQL Server 2005: User Security Management: User Ch

Jun 25, 2007

How do you handle user level security with SQL Server 2005?

Say I have an HR database.

In Active Directory I have two groups: Managers, Employees.

Now in this HR Database I want to setup permissions in such a way that Managers can see all employees under them (but not other managers) and the employees can only see themselves.

(I'd have various levels of management defined in a table somewhere, so that each employee has a manager ID that links to another employee so that the CEO would be manager of everyone by working down the chain).

What I'm trying to understand is the best way to handle the permissions.

I'm not entirely clear on how to deal with that.

Would I use user chaining to do that, I wouldn't need impersonation (that's just for instances where you want dynamic SQL and it won't execute with user chaining, correct?)

Anyway, just looking for some general direction on this (obviously I need to get a good book it would seem).


Would I create a stored procedure that runs with EXECUTE AS permissions so that I'd have a non-interactive login it uses that has table access then all the other users have permission to execute the sproc?

So that sproc runs, pulls back a SELECT * FROM tbl_HRINFO and using a WHERE constraint limits who is returned WHERE SupervisorID = CurrentLoggedInEmployeeID ?

Also: How can I determine who is logged in and running the procedure, would the sproc use the SELECT USER_NAME command to see who was running it?

As you can see, I'm working from square one on all of this.
Not sure if my posting entirely made sense, but hopefully someone can get me pointed in the right direction, thanks!

View 3 Replies View Related

SQL Security :: How To Get Specific Permissions Per User On Each Database

Nov 24, 2015

I'm trying to get specific permissions per user on each SQL database, would you let me know if it could be achieved e.g on an object level?

View 5 Replies View Related

SQL Security :: Sync AD User Credentials With Database

Mar 14, 2014

How Sync the user and password from my Active Directory, to a SQL Database. Actually, my environment have a database with users and password added, my custom applications uses it like a passport, but now I want to use Active Directory to control these users, but I can't use windows authentication in my old apps. I was reading about Forefront Identity Manager to do this, but I need a free solution. The Sharepoint database sync user credentials with AD?

MCTS Exchange 2010

View 3 Replies View Related

Security For Row Level But Not Based On Database User's Login

Apr 21, 2006

HiI need to set security for row level but not based on Database user'slogin. It should be based on the user table login. For the particularuser I need to allow only the particular records to access insert,update delete and select.Let me explain clearlyFor example think we are using asp/asp.net websiteEg:www.test.comSo take this is our website and if you try this URL then you will get awindow for Login name and password.For example the Login name is windows user name (Here windows usermeans server windows user and not client) and windows password. So ifyou have login user id you can able to login in our site and we haveanother check. We have our own usertable this table consist all theuser login names and user rights. We will check the windows user whologin in our site has rights in the usertable I mean he is present inthe usertable if he is not present then we will display a message youhave no rights to access this site.If he has login id in our usertable then he allowed viewing ourpages. Still if he has the login id we will check the user who loginhas how much right to access to each page and the records of each tableits all depend on the user rights.So, here I need the row level security. For each and every table weneed to check the corresponding user and executing the record producelot of business logic problem for us.So after the user login we need automatically to set row levelsecurity for all the tables. Based on the user who login.So from there if we try select * from <tablename> then we can only ableto get the allowed records to select, insert, update, delete.Please can some one help how to solve this?Note:For some help you can refer the below URL (See in that they only givenabout the row level and column level security for each database usersnot for our required concept)http://www.microsoft.com/technet/pr...5/multisec.mspxThanks in advanceRams

View 1 Replies View Related

SQL Security :: Database TEST Is Already Open And Can Only Have One User At A Time

Apr 30, 2015

Database 'TEST' is already open and can only have one user at a time.

I also tried this command but having the same error.

Use Master
GO
Select * from master.sys.sysprocesses
Where spid > 50
            And dbid=DB_ID (‘StuckDB’))  -- replace with your database name

View 8 Replies View Related

SQL Server 2012 :: Finding What Table A User-created Statistics Is Located?

Feb 21, 2014

I can easily find user created stat in a databaseSELECT * FROM DB.sys.stats WHERE user_created=1But how do I determine what tables those stats are in? with over 6000 tables I don't feel like looking through all the tables.

View 2 Replies View Related

Get New Database Created Then Running Script To Created Tables / Relationships

Jun 29, 2015

trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication. 

E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".

From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName]   Go   CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?

View 3 Replies View Related

User Is Not Able To Access The Database Model Under The Current Security Context

Feb 7, 2008

I have a restriced user on SQL Server that is only permitted to creat a new database and manage it only. All other database are hidden to that user.

when that user login to SQL Server and create a database and try to change the default folder path for data and log files, gets an error,

The Server principal "User" is not able to access the database "model" under the current security context. (MSSQL Server, Error: 916)

Any idea???

Thanks,

View 7 Replies View Related

SQL Security :: Create Database User And Give Grants To Few System Tables

Aug 12, 2015

Need to create a user in sql server provide grants to few system tables to the above user.

View 10 Replies View Related

SQL Attached Mdf Database Files VS Database Imported Into Or Created In SQL Server 2005

Apr 8, 2007

 Hi all (newbie @ asp.net)(oldie @ ASP 3)What is the purpose of using an attached MDF database files in the App_Data folder on a web site as to importing it into the SQL server directly or creating it on the SQL server. Does a mdf database attached file purely use the SQL server as a connection interface.Is it something similiar to DSN(ODBC) Connections for ms access databases.

View 2 Replies View Related

How Can I Detect Changes Automatically In The SQL Server Database When Anything Is Updated..?

Mar 13, 2007

I been asked recently in the interview thatHow can I detect changes automatically in the SQL Server Database when anything is updated, deleted or inserted?if anyone can help me in this that will be really great.
I dont actually know whether I can ask this here but I wanted to know the answer for this and thought this might be right place to ask?
sorry if I am wrong?
Thanks in Advance

View 2 Replies View Related

SQL 2012 :: Automatically Syncing Database To Different Server?

Jul 25, 2014

We have two SQL 2012 servers, sever A and B.

We need to sync a database on A so that there is a copy of it on B.

We need to be able to query both databases so mirroring isn't an option.

View 7 Replies View Related

Ms Sql Mdf Database File Attached Vs Created On Sql Server

May 27, 2007

 Hi allI have a question concerning sql database mdf files. In the old days I would user a ms access database. This file would be stored with the actual web files and would utilise a dsn connection. I have noted when designing with vwd 2005 express it allows you to use 2 methods of creating a mdf database. You can either create it as an attachment mdf or you can create it directly using sql manager. My question is, if you create the mdf database as an attachement file can you store it in the same manner as if you where using a ms access database, meaning can you store it with the web site's files so it uses the file storage allocated size and then create a connection similar to a dsn (but for sql) to the isp's sql engine or does it have to be uploaded to the isp' s sql server.The reason for this question is some of my customers do not want to pay the extra cost to have an sql allocation, however I do not want to go back to using old asp methods to create advanced sites as I prefer using stored procedures. Any help will be appreciated 

View 4 Replies View Related

Connecting To An SQL Server Database Created Using SQLDMO

Jan 10, 2001

I am a new SQL Server developer using Visual Basic 6 Professional Edition. I am using ADO and SQLDMO to develop my application. The SQL Server is Version 7.0, SP2 and SP3 (two different servers).

I am having difficulty gaining access, using ADO, to databases I create using SQLDMO. I create the database, create the DBFILE object, create a login and attach it to the database, create a user, assign the created login to the user,
and assign the created user to the 'db_datareader', 'db_datawriter' and 'public' roles. the login object was set to 'standard', not NT. the SQL Server is set to 'mixed' NT -SQL Server login mode. Thw SQL Server is running under the 'system account'. Using SQL Server Enterprise Manager, everything looks OK. An attemp to connect using the ADO connection object fails, giving an error of Login failed for user 'engbom3admin'.

Connecting to the SQL Server using SQLDMO uses the 'sa' user name with a blank password.

I can successfully connect to the created database using the ADO connection object using the 'sa' user name and blank password. Using the SQL Server Enterprise manager, I cannot see anything at all different between the 'sa' and my created 'engbom3admin' user. I've manually set the created user to have 'db_owner' roles, etc. Same result. I'm stumped. I've read all I can gather from the Microsoft SQL Server books. I'm still stumped.

I would greatly appreciate any help, information or tips you could provide. Thank you in advance.

Sincerely,
Bob Wohlers
SVP, IS Datamax Corporation

View 2 Replies View Related

SQL Security :: How To Create A Low Privilege User And Role For Server Session State

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

SQL Security :: Login Failed For User Null / Not Associated With Trusted Server Connection

Jun 19, 2015

Any way to find out who's causing "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection." error in SQL Server error logs?

View 7 Replies View Related

User Created DBs

Jul 1, 2006

Hi,

How can i get the names of user created databases?

select Distinct (name) from sysobjects where xtype = 'U', i know this statement for tables but is there a statement for Databases?

thanks

View 5 Replies View Related

SQL Server 2008 :: Automatically Execute Script Once Database Has Been Attached

Jul 29, 2015

our clients have the flexibility to detach and attach databases (I know there are a lot of considerations around this but there is now way of changing it), once they attach a database we need to run some code to update a bunch of values in the database.

Other than creating a SQL Agent job are there any other options available to automatically execute a script once the database has been attached?

View 1 Replies View Related

How To Backup A Database In Sql Server Management Studi Express Automatically

Mar 3, 2008

I come cross some post about how to backup a database in Sql Server Management Studio Express automatically. The scripts look like as below

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_BackupDatabase]
@databaseName sysname
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +
REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
' TO DISK = ''C:Backup' + @databaseName + '_Full_' + @dateTime + '.BAK'''

EXECUTE sp_executesql @sqlCommand
END



It throws that the DDL statement is not allowed. What's wrong with it?

View 5 Replies View Related

Transfer Database Created In SQL Express To SQL Server 2005

Dec 2, 2005

Using VWD 05 Express & SQL Server 05 Express, how do I transfer my database to my host that is using SQL Server 2005.
On the SQL Server express web site http://msdn.microsoft.com/vstudio/express/sql/powerful/ one of the features listed for SQL Server 05 Express is: Transfer any database created in SQL Express to other editions of SQL Server 2005.
Thanks

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved