How do I give a Windows group complete rights (including create) to all
stored procedures and user defined functions without giving them dbo access
in SQL Server 2005? If I have to I can do it from the Management Console,
but I would also like to know the commands.
Thanks
Matthew Wells
Join Bytes!
I'm working on Application that requires me to check and display status of reports running on report server. My application calling ListJobs() function of Job class part of Reporting Services Web Service API. When i run my application i'm getting insufficient previleges error. So i need to find out what type of permission i need to excute ListJobs().
This is very important part of my app. Please help me out.
Thanks,
Viral Patel
I have created an assembly with permission set safe, and a function inside the assembly.
The function reads data from the same SQL Server as it is running inside. For connection I use the "Context connection = true", and the function has the SystemDataAccessKind attribute set to Read.
However when I execute my CLR function I get an error saying something like:
"The request for permission of type System.Data.SqlClient.SqlClientPermission...... failed"
I do, as the login user, have been granted the necessary rights, so I don't believe this is the answer to the error.
And my .dll is also signed.
Has this something to do with writing something a config file?
I have had simular problems with reporting services but fixed them by entering a node in the rspolicy.config file. If this is the case here - which .config file should i modify...machine.config?
Hi,
Is there any query available to check the existence of 'CREATE TABLE' permission in a database
?
Please advice,
Thanks,
MiraJ
I have a requirement in SQL 2005 in Development database1. Schema dbo owns all objects (tables,views,SPs,UDFs etc) .2. Only DBA's ( who are database owners ) can create, alter tables .Developer's should not create or alter tables .3. Developers can create/alter Stored Procedure/User Defined functionsin dbo schema and can execute SP/UDF.4. Developers should have SELECT,INSERT,DELETE,UPDATE on tables (tables in dbo schemaHow to achieve this using GRANT SCHEMA statementThanksM A Srinivas
View 2 Replies View RelatedI gave a user explicit permission to create table, but still gettingthis error:Property Default Schema is not available for database[DBNAME]. Thisproperty may not exist for this object or may not be recoverable due toinsufficient access right. Microsoft.SQLServer.Express.SQLEditorsDo I need to create a new schema?--sharif
View 1 Replies View Related
question 1 :
I create a login account and a user account ,and then grant create table permission to the user.
But the user can't create table yet,it return a error message:
The specified schema name "dbo" either does not exist or you do not have permission to use it
But i can find the dbo scheam by querying sys.schemas.
the sql script i execute:
(sa login)
Code Snippet
use tempdb
go
create login l1 with password='a' ,check_policy=off
go
create user u1 for login l1
go
grant create table to u1
Code Snippet
(l1 login)
use tempdb
create table table1
(
col int
)
--fail
--error message:The specified schema name "dbo" either does not exist or you do not have permission to use it
question 2 :
The granularity of "grant permission to useraccount" command is database,while i want to get a table level of granularity.. how?
thanks
Hi,i run an asp.net application which uses sql server express.i defined a login 'aspnet' (IIS 5.0) and for the specific database, an user 'aspnet' with following roles:db_datareader and db_datawriter.Now, any user who uses that application must also be able to create programmatically tables in that database. My question is: which role do i have to give to user 'aspnet'?
I use Studio Management express.ThanksTartuffe
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
What is the best way to grant a user permission to create a view?I first created a role using enterprise manager but for the role Icreated it doesn't seem to offer that permission. It offers the basicstuff such as insert, select, and update.I could go in and use a grant create view sql statement I suppose butI'd rather do it through enterprise manager where it would be visibleif I need to change it in the future.-David
View 3 Replies View RelatedWhen I attempt to create the endpoint in "Programming SQL Server 2005", I get the error message: "
the user does not have permission to register endpoint. Any assistance in troubleshooting will be appreciated. Thanks.
Dan
i'm using SQLCacheDependency in my code. I have ran following lines to enable SQL notification.
ALTER DATABASE [DataBaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [DataBaseName] SET ENABLE_BROKER
ALTER DATABASE [DataBaseName] SET ARITHABORT ON
ALTER DATABASE [DataBaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATE
In Global.asax file, i have protected void Application_Start(object sender, EventArgs e)
{
SqlDependency.Start(CONNECTION_STRING);
} but whenever my application starts i get error saying "CREATE PROCEDURE permission denied in database", if i give dbo owner permission to SQL user then it works fine, but ofcource i dont want to give dbo owner permission to sql user defined in connection string
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.
Dear All,
This is my first post to this forum.
I would like to know if there is any way to restrict users from creating temp tables.
Problem: I am facing problems with lots of temporary objects getting created in my database. The users have read-only access to the database for adhoc-querying purpose through QA. Yet they are able to create temporary tables in tempdb database taking lot of resources on tempdb disk causing abnormally high growth of tempdb.
Thanks in advance.
Best Regards,
Chetan Jain
I have a new instance (SQL 2012) and I am trying to create a cube but it retrieves the error: Either the 'NameUser' user does not have permission to create a new object in 'NameServer' or the object does not exist. I am not the DBA but I got privilege as administrator in my instance. I even checked the propierties (right click on the analysis service instance) and in security there is my name as well..
View 4 Replies View RelatedWithout giving dbowner how can we grant permission to user to alter/create procs?
View 3 Replies View RelatedI have windows 2003 reporting services 2005 that has been working fine. Now all of a sudden when I go to http://localhost/reports I am unable to assign security to folders(the link is hidden) the New data source button is hidden, the new folder button is hidden, upload folder and report builder are also hidden. I am logged in as aministrator.
Also when I connect to reporting services through SQL Mgmt Studio I have the same issue, there are no options for permissions only a general option.
I'm sure it's a permissions issue but what do i do to resolve?
Thx
Can somebody tell me without pointing to any other link how to grant Create Procedure permission to DB user.
Hi,
I am learning SQL on my own computer and I am having difficult creating a new DataBase. I am using SQL Server Manabgement Studio Express. I installed it as an administrator; however, the software states that I do not have the proper permission. When I attempt to create a new DB, I get a message stating
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Create failed for Database 'test'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 262)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=262&LinkId=20476
how do I correct this issue?
I am using SQL express and Visual web developer on windows Vista.
When I try to create a new database the following message appears.
CREATE DATABASE permission denied in database master (error 262)
I log on to my computer as an administrator.
Help appreciated
Prontonet
I have a sql server 2012 server and I need to prevent the users from creating new schemas by mistake. Is there any way to revoke that permission alone but still letting the user to create their own objects in dbo (yes I know that shouldn't be in dbo but that is another issue).
View 2 Replies View RelatedHi all,
i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.
Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful
2. Creating the publication: executed as User2(db_owner)
2.1 publication.Create(); ... successful
2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with
agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by
agent.GenerateSnapshot();
And at this point,i got an error message, because the snapshot agent cant be executed ...
2007-09-12 12:05:46.58 User-specified agent parameter values:2007-09-12 12:05:46.58 --------------------------------------2007-09-12 12:05:46.60 -Publisher EDOM04SQLstandard2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest2007-09-12 12:05:46.60 -ReplicationType 22007-09-12 12:05:46.60 -Distributor EDOM04SQLstandard2007-09-12 12:05:46.60 -DistributorSecurityMode 12007-09-12 12:05:46.60 -PublisherSecurityMode 12007-09-12 12:05:46.60 --------------------------------------2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04SQLstandard'2007-09-12 12:05:46.96 The replication agent had encountered an exception.2007-09-12 12:05:46.96 Source: Replication2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command. Contact your system administrator.2007-09-12 12:05:46.96 Message Code: 142602007-09-12 12:05:46.96
Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role
This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!
Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo
Hi,
I am trying to create a inline function which is listed below.
USE [Northwind]
SET ANSI_NULLS ON
GO
CREATE FUNCTION newIdentity()
RETURNS TABLE
AS
RETURN
(SELECT ident_current('orders'))
GO
while executing this function in sql server 2005 my get this error
CREATE FUNCTION failed because a column name is not specified for column 1.
Pleae help me to fix this error
thanks
Purnima
I have an application that uses Integrated Windows authentication. My Web.config looks like below
<add key="dbconnection" value=" server=XXX;Initial Catalog=XXX;persist security info=False;Integrated Security=SSPI;Pooling=true" />
When users try to access my application, they get the below error:
Execute permission denied on object 'SprocName', database 'DBNAME',Owner,'dbo'
The Only way I could get rid off the error is if I set DBO permissions for the user group on the databse.
Can someone suggest how to set up a security group with the ‘necessary’ permissions on SQL SERVER (ie read,write execute Sproc etc) and not too many extra ones, like DBO.
Thanks,
got rid of my error about user login rights, it was all working yesterday. but for some reason i now get this error CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734995
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
i have this in my web.config file <connectionStrings> <add name="ConnectionStringTest" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf;Integrated Security=SSPI;Connect Timeout=30;User Instance=False" providerName="System.Data.SqlClient" /> </connectionStrings> <appSettings /> <system.web> <!-- Set compilation debug="true" to insert debugging symbols into the compiled page. Because this affects performance, set this value to true only during development. --> <compilation debug="true" /> <identity impersonate="true"/> and my asp connection string is <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringTest %>" SelectCommand="SELECT [entryID], [compID], [emailAddy], [answer] FROM [entry]"></asp:SqlDataSource> if i set user instance to true i get a user permission error. it says on sql server management that i have dbo rights on my database, but it wont let me put datareader or write on this login. any ideas? its driving me insane
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.
Hello!How can I make this function in MS SQL:CREATE FUNCTION id_name() RETURNS INTEGER AS 'SELECT MAX(ID)+1 FROM Test;'Thanks!
View 2 Replies View RelatedI have the following function that I was able to put together with the help of the following article http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx but I'm having some problems with it any help would be greatly appreciated.
USE database1
GO
CREATE FUNCTION dbo.Concatdwg_Seq (@prt_Mark CHAR(2))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''
SELECT @Output =CASE @Output
WHEN '' THEN dwg_Seq
ELSE @Output + ', ' + dwg_Seq
END
FROM dbo.Un_Combined
WHERE prt_Mark = @prt_Mark
ORDER BY dwg_Seq
RETURN @Output
END
GO
prt_Mark | dwg_Seq
12 | 12a,23b
25c | 1b,5e,8d,100as
I get the following errors
Msg 325, Level 15, State 1, Line 2
Incorrect syntax near 'FUNCTION'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@prt_Mark".
Msg 178, Level 15, State 1, Line 17
A RETURN statement with a return value cannot be used in this context.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'To'.
Hi,
I'm using Visual Studio 2005 Pro and SQLExpress that comes with it.
I have my program running fine in XP Pro OS using a window user "Glen" (Computer administrator) with Administrator rights. This means that I installed VS 2005 using this window user "Glen"
I created another windows user "TestUser" (Limited account) in the same physical PC.
I tried to run the program and on the part that I need to access SQL table, I got the error [CREATE DATABASE permission denied in database 'master']
At the same time while using "TestUser" and running sqlcmd (to check if I can connect to SQL), I also got error HResult 0x2, Level 16, State 1.
I read alot on MSDN discussions and related links but it seems that I can't get the solution that I need.
SO HERE ARE MY QUESTIONS :
1. Am I allowed to run my program using user "TestUser" since SQL is installed using "Glen" windows user?
2. Do I need to add access rights to "TestUser" to allow the user to have CREATE rights? (Note : for security reason, I can add other access rights except Administrator)
Thanks in advance for all you help.
I want to divide day of month as
example: month is July
week1: 02-07
week2: 09-14
week3: 16-21
week4: 23-28
week5: 30-31
--========Sunday is not including
example: month is August:
week1: 01-04
week2: 06-11
week3: 13-18
week4: 20-25
week5: 27-31
but if December:week1: 01
week2: 03-08
week3: 10-15
week4: 17-22
week5: 24-29week6:31 please help me!thanks so much!
how do create function in sql server 7.0
View 1 Replies View RelatedHi All,
I'm running SQL Server 2002 and trying to create a User Defined Function. However, everytime I try to save the script I get Error 170 Incorrect Syntax near 'FUNCTION'.
This happens if I create the Function from Code or use the Enterprise Manager. I'm logged in with 'sa' privs, so I don't think it's a privilege issue. I'm well confused.
Anyone help?
TIA
William.
Hi, Is there anyway to create a view within a Function? The code is as below. I execute the code between "BEGIN" and "END". SQL Analyzer report error that said
'CREATE VIEW' must be the first statement in a query batch.
I could make the variable constant in SELECT statement, but I'm wondering if there is a way to make CREATE VIEW as part of code piece.
CREATE Function GetCommonFailurs()
AS
BEGIN
IF OBJECT_ID(N'CommonFailures') IS NOT NULL
DROP VIEW CommonFailures
DECLARE @Run1Result as char(4), @Run2Result as char(4);
SET @Run1Result='Fail';
SET @Run2Result='Fail';
CREATE VIEW CommonFailures
AS
SELECT Run1Failures.RunID as Run1ID,
Run2Failures.RunID as Run2ID,
@Run1Result as 'Run1Result',
@Run2Result as 'Run2Result',
Run1Failures.SmartyDOTXMLFilePath as Run1SmartyFilePath,
Run2Failures.SmartyDOTXMLFilePath as Run2SmartyFilePath,
Run1Failures.SDET as SDET,
Run1Failures.CommandLine as CommandLine,
Run1Failures.OutputFilePath as OutputFilePath
FROM Run1Failures
INNER JOIN Run2Failures
ON Run1Failures.TestID = Run2Failures.TestID
END