Automated Role Creation

Oct 16, 2006

i seem to have some misunderstanding about how roles work in sql server
2005. i see that i can add a role to a database
(dbname->[right-click]->properties->permissions->[add ...]. THis allows
me to add either users or roles. Users can be added programmatically
using sp_grantdbaccess @username, but this does not allow for addition
of roles to access the database (i.e., sp_grantdbaccess @rolename does
not work).

Is there some other command that is used to add a role to the
database's permissions list? Seems there must be since all the other
parts of the permissions chain work quite well:
* sp_addrole
* sp_addrolemember
* grant <permissionon role::@rolename
etc.

So the missing ingredient of something like sp_grantdbroleaccess is
what I need and can't seem to find it anywhere!

Thanks much for any help

View 4 Replies


ADVERTISEMENT

Automated Creation Of Snapshots

Oct 17, 2007

Hi All..

I am trying to create daily automatic snapshots by using the code below...




Code Block
declare @MyDay varchar(20)
declare @query varchar(1000)
declare @DatabaseName varchar(128)
declare @snapshotName varchar(128)
declare @snapDataName varchar(128)
declare @snapFileName varchar(128)
declare @snapFilePath varchar(128)
set @Myday = (Select datename(weekday,getdate()))
print 'It is ' + @MyDay
Set @DatabaseName ='Cerritos_Net'
Set @SnapDataName='Cerritos_Net_Data'
Set @SnapshotName ='Cerritos_Net_Snapshot'+'_'+@MyDay
Set @SnapFilename ='E:ShareINCOMINGSnapshotsDailyCerritos_Net_Data'+'_'+@MyDay+'.ss'
Print 'Snapshot name is ' +@SnapshotName
select * from sys.databases where source_database_id =db_id(@databasename) and name = @SnapshotName
if @@rowcount <>0
begin
set @query = 'Drop database '+ @SnapshotName
print @query
exec(@query)
end
set @query ='Create database '+ @SnapshotName + ' on (Name = '''+@snapDataName +', FileName="'
+@SnapFilename +'") AS SNAPSHOT of '+ @databasename+';'
print @query
exec(@query)




But I keep getting this error





Code Block
It is Tuesday
Snapshot name is Cerritos_Net_Snapshot_Tuesday
(0 row(s) affected)
Create database Cerritos_Net_Snapshot_Tuesday on (Name = 'Cerritos_Net_Data, FileName="E:ShareINCOMINGSnapshotsDailyCerritos_Net_Data_Tuesday.ss") AS SNAPSHOT of Cerritos_Net;
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'Cerritos_Net_Data, FileName="E:ShareINCOMINGSnapshotsDailyCerritos_Net_Data_Tuesday.ss") AS SNAPSHOT of Cerritos_Net;'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'Cerritos_Net_Data, FileName="E:ShareINCOMINGSnapshotsDailyCerritos_Net_Data_Tuesday.ss") AS SNAPSHOT of Cerritos_Net;'.




I think I placed my quotations right.....

View 3 Replies View Related

Help With ROLE Creation

Jun 16, 2008

I am a promotional DBA and need help with some code a developer wrote that errors out.
He is trying to create a role and to my knowledge there is nothing wrong with the syntax but it gives an incorrect syntax error.

CREATE ROLE 'PPS' IDENTIFIED BY 'stressboy';

error: Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'ROLE'.

we tried it with ' ' & "" and without either. I'm sure this is an easy one for you all. Thanks.

View 1 Replies View Related

Sql 2005 Database Role Vs Application Role ?

May 18, 2007

Ive been reading over the documentation and some stuff online, but I still dont really understand what the difference is and when you would use one vs the other. Can someone put it in simple terms for this dummy (me) ?

thanks

View 2 Replies View Related

SQL 2012 :: Trace Creation Time Is Different From Time Check Creation

Oct 8, 2014

We 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".

View 0 Replies View Related

Can't Make Database Role A Member Of Another Database Role In 2005.

Jan 9, 2006

In sql server 2000, I created some custom database roles called ProjectLeader and Developer.  I would make these roles a member in the fixed database roles so that I would only have to add the user to the ProjectLeader or Developer role once and they would presto-magico have the security I wanted them to have with no unecessary mouse clicking.  I'm not sure how to repeat this process in 2005?  Management Studio doesn't seem to allow you to add a role as a member in another role.  Is there a work around or solution for this?

View 1 Replies View Related

Application Role, App Role

Jan 23, 2008

 Hi all, I am trying to connect to the database using application role.  But gives an error An error has occurred while establishing a connection to the
server.  When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)
 for the given connection string Dim connstring
As String = "Data Source=Northwind;Initial
Catalog=OrderProcessing;Persist Security Info=True;UserID=application_login;Password=wewewe;"

        Dim cmd
As SqlCommand

        Dim
param As SqlParameter

        Dim
cookie As Byte()

        Dim cn As New
SqlConnection(connstring)

        If
(cn.State = ConnectionState.Closed) Then

            cn.Open()

        End If Please help.. 

  Thanking you, Nirmala  

View 2 Replies View Related

Automated Dataimport

Oct 28, 2005

Hi everyone,i'm new to ado.net and need some help. My problem:got one DB with real bad tablestructureand one new DB (structured!)i need do do an automated import from the old to the new DB, with check if the data row is new or updated.I try to do it this way-> 2 datasets merged together  ->Problem: new entries in destination dataset have row state unchangedother way  i tryed  add rows from source to destination with add funktion -> problem no check if data exist and maybe is changedSo how can i get the merge working or how can i implemt an effective funktion to solve this problem. Thanks in advance!

View 5 Replies View Related

SQL Automated Backup Help

Apr 3, 2000

Hi, looking for some paths to take on this one. I have to backup the database to a LS120 floppy
drive. It has to be automated so the user can just click on a desktop icon and away it goes. Probably
an easy script buy i sure would appreciete some help on this one.

Thanks
Troy

View 1 Replies View Related

Automated Export?

Oct 19, 1999

Hi. I am using SQL 7.0.

Here is the situation... there are random (.csv) files being dropped into a specific directory on a daily basis. Is it possible to have SQL server automatically pick up this file and run it through the Import/Export data option so that it creates a SQL table from the original data? I need this whole sequence to be automated. Is this possible? Somebody please help... I will appreciate it greatly. Thanks a lot!

Alan

View 2 Replies View Related

Automated Installation Of SQL 6.5

Aug 28, 1998

I am trying to automate the installation process of our Help
Desk software `Expert Advisor`. It uses a MS SQL Server 6.5
database. I can figure out the automation of Expert Advisor,
but is it possible to automate the installation of the
SQL drivers for each workstation? I don`t want or need any of the
utilities such as ISQL/w installed along with the driver--just the
SQL driver itself. Is there a runtime version of the database I should
use? I`m fairly new to SQL so please forgive me if I seem naive.

Any suggestions? Any help would be appreciated.
Toni

View 2 Replies View Related

Automated .txt Import Pt. 2

Jun 29, 2005

I don't know what happened on that first thread, but here it is again.

Thanks for your help.

Hello,
I have set up a .bat file to automatically export all of my server logs to a directory on my SQL server. From there I am trying to import those files into an event_log table that I have set up. (from there I am going to use Cold Fusion to parse the table each day and e-mail me with anything I should look at) The estimated ROI for my company is roughly $14.5k, it's easy so if you don't already have an error log monitoring solution, check this out!

Anyway I tried to use BCP in my .bat file but it keeps coming back with an unexpected end of file error.

Can I get some help with either:
1. Find a command line tool to import these .txt files with
-or-
2. Help me with the BCP issue?

Thanks in advance.

View 3 Replies View Related

Automated SQL Documenation?

Jul 2, 2004

I am an intern at a university and we are looking for a way to automatically document tables, queries, etc. ColdFusion is the platform used, and SQL Server 2000. Is there a way to have the documentation performed that will capture tables, constraints, etc.? I have found some, but so far, none are working. Please help if you can!!! :confused:

View 4 Replies View Related

Automated Restore

Oct 17, 2005

Hi,

I have 2 databases, one called "ManagementDB" and the other called "ManagementDBYesterday".

Basically I get asked very frequently to look at yesterdays data for various reasons and I thought it'd be a good idea to always have yesterdays data to hand instead of restoring all the time.

I have tried DTS but this takes too long, I've also thought of replication however the database is modified too frequently for this to be easliy implemented.

Restoring from a backup is quite quick so I thought if I could somehow automate this, I'd have a readily available copy of yesterdays data to hand.

I have a mon-sun daily backup so I need to write some SQL script to restore from the correct backup to the "yesterday" database.

I've only used restore via the enterprise manager and I was wondering if anyone here could help.

kind regards,

Mike,

View 1 Replies View Related

Automated Import Of XLS To SQL

Jan 6, 2004

I have a customer who insists on emailing an excel spreadsheet to me to be uploaded to a sql database. I came across a script at one point that laid out how I would script this out. Basically it was in parts, 1. open email program and get attachment. 2.After saving attachment closing email program in order to be used the next day or next time an attachment was sent. 3. import the attached spreadsheet into SQL database. I would also like to be able to schedule this to run with the built in Windows 2000 scheduler.

Can anyone help me out on this one??

Thanks,
Will

View 5 Replies View Related

Automated Service

Apr 11, 2006

Hi,
I'm working on a program that is some kind of organizer for all the tasks that employees have to carry out. A part of this program should check the deadline from the tasks, and send a mail if the deadline is in less than 5 days.
I'm using asp .net 2.0 & sql server 2000
Can anyone tell me how I can do that, or put me on track with some key words to google for?
thx.

View 4 Replies View Related

Automated CSV Import

Oct 21, 2007

Hi guys,

I'm pretty new in SQL Server and trying to build up a BI Solution for my firm. We have got monthly data about revenue headcounts and so, which should be integrated into a DW.

I was wondering whether there is an automated way to import this csv data in SQL like in a batch, because the users are not familiar with SQL Server at all. The structure of the data will be the same all the time, since it comes from legacy systems.

YOur help would be highly appriciated:)
demon

