SQL 2012 :: Permission To Create / Alter Proc But Not DBOwner
Feb 5, 2015Without giving dbowner how can we grant permission to user to alter/create procs?
View 3 Replies
ADVERTISEMENT
Create A Login And Make It Dbowner Of My Database
Nov 18, 2007Hi All
I have my program create and load (restore) a database, then create a login and make it dbowner by running a script like below via OSQL:
EXEC sp_addlinkedserver 'SETUP1myserver','SQL Server'
EXEC sp_serveroption 'SETUP1myserver','DATA ACCESS', 'TRUE'
GO
DROP DATABASE [mydb]
GO
EXEC sp_droplogin 'mylogin'
GO
CREATE DATABASE [mydb]
ON (NAME = N'mydb_data', FILENAME = N'C:AppfolderSQLmydb.mdf' , SIZE = 100, FILEGROWTH = 10%)
LOG ON (NAME = N'mydb_log', FILENAME = N'C:AppfolderSQLmydb.ldf' , FILEGROWTH = 10%)
COLLATE Latin1_General_CI_AS
GO
RESTORE DATABASE mydb FROM DISK = 'C:AppfolderSetupmydb.bak' WITH REPLACE,
MOVE 'mydb_data' TO 'C:AppfolderSQLmydb.mdf',
MOVE 'mydb_log' TO 'C:AppfolderSQLmydb.ldf'
GO
EXEC sp_addlogin 'mylogin','pass1$'
GO
USE mydb
EXEC sp_changedbowner 'mylogin'
GO
This works fine to a point. The program can then connect to the database and carry out normal processing with the "mylogin" user/password but it cannot do administration activities that a dbowner can do.
How do I create a login that does have true dbowner rights?
TIA
Sed Mayne
SQL 2012 :: Revoke Create Schema Permissions Without Removing DDL Permission?
Apr 17, 2015I 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 RelatedAlter Table Permission To Dbo
Jul 20, 2005I have the following requirementI am creating a login and database user 'test' on a database with dborole .I want to remove create table , alter table permisions to this user.I am able to revoke create table permission but alter table goesthrough.I gave a command deny insert,delete,update on ssycolumns to test.Still I am not able to prevent user altering schema . Alter tablesuccessfully goes throgh.I do not want to use datreader and datwriter role.since I want user 'test' to create storred procedure with dbo ownerIs there a way to achieve this ?ThanksM A Srinivas
View 1 Replies View RelatedALTER ASSEMBLY Needs Special Permission
Feb 20, 2008Hello again,
I get an error message every time I want to alter an assembly in the Management Studio. The message goes something like this:
"An error occurred in the Microsoft .NET Framework while trying to load assembly id 65610. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues."
I've already found this article on Microsoft support which mentions two possible workarounds. Could you possibly explain me, how do they work? I've tried both methods by 'registering' my windows login, with and without the domain, but none of them seemed to work. I've also set the TRUSTWORTHY property before using these workarounds.
So, do you have any ideas what could possibly went wrong? Thanks in advance for your time and help!
Deny Connect To Sql Permission Or Alter Login Disable
May 7, 2008Hi All,
I would like to disable a user account from logging to the database. I would like to know the difference between deny connect to sql permission and disabling an account by alter login disable. Please advice. Thanks
Stored Proc Question : Why If Exisits...Drop...Create Proc?
Jun 15, 2006Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies View RelatedUnable ALTER Stored Proc If SP Is Replicated
Apr 12, 2008HI,
My Stored Proc is Replicated to other DB Server, When iam trying to ALTER my modified SP in Publisher DB, ALTER statement not working, even it's not throwing any errors also.
My SP has very simple logic [Just for get data from DB] , no of lines 680, no of chars 32000 [Approx].
If i decrease the length of SP, it's working fine.
Can anybody help on this?
Thanks in advance..
Schema Permissions, Alter Proc But Not Table?
Jan 10, 2008I want our developers to be able to alter procs owned by the dbo schema, but for data modelling reasons, I want to exclude them from creating or altering any tables in the dbo schema. I can't seem to figure out how to do this, is there a way?
Thanks,
Permission Problem Calling The Sp_start_job Proc
Feb 12, 2008Code Snippet
ALTER procedure [dbo].[sp_MyProc]
AS
EXEC msdb.dbo.sp_start_job @job_name = 'MyJob'
I'm trying to write a procedure that calls a job. If I execute it (calling it from adp) as a user that is not db_owner (I guess), I get the error: The Execute permission was denied on the object sp_start_job, database 'msdb', schema 'dbo'.
How can I resolve this problem?
Problems On Create Proc Includes Granting Create Table Or View Perissinin SP
Aug 4, 2004Hi 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.
Granting Permission To A Database User To Alter Database Role
Sep 5, 2006I want a database user to be able to alter login, database user and database role from my application. so, i assigned that user to sccurityadmin server role, db_accessadmin and db_securityadmin database roles....By now, the user can add or remove login and database user. However, the user cannot add or remove any database role membership. What am I missing here?? What should I do so that the user can create, and alter database roles in the database??
View 1 Replies View RelatedCreate Or Alter Procedure
Nov 13, 2007I want to use Create a procedure if not exisit and Alter it if exisit.
Can I use Create and Alter keyword together while writing a procedure in SQL Server 2005? If YES than HOW?
Create Table Permission
Nov 4, 2005Hi,
Is there any query available to check the existence of 'CREATE TABLE' permission in a database
?
Please advice,
Thanks,
MiraJ
Create Procedure Permission ONLY
Sep 21, 2006I 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 RelatedCREATE Table Permission
Sep 20, 2007I 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 RelatedCreate Function Permission...
Sep 20, 2007How do I give a Windows group complete rights (including create) to allstored procedures and user defined functions without giving them dbo accessin SQL Server 2005? If I have to I can do it from the Management Console,but I would also like to know the commands.ThanksMatthew WellsJoin Bytes!
View 1 Replies View RelatedCan't Create Table .no Permission?
Sep 25, 2007
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
Using Alter Statements To Create Indexes?
Apr 19, 2006I am new to writing SQL code and I read that you can use ALTER statements to create an index for a table. How would I go about doing that? Everything that I have tried in Query Analyzer comes up with an error.
Any help is appreciated.
Thoughts On ALTER Vs. DROP & CREATE.
Nov 8, 2006Thought I should post in the newbie forum for a while, instead. :-)
I have a couple of scripts that I've generated that drop a couple of system stored procedures and recreate them. I'm not sure why I did it in the first place, but I think it was that it wouldn't let me run an ALTER statement on them. Specifically, I'm now looking at sp_add_operator. I changed it to a 500 character email field instead of whatever it was (100, I think.)
/* Explanation: Why did I do that? SQL Mail is prohibited here, so I'm using CDO_Sysmail to email myself and the developers if a job fails. The list of people to email is determined by the owner of the database, who is also an operator in SQL. I get the list of emails from the email field of the operator properties. Hence, I need a bigger email field. Yes, I now know it would most likely be better to create an ADMIN database on each server for this kind of stuff. (Thanks to Tara for that blogged suggestion.) */
While I will probably go back to the default stored procedure, this got me to thinking: when would it be better to use an ALTER statement on a SProc rather than to do a DROP and CREATE?
Your thoughts, oh SQL gurus?
CREATE, ALTER, Why Wont It Save?
May 11, 2007
when i create a new stored proc, i say "CREATE PROCEDURE [sprocName]"
but the next time i edit the stored proc, it hasnt changed to "ALTER ..."
I manually change it to ALTER, and run it, and close it, and it reverts back to "CREATE..."
Is there some sort of privelage i have to enable? Has anyone encountered this before?
Help guys, please :-S
Also, if you know of a better place where i can post this thread, please let me know
Create Table Permission Denied
Jul 8, 2007Hi,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
How To Grant Permission To Create A New Object
Jan 23, 2008I 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
Granting Permission To Create View
Jul 20, 2005What 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 RelatedCreate Endpoint Permission Fails
Feb 27, 2008When 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
Drop Create Vs Alter In Regards To Stored Procedures
May 17, 2007Anyone have any compelling arguements for using one over the other?
Thanks
Mercy
Alter Statement To Create Foreign Key Relationships
Jul 18, 2006Here is the alter statement that I am trying to use to create a relationship between 2 tables. This does not seem to work on mobile. What am I doing wrong?
ALTER TABLE [SubCategory] CONSTRAINT [FK_SubCategory_Category] FOREIGN KEY([CategoryID])
REFERENCES [Category] ([CategoryID])
ON UPDATE CASCADE
ON DELETE CASCADE
CREATE PROCEDURE Permission Denied In Database
Apr 7, 2008i'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
How To Grant User Permission To Create Other Users?
Jun 26, 2007There'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.
Deny Permission To Create Temporary Tables
Apr 7, 2007Dear 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
DB Engine :: User Does Not Have Permission To Create A New Object
Aug 28, 2015I 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 RelatedChange Dbowner
Sep 14, 2000Does anyone know how to change the database owner to sa (dbo)?
View 2 Replies View RelatedChange DbOwner
Apr 11, 2006How do I loop through a database and display all the table name in it
then use
SP_ChangeObjectOwner "TABLE_NAME","dbo"
where table_owner is OBI
In a nut shell, how do I loop through a database and isplay name ?