restrict access to database: management

Showing posts with label management. Show all posts

Friday, March 30, 2012

How to script Sprocs permissions in Management Studio?

I am dissapointed that script options are missing in Management Studio. I
never understand why a good feature goes away in a newer version.
Is there any way to script the permissions for stored procedures?
John DalbergDepends on how you script them. If you select the stored
procedure and select "Script Stored Procedure" you won't get
the permissions. If you go from the database level, Tasks
and Generate SQL Scripts (or whatever other ways there are
to invoke the Generate SQL Server Scripts Wizard, you have
more options in the scripting. If you script the stored
procedures for the database and select to include object
level permissions, the grants will be scripted with the
stored procedures.
-Sue
On 10 Mar 2006 19:05:08 GMT, nospam@.nospam.sss (John
Dalberg) wrote:
>I am dissapointed that script options are missing in Management Studio. I
>never understand why a good feature goes away in a newer version.
>Is there any way to script the permissions for stored procedures?
>John Dalberg|||Sue Hoegemeier <Sue_H@.nomail.please> wrote:
> Depends on how you script them. If you select the stored
> procedure and select "Script Stored Procedure" you won't get
> the permissions. If you go from the database level, Tasks
> and Generate SQL Scripts (or whatever other ways there are
> to invoke the Generate SQL Server Scripts Wizard, you have
> more options in the scripting. If you script the stored
> procedures for the database and select to include object
> level permissions, the grants will be scripted with the
> stored procedures.
Not in SQL Server 2005. I found out later that it can be done at the
database level. Still not as convenient as pre 2005.
John Dalberg
> On 10 Mar 2006 19:05:08 GMT, nospam@.nospam.sss (John
> Dalberg) wrote:
> >I am dissapointed that script options are missing in Management Studio.
> >I never understand why a good feature goes away in a newer version.
> >
> >Is there any way to script the permissions for stored procedures?
> >
> >John Dalberg|||Yes...what I wrote was for SQL Server 2005. And I explained
how to do it at the database level in SQL Server 2005.
That's what I wrote from what I can see..."If you go from
the database level..."
-Sue
On 13 Mar 2006 15:36:47 GMT, nospam@.nospam.sss (John
Dalberg) wrote:
>Sue Hoegemeier <Sue_H@.nomail.please> wrote:
>> Depends on how you script them. If you select the stored
>> procedure and select "Script Stored Procedure" you won't get
>> the permissions. If you go from the database level, Tasks
>> and Generate SQL Scripts (or whatever other ways there are
>> to invoke the Generate SQL Server Scripts Wizard, you have
>> more options in the scripting. If you script the stored
>> procedures for the database and select to include object
>> level permissions, the grants will be scripted with the
>> stored procedures.
>
>Not in SQL Server 2005. I found out later that it can be done at the
>database level. Still not as convenient as pre 2005.
>John Dalberg
>
>
>
>> On 10 Mar 2006 19:05:08 GMT, nospam@.nospam.sss (John
>> Dalberg) wrote:
>> >I am dissapointed that script options are missing in Management Studio.
>> >I never understand why a good feature goes away in a newer version.
>> >
>> >Is there any way to script the permissions for stored procedures?
>> >
>> >John Dalberg

How to script Sprocs permissions in Management Studio?

how to schedule integration services package(SSIS) 2005 using Management studio?or is there any

Have anyone successfully accomplished scheduling integration services package using management studio? or is there any other way to do this?

i am scheduling the package to run from SQL SERVER Management Studio using SQL Server Agent,but it is not working.

Help is appreciated.Moving to SSIS forum from Bug Reports Forum.|||

devi_anitha wrote:

Have anyone successfully accomplished scheduling integration services package using management studio? or is there any other way to do this?

i am scheduling the package to run from SQL SERVER Management Studio using SQL Server Agent,but it is not working.

Help is appreciated.

Please provide more details about the error. In the mean time try seraching this forum

Wednesday, March 28, 2012

how to save trigger into table in sql 2005

I have found the node for Triggers in SQL Server 2005's Management Studio and tried to create a 'New Trigger...' but when I save, it saves it to an .sql file but do not attach it to the table.

What am I doing wrong here? I can't seem to attach it. And where is this Assisted Editor ...i can't seem to find it anywhere

creating a trigger is like any other sql statement. You can execute them against a database or you can save the script for later use. You want to execute the statement so instead of hitting save try the execute button or hitting F5

|||

THANK YOU SO MUCH, I MADE IT. Big Smile

sql

Monday, March 26, 2012

how to save a stored procedure with Management Studio?

Hi,

i can make and save a stored procedure in Visual Web Developer (via Database Explorer). It appears then in the list op stored procedure in Management Sudio.

But how to do the same in Management Studio? When i make a sp and i want to save it, Management Studio asks me a name, but put the file in a Projects directory in 'My documents'. It never appears in the list of sp.

Thanks

tartuffe

Simple execute the script. When you make a SP in SSMS, it generates the "Create" or "Alter" statement, so executing the script will not execute the stored procedure, it will create or alter it, accordingly.

|||

Hey,

Try clicking execute instead of the save icon, as the two work differently. Management Studio uses Execute to actually execute against the database, where VWD uses the save button to perform that action, for whatever reason that may be.

|||

Thanks, it works.

Friday, March 23, 2012

How to run SQL Server Management Studio

We have installed SQL Server 2005 on a machine running Windows 2003. We found
out that there is no Enterprise Manager, but Management Studio. We have no
idea how to run this.
We tried to remove SQL Server, but Add/Remove doesn't remove it. Just only
removes it from the list.
We hope we can get an answer, because we are a serious evaluating SQL 2005
over Oracle 10g.
Thanks for your help.
MattStart -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management
Studio
'
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:DD381736-D551-4BB6-9488-D2E61C97E33F@.microsoft.com...
> We have installed SQL Server 2005 on a machine running Windows 2003. We
> found
> out that there is no Enterprise Manager, but Management Studio. We have no
> idea how to run this.
> We tried to remove SQL Server, but Add/Remove doesn't remove it. Just only
> removes it from the list.
> We hope we can get an answer, because we are a serious evaluating SQL 2005
> over Oracle 10g.
> Thanks for your help.
> Matt|||We would assume that it was that simple, but in the Microsoft SQL Server 2005
menu only Configurations Tools is shown.
We have seen another post with the same problem but they were trying to
install it on XP, while we have installed it on Server 2003.
The installation program seems to run so smooth, so we can't imagine that
something crucial like this was overlooked.
Any help is appreciated.
Matt
"Adam Machanic" wrote:
> Start -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management
> Studio
> '
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:DD381736-D551-4BB6-9488-D2E61C97E33F@.microsoft.com...
> > We have installed SQL Server 2005 on a machine running Windows 2003. We
> > found
> > out that there is no Enterprise Manager, but Management Studio. We have no
> > idea how to run this.
> >
> > We tried to remove SQL Server, but Add/Remove doesn't remove it. Just only
> > removes it from the list.
> >
> > We hope we can get an answer, because we are a serious evaluating SQL 2005
> > over Oracle 10g.
> >
> > Thanks for your help.
> >
> > Matt
>
>|||Did you already go back through the installer and make sure that the correct
options were selected? Also try switching into Advanced mode to be certain.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Matt" <Matt@.discussions.microsoft.com> wrote in message
news:5D45FE06-D501-4CC9-8DC0-3C786F6ECFEE@.microsoft.com...
> We would assume that it was that simple, but in the Microsoft SQL Server
> 2005
> menu only Configurations Tools is shown.
> We have seen another post with the same problem but they were trying to
> install it on XP, while we have installed it on Server 2003.
> The installation program seems to run so smooth, so we can't imagine that
> something crucial like this was overlooked.
> Any help is appreciated.
> Matt
> "Adam Machanic" wrote:
>> Start -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management
>> Studio
>> '
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "Matt" <Matt@.discussions.microsoft.com> wrote in message
>> news:DD381736-D551-4BB6-9488-D2E61C97E33F@.microsoft.com...
>> > We have installed SQL Server 2005 on a machine running Windows 2003. We
>> > found
>> > out that there is no Enterprise Manager, but Management Studio. We have
>> > no
>> > idea how to run this.
>> >
>> > We tried to remove SQL Server, but Add/Remove doesn't remove it. Just
>> > only
>> > removes it from the list.
>> >
>> > We hope we can get an answer, because we are a serious evaluating SQL
>> > 2005
>> > over Oracle 10g.
>> >
>> > Thanks for your help.
>> >
>> > Matt
>>|||Thanks for your help, but I tried already all of that.
The bizarre thing is that it doesn't remove the installation through
Add/Remove Programs, while it takes it from the list. When you try to install
certain components (through Advanced) then it tells you it is already there.
Another problem is that when you install subcomponents it won't let you
install other subcomponents later.
This product definitely has installation issues and I hope they will be
resolved as the pack will now start evaluating (like us) and they're turned
away before they can even try away. A smooth installation is software
marketing 101.
Any help is appreciated. Even help to remove the product.
"Adam Machanic" wrote:
> Did you already go back through the installer and make sure that the correct
> options were selected? Also try switching into Advanced mode to be certain.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> news:5D45FE06-D501-4CC9-8DC0-3C786F6ECFEE@.microsoft.com...
> > We would assume that it was that simple, but in the Microsoft SQL Server
> > 2005
> > menu only Configurations Tools is shown.
> >
> > We have seen another post with the same problem but they were trying to
> > install it on XP, while we have installed it on Server 2003.
> >
> > The installation program seems to run so smooth, so we can't imagine that
> > something crucial like this was overlooked.
> >
> > Any help is appreciated.
> >
> > Matt
> >
> > "Adam Machanic" wrote:
> >
> >> Start -> Programs -> Microsoft SQL Server 2005 -> SQL Server Management
> >> Studio
> >>
> >> '
> >>
> >> --
> >> Adam Machanic
> >> Pro SQL Server 2005, available now
> >> http://www.apress.com/book/bookDisplay.html?bID=457
> >> --
> >>
> >>
> >> "Matt" <Matt@.discussions.microsoft.com> wrote in message
> >> news:DD381736-D551-4BB6-9488-D2E61C97E33F@.microsoft.com...
> >> > We have installed SQL Server 2005 on a machine running Windows 2003. We
> >> > found
> >> > out that there is no Enterprise Manager, but Management Studio. We have
> >> > no
> >> > idea how to run this.
> >> >
> >> > We tried to remove SQL Server, but Add/Remove doesn't remove it. Just
> >> > only
> >> > removes it from the list.
> >> >
> >> > We hope we can get an answer, because we are a serious evaluating SQL
> >> > 2005
> >> > over Oracle 10g.
> >> >
> >> > Thanks for your help.
> >> >
> >> > Matt
> >>
> >>
> >>
>
>

How to run SQL Server Management Studio

Friday, March 9, 2012

How To Retrive Data If There Are Large No. Of Datas In Table(by Minimum Time Conusum

MY PROJECT IS HOSPITAL MANAGEMENT SYSTEM
AND THERE ARE ABOUT 700 PATIENT DAILY VISIT THE HOSPITAL/ ADDING NEW PATIENT(LARGE HOSPITAL).
SO, LARGE NUMBER OF PATIENTS ARE THERE. NOW SUPPOSE AFTER FEW YEARS THERE ARE ABOUT 70000000 PATIENTS RECORDS IN DATABASE , THEN TO FETCHNIG SOME PATIENT'S DETAILS IS VERY TIME CONSUMING [ BY SELECT * FROM PATIENT]
THEN WHAT SHOULD I DO? ANY ONE HAVE ANY IDEA?

AND ONE MORE THING I WOULD LIKE TO KNOW THAT HOW THE IMAGES WILL STORE IN DATABASE ACCESS?
PLS REPLY SOON

RAHUL PRAJAPATI
rahul_nadiad@.yahoo.com

Quote:

Originally Posted by RAHULPRAJAPATI

AND ONE MORE THING I WOULD LIKE TO KNOW THAT HOW THE IMAGES WILL STORE IN DATABASE ACCESS?
PLS REPLY SOON

RAHUL PRAJAPATI
rahul_nadiad@.yahoo.com

Hi there,

The structure of the database isteslf can play a major role is enabling fast access, heard of database normalization? Why not use database normalization to restructure your current database.

In relational database theory, normalization is the process of restructuring the logical data model of a database to eliminate redundancy, organize data efficiently, reduce repeating data and to reduce the potential for anomalies during data operations. Data normalization also may improve data consistency and simplify future extension of the logical data model. The formal classifications used for describing a relational database's level of normalization are called normal forms

Kindly refer to below link for further reading & understanding, hope it helps.

http://en.wikipedia.org/wiki/Database_normalization
You can use Binary large object (BLOB technique in order to be able to safe graphics and other related material in database.

Good luck & Take care.|||Hi Sashi,
I am also a newbie to these things.
My understanding is that, data normalization eases the adding the data and managing the constraints.
But normalisation results in more number of tables than without (or lower level of) normalisation.
So doesnt it mean that it slows the data access as it requires to join more number of tables.

Please correct me if I am wrong.

-Subhash.

Quote:

Originally Posted by sashi

Hi there,

The structure of the database isteslf can play a major role is enabling fast access, heard of database normalization? Why not use database normalization to restructure your current database.

Kindly refer to below link for further reading & understanding, hope it helps.

http://en.wikipedia.org/wiki/Database_normalization
You can use Binary large object (BLOB technique in order to be able to safe graphics and other related material in database.

Good luck & Take care.

How to retrieve the T-SQL for a particular subscription?

In SQL 2005 NS, I have created a custom subscription management interface that allows for condition actions. I can currently create, update and delete subscriptions without a problem.

However, I would like to be able to retrieve the T-SQL that will be run when the event is fired to test it for correct syntax etc. I am hoping to notify the user of any possible issues as soon as the subscription is created as opposed to waiting for an error when the event fires. Since many matching fields are simply text entries, the user may enter some data values that cause the matching statement to fail and I would like to catch that error and notify the user at subscription creation.

To do this, I plan to create the subscription with a "disabled" status. The next step would be to retrieve and run the actual T-SQL that will be executed when an event is fired. If there are no errors then I will update the subscription to "enabled".

I cannot seem to find a class member or stored procedure that returns the resulting T-SQL for a single subscription.

Any help would be appreciated.

Thanks

Maybe, I didn't understand exactly what you are trying to accomplish, but as far as I can see, you do not need to retrieve T-SQL for a subscription creation. Instead, you can just do your validations in your SMI class. And you can maximize your GUI usage for validations.

For example, use as many drop-down menus or listboxes as possible instead of combo boxes, textboxes, etc. Let users choose the predefined values.

Also, say, for example, that there is a field in your subscription class which is a DateTime value. Don't let the user enter the date. Give him a Calendar or DateTimePicker controls.

If the user still has to enter some values manually (such as money values), do validations in GUI before calling a SMI method, such as

try {

double amount = Convert.ToDouble(textBox1.Text);

// invoke SMI method for creating or modifying the subscription

} catch (Exception ex) { // show error message to the user }

|||

Thank you for your response.

I am already performing these types of validations and using dropdowns/calendars where appropriate.

However, I also wanted to take it a step further in case something is missed in the validations. The bottom line is; you never know what a user is going to enter in a textbox.

I would like to retrieve the actual SQL that will be run for an individual subscription and test it.

Sunday, February 19, 2012

How to restore SQL 7 DB to SQL 2005

Hi, All
I am trying to restore SQL 2000 DB into SQL 2005 Database.
I backup the database from SQL 2000.

From Management Suit, I try to restore database but I can not see network drive from there even though SQL serveris running under network account.
I could see network drive from SQL 2000 or at least I can type path to find backup file. However I can not do this..

My Q is:
1. How can I restore this SQL 2000 db to SQL 2005 using network path?
2. Since backup is SQL 2000 database, when I restore into SQL 2005, does restore upgrade system tables and other schema to SQL 2005 as well?
If not, what is the best practice to upgrade this database into SQL 2005?
Upgrading SQL 2000 current server to SQL 2005 is not an option at this point.
Eventually porduction server will be scrup and install SQL 2005 and then restore DBs into production machine...

Thx in advance
Jay
1. We support restoring from network paths. You need to ensure the sql service has access to the path. The GUI would also need access to the network path. You might try using an explicit UNC rather than drive letters.

2. We automatically upgrade the system tables as part of the restore (or attach).|||

Steve Schmidt

Thanks for the post here. But can you lay it down in plain english. What I mean is I need a step by step. What I have done is I have the backup for my SQL Server 2000 databases. I have created the tables in SQL Server 2005. Then I go to restore the files using the Restore Database in SQL Server 2005. I select the newly created database in the To Database on the general tab. Select From File on the general tab and browse for the backup file which I find. Check the restore check box then hit OK.

I get the following error.

// Start Error Message //

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'VM-WIN03SERVER'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'shoplist' database. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476
// End Error Message //

If I do an import it works just fine but you loss all your key info and other important elements just like when you do a import from SQL Server 2000 to SQL Server 2000.

|||

Your procedure is correct.

There is an "overwrite existing database" option on the "options" page of the restore dialog. You need to check it to avoid the error.

The error indicates that the backup set was not created by backing up the current database you plan to overwrite. So if you are sure that you have the correct backup set, check that box and the restore will procede.

Hope that helps.

|||

Hi,

I was having a similar problem. I am trying to restore a .bak file from another sql server 2000. We are looking at moving all the DB's to the new server running sql 2005.

I have tried a restore using the managment studion via right click on the databases and also tried making a new data base that I may restore into like the example above. I both cases I get an error:

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server 'SQLSERVPRO2'. (Microsoft.SqlServer.Smo)

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program Files\Microsoft SQL Server\MSSQL\Data\Robert_Taylor_Sys.mdf" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

What the problem was here was the paths where the origional data files are not same in sql 2005 x64. Under the options section there is a place to redirect the files to restore.

|||

I am getting the same error msg. We did backup in SQL 2005 itself and wanted to try the restore operation but throws the same error. I did choose "force overwrite existing db" but still the same.

Any idea?

|||Anyone had solution to this? I'm experiencing the same error.|||

This error:

indicates that restore can not find the path:

C:\Program Files\Microsoft SQL Server\MSSQL\Data

You either need to create the path yourself, or:

1. use "WITH MOVE" in the T-SQL

2. use the GUI to specify an alternate location for each database file

|||

To restore a SQL 2000 database on SQL 2005

create a new database in 2005 with the same name and compatibility set for SQL 2000 or the version you want to restore from.

restore this database with options 'overwrite existing database'

i did this from one server to another, but it is also possible from a .bak file ofcourse.

gtx. YAK

|||I have done this with about 30+ DB. The fastest way and most reliable was to back up the DB from the SQL 2000 machine on to the SQL 2005 machine via EM. Then just login to the sql 2005 machine and use the restore database from SSMS. This way you can insure all of your tables will stay exactly the way that you had them ie identities. I have tried many different ways and this is by far the most straight forward. Of course you could script the backup and the restore if you are going to be doing this often or have more than one DB to convert/move.|||

I am trying to restore a database from SQL7 to SQL2005 but get a message:

System.data.sqlclient.sqlerror: The backup set holds a backup of a database other than the existing database.

I have tried the restore and the inport option for the sql2005 database.

****Update

I used the overwrite option with a complete backup file. The procesws seamed to work but now I have a (6.5 Compatible) next to the dadabase name and there is nothing (tables, views, security, etc..) associated with the database. Now I can not delete or rename the database. Those options are not supported: 6.5 database compatibility level is not supported.

Any ideas?

|||

Okay since this forum has failed to answer the question, here's the process in SSMS.

1) Go to restore database

2) Select the database that you want to back up to

3) Locate the backup file on disk. You may have to put it into the MSSQL Server -> MSSQL.1-> MSSQL -> Backup Folder. It must be a .bak file.

4) Select the back that want to restore from the available backups.

5) Go to the top left "options" property and when you do that select "overrite existing database".

6) Now make sure that the path to the files on database to be restored are correct in this same dialog view. Look at the paths to the database file and the log file and make damn sure that they are the correct ones for the database to be restored. The problem here is that those paths are going to be for the filesystem that the backup came from, not the one you are goning to put the restore onto. That's the big problem here.

|||

it working with my good

just go options then chang the path for .mdf and ldf file

like : C:\Program Files\Microsoft SQL Server\MSSQL\Data\newname.mdf

C:\Program Files\Microsoft SQL Server\MSSQL\Data\newname.ldf

choose new name for the database

clear all check box overwrite options

|||

Guys,

I have experienced challenges that only Microsoft can supply,

When all looks fine and it still doesn't work then it is time to copy and paste. I experienced similar problems and found when I copied the file path from windows explorer directly into the "options" -> "restore database files" ->"Restore As", it worked. I did not know that it was case sensitive, if not my DB was just tempremental.

If you follow the previous steps and copy and paste then it will hopefully work.

|||

Hey "Reformed ", yes it worked the way you told us. :) Thanks it was a great help!

The only thing that was not correct was the path ... which you highlighted.

Thanks again!

Friday, March 30, 2012

How to script Sprocs permissions in Management Studio?

How to script Sprocs permissions in Management Studio?

How to script Sprocs permissions in Management Studio?

how to schedule integration services package(SSIS) 2005 using Management studio?or is there any

Wednesday, March 28, 2012

how to save trigger into table in sql 2005

Monday, March 26, 2012

how to save a stored procedure with Management Studio?

Friday, March 23, 2012

How to run SQL Server Management Studio

How to run SQL Server Management Studio

How to run SQL Server Management Studio

Friday, March 9, 2012

How To Retrive Data If There Are Large No. Of Datas In Table(by Minimum Time Conusum

How to retrieve the T-SQL for a particular subscription?

Sunday, February 19, 2012

How to restore SQL 7 DB to SQL 2005

restrict access to database

Blog Archive

About Me