View 2 Replies View Related

Automated Script

Nov 8, 2007

Hi All,

I have around 80 databases in my one of sql server instance.
we hve to regularly send the database wise health report to the client.
The following fields mentioned by the client in excel sheet

Name of database
Logical file name of .MDF
.Mdf File location drive
Size of the file in MB
Physical file location(full path of .mdf file)
Maxsize
Growth

Now a days we have to check databases one by one.
Could anyone provide me the script which automatically fetch all of required fields information from all of the databases.

Urgent help will be appreciable.

Frozen

View 2 Replies View Related

Automated Backup

Jan 28, 2008

Was unsure where to place this post.
Windows Server 2003R2 w/ Sp2
MS SQL 2005

I currently use MS SQL 2005 to host my Citrix Datastore DB. I manually run a daily back up (Tasks>Backup).
How difficult would this be to automated? I have googled this without luck.

Any insight appreciated.

Thanks,
Kerry

View 2 Replies View Related

How To Insert An Automated ID?

Jun 12, 2007

Unfortunately don't I know, how I can adjust this or whether SQL code is needed and if, where?? I do not know myself unfortunately yet so well with the subject out, from therefore am grateful I for each assistance. Many greetings M-l-g



Hello,

I have a problem with an integration services project of SQL server 2005. I have a text file with various data, which can be read out easily with a dataflow task and a flat file source. The data shall be transferred into a target database which I developed according to a star pattern. The fact table can be generated automatically if and only if all data belonging to a dimension table is inserted by myself. Of course, this procedure is not reasonable while having a vast amount of data. That is the reason why I want to generate the dimension tables automatically, too. A dimension is build e.g. like this: ApplicationID | ApplicationName. So, only two columns are available. But I don't know how to insert an automated ID.



