SQL 2012 :: Database Schema Creation Date And User Who Created It
Nov 12, 2014Query to find the date/time when a database schema was created and who created it.
View 2 Replies
ADVERTISEMENT
Automate The Creation Of A Backup Job When A New Database Is Created?
Mar 20, 2008I'd like to be able to create a new database and have a backup plan automatically created for it. Is there a way to do something like this?
Thanks!
SQL 2012 :: Find All Index And Creation Date
Mar 27, 2014I need to find all the index and the creation date. I did cross apply of sys.objects & Sys.indexes on name column. I am getting some but the team is saying they created so many. Any other option to find Indexes and their creation date?
View 2 Replies View RelatedSQL 2012 :: Listing User Created Objects?
Oct 7, 2015I'm trying to list everything (tables, view, procedures, functions, etc.) that was created by users in a database.
The query which seems to eliminate the most SQL system type objects is shown below.
SELECT *
FROM sys.all_objects SAO
WHERE SAO.is_ms_shipped = 0
order by SAO.type, SAO.name
This still includes some non-user created objects, like the below. See the attachment for details.
fn_diagramobjects
sp_alterdiagram
sp_creatediagram
How can I get rid of these type of objects without filtering on SAO.name LIKE...
SQL Server 2012 :: Finding What Table A User-created Statistics Is Located?
Feb 21, 2014I 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 RelatedTrigger To Set Creation Date Of A New Record In Database?
Feb 28, 2014I need a trigger to set the creation date of a new record in the database... I tried the following, but it changed all records, not just the new one...
CREATE TRIGGER trgCreationDate
ON [dbo].tabCustomerLookup
FOR INSERT
AS
BEGIN
UPDATE tabCustomerLookup
SET CreationDate = getdate()
END
How To Find The Creation Date For A Database Diagram?
May 27, 2008
Hello:
I'd like to check the creation date of a database diagram, how to do that?
please advise,
Thank you
SQL 2012 :: Partially Contained Database And Temp Table Creation
Nov 20, 2014As far as I know temp tables/objects will be created inside the default filegroup of the partially contained database and not in tempdb. Is it possible to either define a set of files dedicated to temp objects or define a second partially contained database dedicated to temp objects like tempdb?
View 0 Replies View RelatedSQL Security :: Log Shipped Database Server User SID Is Created Automatically
Jun 11, 2015We 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.
Set Default Schema To Current User For Call Database
Apr 20, 2013i have database and set default table schema to "ray" and me must input ruy.TABLE-NAME for retrive data !!! , i need set Default Schema to current user for call database as just database name(for my program) , how changed it ? (i change default schema for current user by alter command but not worked !)
View 1 Replies View RelatedProblem In Assigning Default Schema To Database User
May 6, 2008Dear All,
I am using SQLServer 2005, I have setup a login user "User1" and next I setup database user using the same username and login name. After that I create a new schema "mySchema" and make "User1" as the owner of the schema. To "User1" I assigned the default schema to "mySchema", so far its working fine. But when I open the user's property window (dialogbox) the default schema always gets reset to "dbo".
What could be the problem here? Please help me if there is any solution to get the right schema which I assigned to the user.
Thanks and regards,
SQL 2012 :: Can't Transfer Table From One Schema To Another Within Same Database
Oct 7, 2015I am trying to do the above using the following...
ALTER SCHEMA [dbo] TRANSFER [schemaame].[tablename];
...but getting the following error...
Msg 15530, Level 16, State 1, Line 5
The object with name "tablename" already exists.
The tablename I see in SSMS is schemaame.Tablename
When I look at the properties of the table, it says the name is 'tablename'.
I cant work out how I can change the schema of the table. Also, surely having a in a schema name is not recommended, right?
SQL 2012 :: Blank Database Created On Two Separate Disks - Write To Multiple Files
Mar 17, 2014I am testing out a blank database created over two physical files on two separate disks with one table called data which has one column called values nvarchar(max).
I filled the table up with a whole load of data and ran a select * against it. If I run Permon at the same time I can see that the read load has been spread over multiple disks as each of these disks is getting read from in parallel. If I create the same database on a single file and run the same select * again it takes much longer, proving that the read load has been distributed across multiple disks.
Now moving onto writes, this is where the confusion lies. I understand that SQL server fills files evenly until they need growing, after which it will then fill files individually until they are full in a round robin fashion unless you have trace 1117 turned on. What I don't understand is why the writes aren't distributed out whilst it is filling these file groups.
I ran an continual insert into my table with go 1000000 to monitor how the files are being filled up. I monitored where SQL server was physically placing the files as they were being inserted by running the following query:
;WITH CTE AS
(SELECT
sys.fn_PhysLocFormatter (%%physloc%%) col1,
RIGHT(LEFT(sys.fn_PhysLocFormatter (%%physloc%%),2),1) AS [Physical RID],
DATAID
[Code] ....
I could see that it would a thousand or so records into file 1, then a thousand or so into file 2, then a thousand or so into file 1 etc etc. In another words it would hit one disk, then another disk, then back to disk one to fill the file evenly. Is there any way to make SQL Server distribute the writes out in parallel so that both disks are writing in tandem?
By the looks of it, multiple disks only scale reads, as with writes only one disk is ever written to at once which is annoying. Any way to harness the write power of multiple disks?
SQL 2012 :: Server Referential Integrity Across Database - Schema?
Sep 19, 2014In SQL Server 2012 (also 2005 and 2008), can you have Referential Integrity across a Database? Across a Schema?
View 1 Replies View RelatedRemoval Auto Schema At Creation Of Table
Sep 11, 2015I've done already the schemas for all the tables a you suggested.But when I created a new table the dbo prefix (dbo.ADM.NewTable) appears.I'd like this dbo not to automatically appear whenever i created a new table — as it requires me to alter whenever I created a new table.Is this something that is required to determine the creator of the table?Otherwise, I'd like that to remove.
View 7 Replies View RelatedQuestion About Create Database,login,user,schema And Grant Permissions.
Apr 25, 2007Hi,
I created a database,login,user and schema like belows.
-- 2. create database
CREATE DATABASE MyTempDatabase;
-- 3. create login
CREATE LOGIN MyTempLogin WITH PASSWORD = '#mytemplogin$',
DEFAULT_DATABASE = MyTempDatabase,
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF;
--
USE MyTempDatabase;
-- 4. create user
CREATE USER MyTempLogin FROM LOGIN MyTempLogin WITH DEFAULT_SCHEMA = MyTempSchema;
-- 5. create schema
CREATE SCHEMA MyTempSchema AUTHORIZATION MyTempLogin;
The created user,MyTempLogin, must have permissions that can create tables,drop tables,select,insert,delete,update and bulk insert.
How can I grant permissions to the user?(or schema?)
I failed to grant by T-SQL query.
Additionally, what is purppose of the ROLE? Should I create or use it?
I'm confusing in security concept(login,user,schema,role).
Thanks.
SQL 2012 :: Database Project Schema Compare Fails To Pull In CDC Tables
Jul 11, 2014I have a database project where objects have been pulled in from the database using schema compare.
Unfortunately CDC tables which are referenced in stored procedures on the database have not been pulled in by the schema compare & hence I cannot build the project and deploy changes back to the database.
How to get these tables included in the project .
Can Table Created On Different File Group Based On Schema
Feb 17, 2012Provided that we have 2 type of tables, the first type of tables with schema schema_a and the second type with schema schema_b.Also 2 filegroups apart from primary created: filegroup_a and filegroup_b.How can I configure so that when table with schema schema_a created, it can be put on filegroup filegroup_a automatically and when table with schema schema_b created, it can be put on filegroup filegroup_b automatically?
View 1 Replies View RelatedSQL Security :: How To Copy Permission Of A User To Newly Created User
Oct 24, 2015How can i assign permissions to a newly created users as of an existing user?
View 3 Replies View RelatedGet New Database Created Then Running Script To Created Tables / Relationships
Jun 29, 2015trying 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?
SQL 2012 :: Trace Creation Time Is Different From Time Check Creation
Oct 8, 2014We have an SSAS instance where when we run the query "select * from $system.discover_traces" the creation time in the resultset shows a different time from when we actually started the trace.
for example if we have create the trace at 3.30pm it shows 7.35 pm in the Sql server management studio resultset when we run the query "select * from $system.discover_traces".
SQL 2012 :: Produce Report To Calculate No Of Days Based On User Input Start And End Date
Jan 5, 2015I have to produce a report to calculate no of days based on user input start date and end date.
say for eg: in the tables I have emp name
user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff
within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days
PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days
PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days
Total days 53 Days
for this :
[code]...
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
The 'System.Web.Security.SqlMembershipProvider' Requires A Database Schema Compatible With Schema Version '1'.
Sep 27, 2007Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
User Creation
Apr 10, 2008We normally map a single SQL user to all application users . From performance standpoint and from best practices perspective , what is the preferred method of mapping OS users to SQL users viz one to one OR many to one .
View 6 Replies View RelatedSQL 2012 :: User Database Backups
Jun 2, 2014I have inherited a new SQL Server 2008 database server and can not figure out how my user databases are being backed up. This database server is running under a VM.
All the user databases are being backed up nightly per the SQL server log. The backups are written to a virtual disk and is kicked off by the NT AUTHORITYSYSTEM user. I can not see the virtual disk. A restore task does not provide any information about the last backup. I have created a new database, and it is automatically included in the next set of backups.
I have looked at the windows event viewer with out any luck. There are no SQL Server Maintenance Plans or Agent jobs that call a backup. I have also checked the Windows Task Scheduler and can not find any task that does a backup.Could the backups be called from another server ?
Query For Db User Creation In MS SQL
Jan 24, 2007HI everybody...
Can anyone help me to know the
Query to Create SQL database Users in MS SQL
through the query engine of MS SQL Server Management Studio..
I am struggling with the same and I tried the one
create user <user_name> for login <login_name>.
But it seems that it will create the windows user itself and not the sql user.
I want a query similar to create user <user_name> identified by <password> which is used in ORACLE.
Please help me in this situation.
Thanks in advance
dance
Database Schema Compatible With Schema Version '1'
Apr 12, 2008Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
View 2 Replies View RelatedSQL 2012 :: Unable To Drop Database User
May 7, 2014We are unable to drop a database user with the following error "Msg 15284, Level 16, State 1, Line 1
The database principal has granted or denied permissions to objects in the database and cannot be dropped."
On checking the database permissions we have the following permissions assigned to the user
classclass_descmajor_idminor_idgrantee_principal_idgrantor_principal_idtypepermission_namestatestate_desc
17SERVICE655360517SN SENDGGRANT
17SERVICE655370517SN SENDGGRANT
17SERVICE655380517SN SENDGGRANT
17SERVICE655390517SN SENDGGRANT
17SERVICE655400517SN SENDGGRANT
[Code] ....
How we can revoke the SEND permissions?
Sql Server User Creation Problem
Mar 28, 2008I m using SQL server 2005 express edition and management studio express. I m still confused with some terms. What is the difference between a new server registration, a new login and a named instance? I installed sql express using default configuration. It shows that my server name is- TANVIRSQLEXPRESS (where Tanvir is my hostname) and it uses windows authentication. I created a new log in from Security>logins in the object explorer of express manager. In the property of this new login I just put the login name and gave password under sql server authentication and leaving everything else as default. I have enabled tcp/ip for both sqlexpress and client protocol in Sql server configuration manger. YET, I cant log in using my new log in name and password. I got the following error-
Cannot connect to TANVIRSQLEXPRESS.
ADDITIONAL INFORMATION:
Login failed for user 'Tanvir'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
------------------------------
I just want to know HOW to create a user with password in sql express edition.
Sql Server User Creation Problem
Mar 28, 2008I m using SQL server 2005 express edition and management studio express. I m still confused with some terms. What is the difference between a new server registration, a new login and a named instance? I installed sql express using default configuration. It shows that my server name is- TANVIRSQLEXPRESS (where Tanvir is my hostname) and it uses windows authentication. I created a new log in from Security>logins in the object explorer of express manager. In the property of this new login I just put the login name and gave password under sql server authentication and leaving everything else as default. I have enabled tcp/ip for both sqlexpress and client protocol in Sql server configuration manger. YET, I cant log in using my new log in name and password. I got the following error-
Cannot connect to TANVIRSQLEXPRESS.
ADDITIONAL INFORMATION:
Login failed for user 'Tanvir'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)
------------------------------
I just want to know HOW to create a user with password in sql express edition.
Scripting User Creation In SSMSE
May 4, 2007Hi,
Strange question here. When I script a user to be created for a database deployment, I get the following script:
USE [DatabaseX]
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'newuser')
CREATE USER [newuser] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
Now, this creates the user at the 'DatabaseX' db level, but not at the 'master' db level. The user "newuser" is then not allowed access to do anything in 'DatabaseX', such as execute queries, update tables, etc.
I had to modify our script to be like this:
USE [master]
GO
CREATE LOGIN [newuser]
WITH PASSWORD=N'somepassword',
DEFAULT_DATABASE=[DatabaseX]
GO
USE [DatabaseX]
GO
CREATE USER [newuser] FOR LOGIN [newuser]
GO
USE [DatabaseX]
GO
EXEC sp_addrolemember N'db_owner', N'newuser'
GO
This allows the "newuser" to execute queries, update tables, etc. in 'DatabaseX',
Is there some option in SSMSE that I'm missing when I'm having it generate my user scripts that it's leaving the rest of the script out?
Thanks in advance for your help
Dynamic Login && User Creation
Mar 27, 2006Hello. I'm trying to create a new login and username inside a trigger using variables.
This code works:
create login testUserName with password = 'testPassword'
The problem comes when I try to use variables for the username and password like this.
create login @username with password = @password
Does anybody know how to get around this problem?
BTW, the error message it gives is this, but I really doubt that semicolons have anything to do with it. If I literally type my data in the create login call it works fine, but when I use variables it doesn't.
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '@username'.
Msg 319, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Date Creation In SQL
May 3, 2006Hi,I have a parameter that is being input as a String via Crystal Reports.It's called 'School Year'and a typical value is '2006'. I need to be able to create a 'Date'that is compared to a field value in a where clause. In this case thedb field is called 'requisition_time_stamp' in the T_ORDER table. The'requistion_time_stamp' file is of type 'datetime'.Below is pseudo_code for what I need to do:T_ORDER.requisition_time_stamp >= Date(ToNumber(School Year),6,1)ANDT_ORDER.requisition_time_stamp < Date(ToNumber(School Year + 1) , 6,1)The 'Date' method is a mock-up and is problematic because the 'SchoolYear' field needs to be converted to a number so it can be incrementedin the second statement, so I also need a conversion method, somethinglike 'ToNumber(School Year)' .I've looked at the Date functions in the Sybase documentation and Idon't see anything that fits what I'm trying to do.Any help would be greatly appreciated!
View 2 Replies View Related