Is there a way to give someone the right to create only views and no other objects in a database? Currently I have given the individual ddladmin database rose, but would rather be more restrictive?
View 1 RepliesI am trying a create views that would join 2 tables:
Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape
I want to create a view that looks like
ID, Color, Size, Flavor, Shape
I am trying to replace object name in views , triggers, stored procs, UDF,TVF etc.I have created a automated script to replace 'dbo.Cust' with 'dbo.Customer' in all objects and generate script as ALTER Statements. some objects are still scripted out as Create. Reason is it has some extra space in between
CREATE TABLE #test1(
[NAME] [nvarchar](128) NOT NULL,
[DEFINITION] [nvarchar](max) NULL,
[DEFINITION_bk] [nvarchar](max) NULL,
[type] [char](2) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
[code]....
I'm trying to create a report which would give the latest transaction on a database, which all sit on different servers. I wanted to know if there is an a simple way tracking the latest transactions instead of getting the information from the database tables.
View 3 Replies View RelatedNeed to create a user in sql server provide grants to few system tables to the above user.
View 10 Replies View RelatedHi,I have to prepare an ER diagram for the objects in my SQL Serverdatabase. I have used the option "DIAGRAMS" in EnterPrise Manager ofSQL Server 2000. It is creating the diagram for the selected tables(but the diagram contains only the table which i have selected. It isnot displaying its depended tables). But i am not able to export it toany of the flat file like MS WORD or paint. I want this diagram to beuploaded to one Flat file and send to my customer for his reference.Could anyone suggest me how to create the ER Diagram for the SQL 2000database and export it to a flat file.Thanks for your help in advance.-Rao
View 2 Replies View RelatedMySql has a statement like:
SHOW CREATE TABLE tablename;
that returns the precise CREATE TABLE statement for the specified tablename.
Sql Management Studio also allows scripting Create Statement for any object by right-clicking it.
But I want to do this programatically, and fetch CREATE statements for Tables, Procedures & Views.
How can I retrive CREATE statements for Database objects progrmatically in Sql Server???
I've just installed SQL Server 2005 Developer Edition, upgraded from SQL Server 2000, and have encountered a problem in trying to query against the System Catalogs.
I've tried SELECT * FROM sys.objects in addition to SELECT suser_name( role_principal_id ) FROM sys.server_role_members. In both cases I receive the following error: Invalid object name 'sys.objects' or 'sys.server_role_members' in the second example.
I changed the properties of the registration for my local db instance to use SQL authentication in lieu of Windows authentication. I used the sa login account to see if that made a difference, no go.
Can someone *please* tell me what I'm doing wrong here?
I would also like to know if it's possible to run a query confirming the user account I'm logged in to the system as.
Thanks!
I'm trying to copy data over using a Transfer SQL Server Objects Task however I get problems trying to copy over views. I get the similar error (pre sp1) when using import/export wizard and copying over views. Now with SP1 the views just don't transfer. I'm transferring from SQL 2005 to SQL 2005. The error I get is:
Error: 0xC002F363 at Transfer Data (NEW), Transfer SQL Server Objects Task: Table "vw_XXXX" does not exist at the source. Task failed: Transfer Data (NEW)
I know the view exists at the source because:
A) I can select it in the Views List Collection within the Transfer SQL Server Objects Task
B) I'm using the view on the source
Somehow the views are not transferring. Anyone have any ideas?
Hi!
There is a view in our replicated SQL-2000 database, that returns all user tables and views with replication state (0 if not included into publication, 1 if included):
Code Snippet
CREATE VIEW [dbo].[ViewREPL_PublishedObjects]
AS
SELECT TOP 100 PERCENT
CASE [xtype]
WHEN 'U' THEN 'Table'
WHEN 'V' THEN 'View'
ELSE NULL END AS [Object Type],
[name] AS [Object Name],
CASE WHEN [replinfo] = 0
THEN 0 ELSE 1
END AS [Replicated]
FROM [sysobjects]
WHERE
[xtype] in ('U', 'V')
AND [status] > 0
ORDER BY
(CASE [xtype]
WHEN 'U' THEN 1
WHEN 'V' THEN 2
ELSE 10
END),
[name]
Now we need to upgrade our database to SQL-2005, but [sysobjects] table have been changed, so neither Replicated state could be determined according on [replinfo] column value, nor User/System object according on [status].
So, I need a view with same functionality, that will work under SQL-2005 and 2008.
Please, help!
I have a table with plant types and plant names. Certain plants are grouped on a custom field, currently called Field. I am trying to create a query that will give me a result set containing the primary order on Type, but need items with the same 'Field' value grouped by each other.For example, the following shows a standard query result with "order by Type", ie select * from plants order by Type
Code:
ID Type Name Field
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
5 Type5Name5(group2) -group2
6 Type6Name6(group6)
But I want it to look like this, with fields of the same value located next to each other in the result set (but still initially ordered by Type)
Code:
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
5 Type5Name5(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
6 Type6Name6(group6)
I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?
View 10 Replies View RelatedWe are having trouble figuring out how to create a view for this scenario:
We have a status log table that holds an order number, statusdatetime, and statuscode. This table will have multiple status' for the same order number. I want to create a view that will give me the most current status (by statusdatetime) of each order number. This view would show: order number, statusdatetime, and statuscode.
Here is a sample of the data:
Order numberStatusDateTimeStatusCode
1234512/15/2014 15:00CREATE
1234512/15/2014 16:30CONFIRMED
4567812/16/2014 08:00CREATE
9876412/18/2014 12:00CREATE
9876412/19/2014 08:00CONFIRMED
4567812/17/2014 09:30CONFIRMED
4567812/19/2014 15:30IN-TRANSIT
So my view should result in :
Order numberStatusDateTimeStatusCode
1234512/15/2014 16:30CONFIRMED
9876412/19/2014 08:00CONFIRMED
4567812/19/2014 15:30IN-TRANSIT
Is there a way i can grant a windows authenticated user say abcadam privilege:
-> to create, execute and alter the stored procedures that will be created by him only:
Point is we do not want the user to execute alter or modify other stored procedures out there for that DB
I will have a group of developers creating objects in a database. Currently they all
create them with the SA account. I would like to have them mapped to the dbo or some method
that when they create objects all objects are dbo.object name and not h1fed99.object name.
Is there a good methor or practice to handle this
My website host allows me to create DB and DB users from its control panel and then I am able to use SQL server Management studio to Manage My DB.
Therefore I create My DB (TBC20) and create a user for it(Tony) from Host Panel.
When I want to Export My Local copy DB data to the remote DB using Import/Export wizard and set the It won’t Generate any DB objects. Even When I use aspnet_regsql Command I encounter Following error:
SQL error number is 262 and the SqlException message is: CREATE DATABASE permiss
ion denied in database 'master'.
Creating the TBC20 database...
------------------------------------------------------
When I View permissions in Database properties window No Permission is Grant for user: Tony. Only some permission Grant for Grantor: dbo. When I check Boxes to Grant Permission for user Tony None of permissions will be grant to this user.
How could I allow this user ti take ownership of DB and create DB objects
Hi, can we use sqlcmd.exe (successsor to osql) to run sql scripts against a compact edition database? Would like to use the output from Visual Studio Team Edition for Software Professionals to build the database structure on compact edition database.
thanks,
dan
Hi All,
I have a SQL script which has creation of some table variables. When I run this query in Query Analyzer, I get proper results. But, when I use the same script to execute from VB Script, it is not running at all. I tried a simple select statement which works fine; but with the one I want to run, it fails to run. I am trying to print the SQL script from VB script, and it appears to be fine. I think the creation of DB objects is where it is getting stuck. Any help here please??
Below is the SQL Script.
Code Block
DECLARE @COMPSINGRP TABLE (NAME VARCHAR(100))
INSERT INTO @COMPSINGRP SELECT NAME FROM COMPUTER WHERE IDCOMPUTER IN (SELECT INSTANCEID FROM [FN_COMPUTERSINGROUPLIKENAME] ('UBS: APPLICATIONS - DEALAXIS') )
DECLARE @COMPRESTARTS TABLE (NAME VARCHAR(100))
INSERT INTO @COMPRESTARTS SELECT COMPUTER FROM (
SELECT C.Name AS COMPUTER, --- FIRST COLUMN
(SELECT AC.Description FROM Computer CC
INNER JOIN [fn_ComputersInGroupLikeName] ('UBS: Applications - DealAxis') CCG ON CC.idComputer = CCG.InstanceID
INNER JOIN Alert AC ON CCG.InstanceID = AC.idComputer
WHERE (AC.Description Like '%down on a scheduled maintenance%')
AND
CC.Name = C.Name
AND
(CONVERT(NVARCHAR(12),A.TimeOfLastEvent,111) = CONVERT(NVARCHAR(12),GETDATE(),111) )) AS DOWN_DESCRIPTION, --- SECOND COLUMN
(SELECT AC.Description FROM Computer CC
INNER JOIN [fn_ComputersInGroupLikeName] ('UBS: Applications - DealAxis') CCG ON CC.idComputer = CCG.InstanceID
INNER JOIN Alert AC ON CCG.InstanceID = AC.idComputer
WHERE (AC.Description Like '%successfully up now after a scheduled reboot%' )
AND
CC.Name = C.Name
AND
(CONVERT(NVARCHAR(12),A.TimeOfLastEvent,111) = CONVERT(NVARCHAR(12),GETDATE(),111) ) ) AS UP_DESCRIPTION --- THIRD COLUMN
FROM Computer C
INNER JOIN [fn_ComputersInGroupLikeName] ('UBS: Applications - DealAxis') CG ON C.idComputer = CG.InstanceID
INNER JOIN Alert A ON CG.InstanceID = A.idComputer
WHERE (CONVERT (NVARCHAR(12),A.TimeOfLastEvent,111) = CONVERT(NVARCHAR(12),GETDATE(),111) )
--ORDER BY C.NAME
) AS RESTARTS
DECLARE @NOCLUE TABLE (NAME VARCHAR(100))
SELECT 'The Server '+ NAME+' did not have any restart information. Please Check.' FROM @COMPSINGRP WHERE NAME NOT IN (SELECT NAME FROM @COMPRESTARTS)
Any help or suggestion would help me out. Thanks a lot.
Mannu.
How to SQL server save information about create/drop objects action. How can I get this? Example for, a lot of objects(sp) in my database has been dropped, how I know who was dropped them (user login & time)?
View 1 Replies View RelatedHello,
I just upgraded our existing 6.5 installation with the new SQL server 7.0. I can't get any of the existing stored procedures which I imported from 6.5 into 7.0 to allow me to edit them. I do all of my design from Visual InterDev and the SQL Server 6.5 version would allow me to create and edit stored procedures. It would also allow me to create/design new tables.
With SQL Server 7.0 I do not have an option to edit or create any of these items. I have created a new login, assigned it a password, given it admin rights/roles and I am still unable to remotely create these items. What am I doing wrong?
Thanks for any assistance
Doug
Hi
Is there any why to Create SQL Server Objects from Command Prompts like (Databases , Tables, Stored Procedures, …) ??
If you will Install some Applications Like this forums you will see the SQL Server object Created from Command Prompts
How Can I do that .. ??
And thanks with my regarding
Fraas
What would be the best way to create a routine that performs the "Script <object> as Create To File" for multiple objects in my database??
I would like a separate file for each object (table, view, or stored procedure).
Could someone point me in the right direction??
Thanks,
Paul
Hi
I use SQL code snippet to attach the CLR assembly (dll) to the SQL Server Express
CREATE ASSEMBLY [DatabaseAndImages]
AUTHORIZATION [dbo]
FROM 'C:CLR_File.dll'
WITH PERMISSION_SET = SAFE
That file content CRL Codes for 10 Stored Procedures & 3 UDT & 5 Functions & 6 Triggers
As you can see it's content large amount of DB objects !!
My Question is ..
Is there any simple way can I use it to extract or automatic create all that Objects in the Database without use separate SQL Statement for each one ??
By Example , I will use this SQL statement to create the sp_AddImage that already located inside the CLR dll file
CREATE PROCEDURE [dbo].[sp_AddImage]
@ImageID [uniqueidentifier],
@ImageFileName [nvarchar](max),
@Image [varbinary](max)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [DatabaseAndImages].[StoredProcedures].[sp_AddImage]
But as you know .. I have many objects .. and I am in development phase and I will do change to that object many time and also may I will add much more €¦
I thing it's not good to write SQL Statement for each object and do changes every time when I change the object definition
Is there any one line of SQL statement can I use it to automatically create and extract all the objects inside the assembly ??
Or is there any way to do that Issue by simple operation ??
And thanks with my best regarding
Fraas
(Please Rush)
Hi all,
I am using SQL SERVER 2000 now, and my SERVER is still have about 27 GB but in my database is saying only 3.4 MB left, I was trying to shrink my database but it is still did not give me more space.
Do you know any way could help me to extend more spaces for my database?
I also included the picture so could help you more
Thank you very much
vdang (vdang2003@sbcglobal.net)
When I create a view using Enterprise Manager, I can click on the 'Add Table' button to select a table. Is there any way I can tell EM that I want to include a table from another server?
Thanks,
Randy
I have noticed that VS2005 create partial class for SQLCLR objects(Stored Procedure, UDF , ecc.)
Is this partial need ?
Here http://technet.microsoft.com/en-us/library/ms345135.aspx is shown an example that do not use partial attribute.
Many thanks
Claudio
I am using SqlServer2005 and asp.net 2005. I have a large database for which I have to provide reports for it. I need a report that user will specify a date, and the report will be run, from this month & year and I will calculate the first day of the month and the last day of the month and these will serve as the input parameters for a long SELECT query.For my previous reports i've used views . But for this functionality i need a parameterized view, which i cant create.Please help me how to create a parameterized view.... Thanks in Advance...
View 3 Replies View RelatedI want to allow a group of users to create views but not be able to create new tables or stored procedures... how can I do this ??
Thanks, John :eek:
Hey
I am very new to database and have a question about views, that I hope someone can help me with, i am sure its simple:
I have to tables for storing different users, I want(for a log in function),to make a view that combine these to tables.
so all names stored in table1 under column customer_name, and all names stored in table2 under column name contact_name will in the view be stored under column username.
What shall a do?
Thanks for all help
I am stuck on creating views using the variables. I keep on getting the 'CREATE VIEW' must be the first statement in a query batch error. I understand that views need to be the first statement, but I have a lot of views that need to reference specific variables - is there any way to do this?
The code I am using is as follows:
DECLARE @view varchar(MAX)
DECLARE @database varchar(30)
SET @database = 'KateTEST3'
--Insert views
SET @view = 'USE ['+@database+']
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON;
CREATE view [dbo].[userssupplier]
as
SELECT *
FROM dbo.Users
WHERE (User_Type = 4.0) OR
(User_Type = 5.2)'
EXEC(@view)
Thanks
We are in the process of trying to automate our production releases (what a concept ;-)
The database is SQL server 2005
All objects are being stored in VSS
Using Nant and Cruise Control for the actual migrations.
I have two directories - Create (for a brandnew database) and Change (db object changes)
In my 'Change' script, I do the following -
1 - Take backup of database
2 - Migrate objects from 'change' directory to production
3 - Script out all objects of database and save in the 'Create' directory
For the #3, I was hoping I could create an SSIS package that would
script out all database objects and save them on the VSS server.
I'm new to SSIS and want to verify it's something that can be done before I start down that path.
If anyone has any examples or references, it would be much appreciated.
Thanks
I create a logon trigger on a sql server 2008 r2 instance,the trigger is very simple,like this:
CREATE TRIGGER tg_login
ON ALL SERVER
FOR LOGON
AS
IF ORIGINAL_LOGIN() IN('sa') AND HOST_NAME()='TestHost1'
[Code] ...
I use master.dbo.LoginRecord table to record the bad logon. When the trigger is working ,latches produced sometimes,wait type is PAGELATCH_UP and wait resource is 2:1:3. At this time, a large number of logon failed ,error message is "Logon failed for login 'login name' due to trigger execution."
I think logon trigger create internal temporary objects maybe,it is right?
I've created a local server from Sql Express, and report server from Reporting service configuration manager, and than publish a report on the local report server. I want to give access to the other computer user to the sql reports, database and report server.
View 0 Replies View Related