I would be thankful for any help.M-l-G

View 7 Replies View Related

Automated Unzip

Apr 15, 2008



Hi,

how would i unzip a password protected file after downloading it from sftp server.

thanks

View 2 Replies View Related

Stop Automated Restore

Aug 3, 2006

We have automated the process of backing up and restoring the databases. But the problem is One database is restoring from several hrs ..though it is supposed to take only a few mins. So there is some issue with it and I want to find out how I can stop the restoring process.
Its not a command am running in query analyser or Enterprise manager, but its automated so i cant see the command to stop it.
 Any ideas?

View 2 Replies View Related

Sending Automated Sql Emails

Mar 12, 2007

Hi
I am using asp.net framwork 2 with sql 2005 express edition. Can anyone please provide me any sample of sending automated emails (like welcoming people when they register to my website, or if their personal details have been updated....). or any e-book/website ref will do. I am not very expert in sql/db and smtp (or other email) stuffs. So please help me.
With thanks

View 6 Replies View Related

Sql Automated Task Stuff

Jan 14, 2008

Where can i learn about making the sql server execute task when a certain situation happens. Like lets say make the server delete all entrys older than 40 days? Or have a entry deleted automatically when a certain situation happens like lets say it take 4 votes and a entry get deleted how do i make it do that automatically

View 2 Replies View Related

