I'm using sql server 2005 express edition. I had so security problems that i got helps from this forum. Now i have another one :
In my system i'm using a database with sa password, it means to login and work with database structure and data user should enter sa password.
But when i deAttach db and transfer it to another computer, I can easily attach db to customers sql server without knowing sa password.
I want to say : Everybody can attach my db to their system without knowing sa password and without need to create user, I need security in customer side not at mine.
Please help me what's wrong? I know there is a solution that i don't know!
I have below table in the snapshot, My task is to send the same output as a attachment in Excel to email. i used the below procedure  EXEC [msdb]..sp_send_dbmail @profile_name='TSSSendMail', @recipients='mymailID@gmail.com.com', @subject='DB Mail', @body='HI Team', @execute_query_database = 'DbName', @query = 'EXEC J16ReimbursementFortnightly1TO15 1', @attach_query_result_as_file=1, @query_attachment_filename = 'SummaryReimbursement.xls'
But I am not getting Proper Output, is seems like CSV Format, but i want it proper tabular format in Excel.
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.
In SSMS, I connect Object Explorer to a partially contained database using a contained user login with password. This user has a database role of dbdatareader. When I try to expand the Tables in the database, I get the error:Â
The SELECT permission was denied on the object 'extended_properties', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)
Is there a way to set permissions for the contained user so that this could be done?
I had created 2 Sql server instance in 2 servers created using VMware. From the primary server I log shipped the required databases into the secondary. Both the servers were in the same domain whose active directory was also in another server in the same virtual lab environment. My question can we have the primary sql server in one domain and the secondary sql server to which the logs are shipped in another domain by including a router also between the 2 networks for connectivity?
I have installed new SQLServer2012 instance and my domain user have sysadmin privileges on this instance. I have a restore procedure and it will execute WITH EXECUTE AS 'domainmy username', for all the developers have exec permissions to this procedure. But newly installed server this procedure was failing with the following message. But the same procedure executing fine on other servers.
Msg 262, Level 14, State 1, Line 1 CREATE DATABASE permission denied in database 'master'. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
I have a script that automates some db drop/restore operations and bringing the database to single user mode is part of it: ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE...I want this to executes under a login, that has restricted privileges, so I've created a login and granted it a dbcreator role + ALTER ANY DATABASE privileges.
Problem: When I run the script against a database with an active/sleeping connection:It fails when using the restricted login: "Msg 5061...ALTER DATA BASE failed because a lock could not be placed on database ..."It completes successfully when using a sysadmin login According to stackoverflow.com the solution is to kill the active/sleeping connections to the database, before ALTER-ing it, which works fine, but the question is....
Questions: Why the "ALTER DATABASE..." statement works under the sysadmin login, but not under a dbcreator one?Does this mean the sysadmin login kills the connections to the target database in the background?Is it possible to grant additional privileges to the restricted login, so the "ALTER DATABASE..." statement won't need preventive killing of the connections?
i have a development database that has updates and changes to a production database. rather than go through individually and alter all relevant tables and stored procedures, id like to back up the database on the development side and restore it on the production side as the production database. is there a way to restore the database on the production server but preserve all the security settings (ie logins and such)? i noticed on our development server, that if i try to restore the database with my development database, it overwrites the users and/or if the user is the same on both, it removes the login name for that user.
I'm trying to use the xp_sendmail and include a txt file as an attachment. I can't get the procedure to work with the attachment.....any help would be greatly appreciated. this is what I have been trying to get to work.....Thanks!! Scott
I wrote a procedure in Sql Server 2000 which sends an E-Mail to any E-Mail account. But I not able to send an attachment with the E-Mail. I dont have the code for attachment in the same procedure, as I searched on Google I not able to find out. Please help me for the same. Waiting for your reply.
I have a DTS package that runs 4x a day and generates an excel spreadsheet, renames that spreadsheet with a datetime stamp and then places it into a folder on our network. I have been asked to email that spreadsheet to someone everytime the package runs.
My question is what would be the best way to handle this and how do I ensure that whatever process I define grabs the correct file? The folder that the Excel file is being placed into has multiple files in it. Is there a way to tell SQL Server which file to grab and email?
Any thoughts or suggestions would be greatly appreciated! Thanks!
I am new to sql and want to write a stored procedure to email a database group an attachment of a report that was created. Can someone please point me in the write direction thanks.
Hi, I am using the following stored procedure to send a mail with attachment. But the mail is sent without the attachment. Can anyone help me?
CREATE PROCEDURE DBO.sp_Send_Mail_test( @p_From as nvarchar(50), @p_To as nvarchar(50), @p_Subject as nvarchar(255), @p_Body as varchar(1000), @p_CC as text = null, @p_BCC as text = null, @p_Attachment varchar(500)=null ) AS Declare @Message int Declare @hr int Declare @source varchar(255) Declare @description varchar(500)
EXEC @hr = sp_OACreate 'CDO.Message', @Message OUT
I am able to send normal mails from SQL Server with no attachments.
But when i am trying to send mail with an attachment i get following error.
Server: Msg 18025, Level 16, State 1, Line 0 xp_sendmail: failed with mail error 0x80004005
I am running SQL Server 2000 , SP3.
select @@version --------------------------------- Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
We have a DTS package in the old SS2000 that we are still using in SS2005. We change the old xp_sendmail to use the new sp_send_dbmail. This runs fine without attachment. But with attachment, I'm encountering an error when running the package.
The task reported failure on execution. The client connection security context could not be impersonated. Attaching file requires an integrated client login. Syntax error or access violation.
I cannot find much topic on the net about this error.
Dim smtpServer As String = Dts.Variables("MailSMTPServer").Value.ToString Dim htmlMessageTo As String = Dts.Variables("ToLine").Value.ToString Dim htmlMessageFrom As String = Dts.Variables("FromLine").Value.ToString Dim htmlMessageSubject As String = Dts.Variables("MailSubj").Value.ToString Dim htmlMessageBody As String = Dts.Variables("MailBody").Value.ToString Dim htmlMessageLog As String = Dts.Variables("ErrorLog2").Value.ToString Dim htmlMessage As MailMessage Dim mySmtpClient As SmtpClient Dim m As New System.Net.Mail.MailMessage Dim AttachLog As Attachment = New Attachment(htmlMessageLog)
htmlMessage = New MailMessage(htmlMessageFrom, htmlMessageTo, htmlMessageSubject, htmlMessageBody)
mySmtpClient = New SmtpClient("<SMTP Server name>") m.Attachments.Add(AttachLog)
mySmtpClient.Credentials = New NetworkCredential("<emailid>", "<password>")
mySmtpClient.Send(htmlMessage)
Dts.TaskResult = Dts.Results.Success End Sub
Please let me know, how can i see Attachment in my mail.
I am encountering the same problem above and I did exacly as described . But it is not working.
I must send emails every month with dynamically named files as attachments. The files are named according to the date on which they are generated. For example on the first of November 2007, the file will be named myfile_1_11_2007.
I have created a variable called DynamicFileName with package scope, data type string and default value: d:\tests\
In "Send Mail Task Editor" Dialog Box, I have specified the following:
smtpConnection: smtptest.server.com From :nemo@smtptest.server.com To: nemo@smtptest.server.com Subject: Dynamic File Email MessageSourceType: Variable MessageSource: blank Priority: blank Attachments: blank
When I execute the package, I get the following errors: ----------------------------------------------------------------------------------------- Error at Send Mail Task [Send Mail Task]: Either the file "d:\tests\myfile_1_7_2007.csv" does not exist or you do not have permissions to access the file.
Error at Send Mail Task: There were errors during task validation. ---------------------------------------------------------------------------------------------------
Of course, the file does not exist. It will exist at tun-time. How can I tell the Send Mail Task to use a filename that is dynamic ?
By the way, once I have specified the code for FileAttachments, on trying to edit the Send Mail Task Properties, I can see that the Atachments field has been set to "d: estsmyfile_1_7_2007.csv by itself: I never typed it there !! It seems that the task executes the code even before it is run. If I remove the attachment path manually, on running the dts, I get an error saying that "either the file does not exist or you do not have permission to access the file.
I would be most grateful if anyone could be of help
An employee has requested leave a mail should go to the person he is reporting to as a mail and with this mail an attachment of the leave request should be sent.
When the person who recevies mail he opens the attahcment and apporves the leave request or rejects a return mail to go to all concerned.
Hi all, I create a subscription to send the report (in pdf format) to users once every week. The subscription works, all users manage to receive the email but some of them do not have the pdf file attached to the email. Anyone know what is wrong??
I hace an Execute SQL task which calls a sproc which contains the sp_send_mail system sproc to e-mail a set of query results . It is OK as long as the query results are small, but I get this error if they get too big: - File attachment or query results size exceeds allowable value of 1000000 bytes.
any way to change (and where to change) this value.
I am delivering report in Excel format every Monday. I am also including link with that email. sometime, it delivers excel sheet correct and sometime it just gives blank sheet in attachment and the strange thing is that We can see data through the included link, but not in excel attachment.
Any idea?? If any of you passed through this problem plzzz help me out...
I have just built a new SQL7 server (SQL SP3 and NT SP6a) server and need a bit of advice on implementing database security.
This new SQL server will be used by several projects which require a SQL database, our Personnel system being one of them. Since the data in the Personnel system is obviously of a sensitive nature I need to restrict access to the data. Client access is controlled via a client application but I need to restrict Enterprise Manager, Query analyzer ... type of access.
We have a team of people who look after the servers on a day to day basis and these are all Domain Administrators. By default these people will be able to browse into the Personnel database and read the information.
How can I restrict access to a SQL database so that only named users (SQL and NT) can access a database ? If database level access is required then we would need to obtain the SQL user password from Personnel which will then allow mainenance access, after which the password si changed by Personnel.
I am working with a development team and right now there is no security on the server everybody uses the sa account including our dts and jobs. I have talked to my boss about it and he wants me to come up with a guidline
What will happen to our jobs if i change sa password. also dose anyone have a little guidline sample?
What is the standard for database security – windows login or sql server logins? User defined database roles?
We have earlier used a single sql server login with read/write permissions to tables & exec on stored procedures. Individual application users are stored in database tables & their permissions are handled by the application itself. But we are starting to get concerned about password issues with the sql login & also that when I see any connections on the database, all of hem show up with the same sql login. So I cannot make out which user exactly has a connection open.
To get around these issues, we are thinking to add NT logins for each user. These users will be part of an NT group and will be added as a user to the database. There will be a database role with exec permissions to required stored procedures (all read/write/update will be controlled through SPs) and the NT group will be part of this role. The stored procedures will be encrypted. So if a NT user logs in directly to the database, he won’t be able to read from the tables or SPs and access will be limited through the application. I will be able to see each login associated with a connection.
What do you all think about this security set up? What are other issues I need to be concerned about? What are other popular database security models?
Hi experts, I would like to ask if it is feasible to limit the accessibility of an SA account in SQL 2005 in a specific database. The reason of doing this procedure is since we are deploying a package software to our client(s) we want to secure our own database to get tampered by our client(s).
Is there a way to create and encrypted database file?What do people do when data security is important at the file level? Inother words, you don't want anyone to be able to take the database file (orfiles) and extract data from them.Ideally, I want a file the is absolutely encrypted on disk and that isdecrypted for data access. The problem, obviously, is that this would be avery costly (cpu time) approach as you couldn't create a decrypted image ondisk (this would expose the data).Are all database systems then, non-secure?Thanks,-Martin
i a the user of sql server 2005 on window server 2003. i want to knowthat how can i prevent my database from other user logins because allof them are sysadmin type.and i am also looking for database concurrency control methods.if any one know about this plz mail me answer on this mail idJoin Bytes!thanx in advance
Hi, does anyone have the name of the command-line utility that creates a security database on SQL Server 2005 with the corresponding tables (users, roles, applications)? I remember using it in an exercise some time ago and I can't find it anywhere.
Hi experts, I would like to ask if it is feasible to limit the accessibility of an SA account in SQL 2005 in a specific database. The reason of doing this procedure is since we are deploying a package software to our client(s) we want to secure our own database to get tampered by our client(s).
