Grant CREATE VIEW, CREATE PROCEDURE ...
Apr 12, 2006
Hi,
I have currently a problem with setting up the permissions for some developers. My configuration looks like this.
DB A is the productive database.
DB B is a kind of "development" database.
Now we have a couple of users call them BOB, DAVID, ...
who are members of the db role db_reader and db_writer for the productive db a but they should be allowed to do nearly everything on db b.
Therefor I added them to the db role db_owner for db b.
For testing purposes I tried to "CREATE" a view TEST as BOB in database B but I received the error message
'Msg 262, Level 14, State 1, Procedure Test, Line 3
CREATE VIEW permission denied in database 'b'.'
I cross checked the permissions on db level and I even granted all available permissions on db level but nevertheless I receive this error message.
What's my mistake?
Of course it worked fine when I give them sysadmin rights but then they have far too much permissions.
Regards,
Stefan
View 8 Replies
ADVERTISEMENT
Aug 16, 2007
In SQL Server 2005 SP2 I want to grant the ability to create views to a user but in order to do this it requires that the users has the ability to grant alter on a schema.
Is there any way to grant this privilage without granting alter on schema also?
View 1 Replies
View Related
Feb 24, 2000
Hi!
How do I grant rights to an user for creating stored procedures (using T-SQL)?
Thanks,
Fabio
View 1 Replies
View Related
Jun 5, 2008
Is it possible to use sql server managment studio to grant the create procedure to a user on their own schema?
View 2 Replies
View Related
Apr 7, 2008
Can somebody tell me without pointing to any other link how to grant Create Procedure permission to DB user.
View 3 Replies
View Related
Aug 4, 2004
Hi All,
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
Create PROC dbo.GrantPermission
@user1 varchar(50)
as
Grant create table to @user1
go
Grant create view to @user1
go
Grant create Procedure to @user1
Go
Thanks Guys.
View 14 Replies
View Related
Jan 3, 2008
Is it possible to drop and then create a view from a stored procedure? Like the way you can drop and create a temp table.
I want to create a view of the fields in a table something like: But I cannot include the field names, they may be changed by an admin user.
If exists view 'custom_fields"
drop view 'custom_fields'
Create view custom_fields
Select * From tblCustomFields
And make this a view in the db named custom_fields.
And I want to call it from a button click in my UI.
View 9 Replies
View Related
Jun 24, 2008
Experts
I am trying to create a view or Stored Procedure between different table
Table1 consist of the follwing Fields:
Ref_No: String hold the reference number, Unique
Details: String
Table2:
MasterRefNum : String, not Unique
SubscriberRefNum : String, not Unique
What I am trying to do is that when the user enter a refernece number the system should return back
1- the details where Ref_No = the required refernece number
2- get all the SubscriberRefNum from Table2 where MasterRefNum = the required refernece number and from the Table1 get the details for those SubscriberRefNum numbers
Any advice?
View 4 Replies
View Related
May 21, 2007
hi,
Im am wandering if it is possible to create two views in two different tables from within the same stored proc:ex
create proc myProc
as
use [myDb1]
go
create view myV1
as
select * from mytable
go
use [myDb2]
go
create view myV2
as
select * from mytable
go
go
---
of course the go's are not allowed in a sproc, the create statement must be the first of a query batch and a vew can not have the databaase name preapended like when creating a table plus one can not use the "use" word in a proc, I tried using exec to bypass the "first statement in a batch" and go restrictions but have not been able to overcome the "use [myDb]" restriction, is there a way to solve this problem?
thank you
View 20 Replies
View Related
May 2, 2007
Is it possible to dynamically create an sql create view statement then execute that sql statement? Or because create views must be the first statement in a query batch, it's not possible?
View 6 Replies
View Related
Jul 20, 2005
I am trying to create a dynamic SQL statement to create a view.I have a stored procedure, which based on the parameters passed callsdifferent stored procedures. Each of this sub stored procedure createsa string of custom SQL statement and returns this string back to themain stored procedure.This SQL statements work fine on there own. The SQL returned from thesub stored procedure are returned fine. The datatype of the variablethat this sql is stored in Varchar(I have tried using nvarchar alsosame problem).If I have more that 6 SQL statements concated then the main SQL getscut off. It doesnt matter in what sequence I create the main SQL.Here is the Stored procedure/**********************************************//*Main Stored Procedure *//**********************************************/CREATE PROC sp_generate_invoice1 @prev_date NVarchar(1000) ,@prev_month NVarchar(32)ASDECLARE invoice_driver_cur CURSOR FORSelect driversid From Invoice_driversOpen invoice_driver_curDeclare@C VARCHAR(8000),@L_args Varchar(8000),@@sqlstmt Varchar(8000),@L_driverid Int,@L_rowcount IntSET QUOTED_IDENTIFIER ONSET TEXTSIZE 32768Set @L_rowcount = 0-- Drop the previous ViewIF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWSWHERE TABLE_NAME = 'custom_invoice')DROP VIEW custom_invoiceFetch Next From invoice_driver_curInto @L_driverid-- Create the new ViewSet @L_args = N'Create View custom_invoice As'--Select @L_driveridWHILE( @@FETCH_STATUS = 0)BEGINSet @L_rowcount = @L_rowcount + 1select @L_driveridIf @L_driverid = 2BeginExec sp_invoice_driver2 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @L_args + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 3BeginExec sp_invoice_driver3 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 4BeginExec sp_invoice_driver4 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 5BeginExec sp_invoice_driver5 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 6BeginExec sp_invoice_driver6 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 7BeginExec sp_invoice_driver7 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 8BeginExec sp_invoice_driver8 @prev_date, @prev_month, @@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndIf @L_driverid = 10BeginExec sp_invoice_driver_niku @prev_date, @prev_month, @L_driverid,@@sqlstmt OutputIf @L_rowcount > 1BeginSet @C = @C + ' Union ' + @@sqlstmtEndElseBeginSet @C = @L_args + @@sqlstmtEndEndPrint @CFetch Next From invoice_driver_curInto @L_driveridContinueEndClose invoice_driver_curDeAllocate invoice_driver_curExec (@C)--EXEC sp_executesql @CGO/**********************************************//*Sub Procedure sp_invoice_driver2 *//**********************************************/CREATE PROC sp_invoice_driver2 @args NVarchar(1000), @prev_monthNVarchar(100),@sqlstmt Varchar(8000) OutputASSET QUOTED_IDENTIFIER ONSET @sqlstmt = ' Select 1 SortOrder ,( SELECT Drivers.Description) Description,(BillingReport.Active_Accounts * Cast(Fee.fee_rate As decimal(4,2))) / 12 Amount,Drivers.CurrencyFROM BillingReport, Drivers, FeeWHERE ( Fee.Driverid = Drivers.Driversid ) andDrivers.Driversid = 2 andbillingreport.fromdate = ''' + Cast(@args As NVARCHAR(20)) + '''andfee.currentmonth = ''' + Cast(@prev_month As NVARCHAR(12)) +' '''GO/**********************************************/This is what the Print Statement give:/**********************************************/Create View custom_invoice As Select 1 SortOrder ,( SELECT Drivers.Description) Description,(BillingReport.Active_Accounts * Cast(Fee.fee_rate As decimal(4,2))) / 12 Amount,Drivers.CurrencyFROM BillingReport, Drivers, FeeWHERE ( Fee.Driverid = Drivers.Driversid ) andDrivers.Driversid = 2 andbillingreport.fromdate = '9/1/2004' andfee.currentmonth = 'September ' Union Select 2,(SELECT Drivers.Description),(BillingReport.Zero_Balance * Cast(Fee.fee_rate As decimal(9,2) ))/ 12 Amount,Drivers.CurrencyFROM BillingReport, Drivers, FeeWHERE ( Fee.Driverid = Drivers.Driversid ) andbillingreport.fromdate = '9/1/2004' andfee.currentmonth = 'September' andDrivers.Driversid = 3 Union Select 3,(Select Drivers.Description From Drivers Where DriversID = 4),Count(*) * Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 4 )As decimal(6,2)) / 12,(Select Drivers.CurrencyFrom Drivers Where DriversID = 4)From Fund Union Select 4,(Select Drivers.Description From Drivers Where DriversID = 5),(((Sum(BillingReport.Man_Reg_Purch + BillingReport.Man_Reg_Red +BillingReport.Man_Reg_Transexch +BillingReport.Man_Allo_Purch +BillingReport.Man_Allo_Red +BillingReport.Man_Allo_Transexch +BillingReport.Man_Allo_Adj_Purch +BillingReport.Man_Allo_Adj_Red +BillingReport.Man_Allo_Adj_Transexch +BillingReport.Man_Adj_Purch +BillingReport.Man_Adj_Red +BillingReport.Man_Adj_Transexch ) ) +(Select Sum(Cast(satuscnt As int ))From Awd_stubWhere CurrentMonth = 'September'))) *(Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 5 )As decimal(6,2))),(Select Drivers.Currency From Drivers Where DriversID = 5)FROM BillingReportWhere billingreport.fromdate = '9/1/2004' Union Select 5,(Select Drivers.Description From Drivers Where DriversID = 6),( Sum( BillingReport.Auto_Reg_Purch +BillingReport.Auto_Reg_Red +BillingReport.Auto_Reg_TRansexch +BillingReport.Auto_Allo_Purch+BillingReport.Auto_Allo_Red +BillingReport.Auto_Allo_Transexch+BillingReport.Auto_Allo_Adj_Purch+BillingReport.Auto_Allo_Adj_Red+BillingReport.Auto_Allo_Adj_transexch+BillingReport.Auto_Adj_Purch+BillingReport.Auto_Adj_Red+BillingReport.Auto_Adj_Transexch )+(Select Sum(Cast(Processed_msg As Int))From XML_messagingWhere CurrentMonth = 'September'))*(Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 6 )As decimal(6,2))),(Select Drivers.Currency From Drivers Where DriversID = 6)FROM BillingReportWhere billingreport.fromdate = '9/1/2004' Union Select 6,(Select Drivers.Description From Drivers Where DriversID = 7),( ( a.Accountholder_Active_Accounts -(select accountholder_active_accounts from billingreport whereMonth(fromdate) = Month('9/1/2004')-1 ) )+( a.Accountholder_Zero_Balance -(select accountholder_zero_balance from billingreport whereMonth(fromdate) = Month('9/1/2004')-1 ))) *(Cast((select fee.fee_ratefrom fee, driverswhere Fee.Driverid = Drivers.Driversid andfee.currentmonth = 'September' andDrivers.Driversid = 7 )As decimal(6,2))),(Select Drivers.Currency From Drivers Where DriversID = 7)FROM BillingReport a Where a.fromdate = '9/1/2004' Union Select 7,(Select Drivers.Description From Drivers Where DriversID = 8),( Select telephone From cfxbill Where currentmonth = 'September') *(Cast((select feThanks for any helpMD
View 4 Replies
View Related
Jun 8, 2006
Can anyone tell me how can I create a table in (SQL Server 2000) direct from a stored procedure execution or from a SELECT result?
I need something like this: CREATE TABLE < t > FROM <sp_name p1, p2, ...>or like this:
CREATE TABLE < t > FROM SELECT id, name FROM < w > ...
Thank you!
View 6 Replies
View Related
Mar 12, 2007
I have a stored procedure that takes a date range and returns all the sales in that date range. I'm trying to create the report model for ad-hoc reporting. When I go to create the dataset view, it only lets me select tables or views.... how do I get around this?
View 2 Replies
View Related
May 6, 2008
I am creating a login with a user in one database. The id has to
create procedures in the dbo schema, execute them, and grant execute on them to other users.
BOL says to
GRANT ALTER ON SCHEMA::DBO TO username ;
GRANT CREATE PROCEDURE TO username ;
I did that. User can now create procedures, but cannot execute what he creates. And cannot grant execute on the sp to anybody.
I don't want to give this id a lot of priviledges.
View 16 Replies
View Related
Jan 23, 2008
I am new to sql server. right now i getting used to management studio.I am trying to creata a new object / a new database but i am getting an error which says permission not granted
can any help me to move ahead
thanks
View 1 Replies
View Related
Jun 26, 2007
There's something I can't quite figure out about user creating
The application that I'm currently working on is interacting with DB, therefore every time you use application you need to login as user which is fine. The problem is that certain users should be able to create new users and the new user may even have the same level of permissions as the one that's creating it ( like admin creating another admin acount or some like that).
Question is how can I allow users to create these user with giving them as few permissions as possible.
If there's is somewhere a code sample on the net I would appreciate the link.
View 1 Replies
View Related
May 11, 2015
Need to create a user defined role with grant permissions for below .
View Definition
Execute all Function
Grant View
Grant Synonym
dbo
View Definition
Not getting grant statements for above permissions.
I mean like below.
-----------------------------------------------------------------
CREATE ROLE [Role1]
GRANT EXECUTE ON SCHEMA ::dbo TO [Role1]
-----------------------------------------------------------------
View 1 Replies
View Related
Dec 14, 2007
Hi,
We moved a development reporting services database to a new server last night and followed the MS 'Moving a Report Server Database to another computer' directions. We used the Reporting Services Config tool to make the changes, remoted into the 2003 windows server - which is running sql Enterprise reporting and connecting to a 2005 Enterprise cluster.
We had two small problems - although everything seems to be working fine. The First try seemed to be unsuccessful because we did have the service started after applying the database connection changes. The directions don't mention starting the service before you hit apply. The second time we got an error that said "Create Grant Rights Script - The Task Failed". We entered a sql login that was a sysAdmin during the setup.
The exception details started off with:
System.UnauthorizedAccessException: Access is denied
If the rest of this error is needed I can type it out...I was sent a screen shot of it!
Thanks for any help - we are moving production soon and I'd like to understand what went wrong.
Thanks
Sam
View 3 Replies
View Related
Apr 25, 2007
Hi,
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.
View 3 Replies
View Related
Jul 21, 2015
Have a certificate and symmetric key that i have used the following to GRANT to logins. How can I find out which SQL logins have the GRANT CONTROL and GRANT VIEW DEFINTION?
GRANT VIEW DEFINITION ON SYMMETRIC KEY:: Symetric1 TO Brenda
GRANT CONTROL ON CERTIFICATE:: Certificate1 to Brenda
View 5 Replies
View Related
Jul 24, 2012
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
View 2 Replies
View Related
Apr 19, 2002
Hi guys.
I am trying to create a procedure which should drop all existing triggers and can create about 40 differnt triggers in a table.
I cant use "GO" statement in a procedure.
Is there any way to create a procedure like that?
I dont want to run this as a script.
please advice.
--Note: Many triggers use same kind of variable names inside.
-MAK
View 1 Replies
View Related
Aug 14, 2000
I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
View 1 Replies
View Related
Jan 8, 2001
When I use following command it works fine.
CREATE VIEW dbo.[ikw_custom]
AS
SELECT * FROM rns_keyword.dbo.ikw_custom
but if I use following command with condition it gives me an error.
if not exists(select * from sysobjects where name ='ikw_custom' and type='V')
begin
CREATE VIEW dbo.[ikw_custom]
AS
SELECT * FROM rns_keyword.dbo.ikw_custom
end
help will be appriciated.
Thanks in advance
-Balbir
View 1 Replies
View Related
Apr 15, 2006
I am learning how to work with MSSQL
here is what I like to do
I have a table with URLs like this "www.urls.com" I need to create a view that will insert this "http://" before "www.urls.com" can some please let me know what I need to do
Thank you
View 14 Replies
View Related
Sep 25, 2005
Hello All;
I'm new to SQL, and developing my first application using SQL 2000 as the back end for my data.
Can anyone tell me if I can create an SQL View from VBA?
I'm trying to create a data view that access various records from a single table, and it has to distribute that data 2 14 different subforms, representing different days in a 2 week period, which is distingiushed by a field called "Day", that is numbered from 1 to 14.
I also have a summary subform that has the weekly summary (days 1 to 7 and 8 to 14) on seperate subforms. In total I have 16 subforms, which actually source from a single table, just split depending on the day in the period.
As I see it now, creating views iis the best way for me to go, but I also have to be able to change the period id on the fly, so I'm think I have to use VBA to generate these views.
Does any of this make sense, and am I on the right track??
View 2 Replies
View Related
Mar 18, 2006
hi,if the below query
select DISTINCT group_module_menu_info.menulink FROM
user_group_role_info,group_role_module_info,group_ module_menu_info
where user_group_role_info.userid=user_table.userid and
((group_module_menu_info.moduleid = user_group_role_info.moduleid)OR
(user_group_role_info.roleid=group_role_module_inf o.roleid and
group_module_menu_info.moduleid = group_role_module_info.moduleid))
if i want to create a view
how ?
thank you!
View 5 Replies
View Related
Jan 26, 2006
Create a view showing every order that was shipped to Spain. Name the destination column "DestinationSpain". Include code that checks if the view already exists. If it does, it should be dropped and re-created.
Use Northwind
Go
DROP VIEW Spain_Orders
Go
CREATE VIEW Spain_Orders
AS
SELECT *
FROM Orders
WHERE ShipCountry = 'Spain'
this is what I get.
server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the view 'Spain_Orders', because it does not exist in the system catalog.
albanie
View 20 Replies
View Related
Nov 29, 2007
I know IIf doesn't exist in SQL. However, I am creating a view and want to check the value of a field and if it is 1 the field returns 'This value' if it is 2 then 'That value' , if 3 then 'Another value'.
How can I do this ?
View 13 Replies
View Related
Jul 23, 2005
Hi Gurus,I'm a beginner. Would you please tell me if it's possible to create a viewhaving a calcuated column based on the condition of the column on the sqltable.create view vwImaging ASselectEmpID, LastName, FirstName, EmpTag = 'Act' ifFROM tblPerPayI have a table EMP:SSN (char 9)A view giving a formatted SSN (XXX-XX-XXXX,) a--Message posted via http://www.sqlmonster.com
View 3 Replies
View Related
Jul 20, 2005
Hi.I created a simple view with the following statements:CREATE VIEW dbo.VIEW1ASSELECT dbo.VIEW_ALL.ID,dbo.VIEW_ALL.Code,Another.dbo.OTHER_VIEW.Label as SpecialCodeFROM dbo.VIEW_ALL LEFT OUTER JOINAnother.dbo.OTHER_VIEW ON(dbo.VIEW_ALL.Code + '_0') = Another.dbo.OTHER_VIEW.LabelWhen I run :select * from VIEW1 where code = 'abcde',I would get all 10 rows that are of the same data where the code is'abcde'. But what I thought it should return is really just one rowsince the rest of the 9 rows are exactly the same! I know that I canget my result if I useselect DISTINCT * from VIEW1 where code = 'abcde'But, is there any way I could change my SQL statements(CREATE VIEW)above so that I automatically get 1 row instead of 10 rows of exactlythe same value without using DISTINCT?Thank you for any help.
View 7 Replies
View Related
Jan 8, 2008
I have a table in my program which going all the invoices,and I want to create a view where I can see the quantity of the products as many times by 1 as the quantity in the invoice:
Invoice:
Product name quantity
woods 3
at the view
woods 1
woods 1
woods 1
Thank you.
View 4 Replies
View Related
Aug 2, 2006
how to create view in sql server 2005 that recognize Identity And Primary key of base table
View 2 Replies
View Related