Automated DTS Package Problem

Jan 27, 2004

I need to create an Automated DTS package. At present I am running it manually each morning to import our phone switch data to an SQL table. The reason I had to go this route is this.

Because the Access tables are locked, I have a System DSN that connects to the System.mda file, this in turn gives me access to the locked tables to extract the data I need into Excel. I then drop the existing SQL table and run the DTS package recreating the SQL table with the data from the Excel file. I do not want to use OLEDB to connect to the Access table from within my application because it changes each month and of course the traffic load, I have as many 150 clients hitting the DB at one time.

I actually need the DTS package to update the SQL table with the latest data.

I'm no DTS guru, so how can I do this automagically?

View 1 Replies View Related

Automated Data Export

Feb 12, 2004

Here's my process:
1. I have a form where someone enters certain criteria for some data…
2. The form than populates a record in a table with all the criteria…
3. I create a query based on the selected criteria for exporting…

How can I automate the process (maybe using DTS) where SQL server will automatically export the data for me using a stor proc? Create a file, populate the file etc…. or will I need to create an external App to do this?

View 2 Replies View Related

Automated Birthday Email

Apr 13, 2006

I have a SQL server 2005 database. I want to send a user an email when it's his birthday. His birthdate is stored in the "birthdate" column...How can I automate this?

View 5 Replies View Related

Remove Automated Task?

Sep 20, 2002

I have been playing around with the enterprise manager and selected Backup Database from the action menu and schedule this task for an odd time for testing.

How do I go about removing this task?

Whenever I go to the same menu item again, it just starts over .. like I don't have the task already setup.

In general, how do you remove automated tasks?

Thanks,
bf

View 1 Replies View Related

Automated Killing Of Processes

Aug 30, 1999

I wish to select processes from sysprocess that are SLEEPING and more than a certain time old (say 10 minutes) so that I may KILL them. I can get the query to do the select, but how do I KILL the process? I have tried selecting the SPID into a local variable and then trying KILL @var_name, but I get "Incorrect syntax near '@var_name'".

I have tried all of the resources that I can find, but without success. Is this possible? If so, how do I go about doing it?

Thanks in advance.


Matthew

View 3 Replies View Related

Remote, Automated MS SQL Backups.... ?

May 9, 2005

Hi,

I'm looking for a bit of advice about the best way to make automatic remote backups of a MS SQL server.

I currently run a dedicated windows 2003 standard server that i have complete control over, but my databases are hosted on a shared machine that i have no control on.

Is there a way to automate backups from the SQL database onto the windows machine?

Any suggestions on how to do this would be appreciated!

View 4 Replies View Related

Automated Generate SQL Scripts

Apr 11, 2002

Is there a way to automate the generation of sql scripts. I addition to backing up the database I like to have a backup schema. Just wondering if this can be done.

Thanks !!!

Steve

View 2 Replies View Related

Automated Sql Injection Detectors

May 20, 2007

anybody ever use any of these...

http://www.security-hacks.com/2007/05/18/top-15-free-sql-injection-scanners ?

thoughts? preferences?

View 1 Replies View Related

Problems With Automated Jobs

Feb 10, 2004

Hi guys

I have setup a sequence of jobs to run maintenance on one of our SQL Servers.

The sequence of jobs is as follows:

Backup database
Archive database
Recreate indexes
Shrink database
Update statistics
Backup database

There is also another step which is Restore Database. This step is only called if the Archive, Re-index or Shrink jobs fail.

I made a slight script error and so when the job ran on Sunday it deemed the job a failure and attempted to restore the database, that wasn't a problem as the error was deliberate to test the restore process.

The restore process failed and logged the following message:

Job 'Sunday Maintenance - Full' : Step 8, 'Restore Database' : Began Executing 08/02/04 22:58:40

Msg 3101, Sev 16: Database in use. The system administrator must have exclusive use of the database to run the restore operation. [SQLSTATE 42000]
Msg 3013, Sev 16: Backup or restore operation terminating abnormally. [SQLSTATE 42000]

The syntax I used to do the restore was:

RESTORE DATABASE Emvolve
FROM DISK = 'H:DbdataBackupEmvolve_Full_Before.BAK'
WITH REPLACE

How do I get around this error message to complete the restore automatically? Any services that access this database are stopped prior to this process running so nothign should be accessing SQL.

Any ideas would be appreciated as I am only a learner as far as SQl goes.

Cheers

Paul


:)

View 3 Replies View Related







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