Howto Setup My User In Order To Grant Him The Drop Privilege
Sep 28, 2005
Hi. Thru a sproc, I drop & re-create some temp tables.
When I call that sproc from the client, though, I cannot drop the
tables.
I need to allow the user, say "Alex", to drop/create tables (actually,
that would be DDL). Which role should "Alex" assume ? How do I do that
?
I run the following sproc named, say, "CREATE_TABLE" (SNIP):
__________________________________________________ __________________
Set @StrSQL = 'if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[' + @TableName + ']'') and OBJECTPROPERTY(id,
N''IsUserTable'') = 1) drop table [dbo].[' + @TableName + ']'
Exec (@StrSQL)
Set @StrSQL = 'CREATE TABLE [dbo].[' + @TableName + '] (
[GROUP] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
...........
[Stuff] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]'
Exec (@StrSQL)
Set @StrSQL = 'GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].['
+ @TableName + '] TO [Alex]'
Exec (@StrSQL)
__________________________________________________ __________________
As you can see, it is dynamic, because I need to repeat it for many
@TableName values - that means, further more, that I will be executing
this in the context of the current user, Alex, and therefore I have to
give Alex rights to both executing the sproc and to the tables referred
to by the sproc, as specified by @TableName.
I created a _TEST sproc which contains only the following:
_______________________________________________
CREATE PROCEDURE _TEST AS
DROP TABLE [dbo].[SomeTable]
RETURN
_______________________________________________
When I execute it from the client, thru ADODB, on user Alex, I get
"User does not have permission to execute this operation on table
SomeTable"
The table has been created thru "CREATE_TABLE", above
Please help, I have to finish this tomorrow, and I'm under tons of
pressure.
Thanks a lot,
Alex.
View 1 Replies
ADVERTISEMENT
Aug 24, 2006
it is possible to grant all privilege (CRUD) to specified table to user. But, now, i want to grant all privilege (CRUD) of all tables, views, sp, ... of database to the user. is it possible?regards,
View 5 Replies
View Related
Feb 18, 2014
I am relatively new to sql developer. There is a new user that just joined our organization. I am trying to grant him the same direct grants privilege to the tables that an existing user has. The existing user has a ton of direct table access privileges and it will take days if I had to do each grant one by one like: grant select,insert,delete,update on 'table name' to 'user id'. Is there a way of copying or inserting an existing user's privilege and granting it to a new user.
View 2 Replies
View Related
Oct 31, 2014
I am writing a stored procedure which updates a table, but when I run the stored procedure using a login that I have granted execute privileges on, then I get a message that I cannot run an update on the table. This would happen in dynamic sql... while my SQL has parameter references, I don't think it is considered dynamic SQL?
sproc:
CREATE PROCEDURE [schemaname].[SetUserCulture]
@UserID int
, @Culture nvarchar(10)
AS
UPDATE dbo.SecUser
SET Culture = @Culture
WHERE UserID = @UserID
execute SQL:
EXEC schemaname.SetUserCulture @UserID = 9, @Culture = N'x'
error:
The UPDATE permission was denied on the object 'SecUser', database 'DatabaseName', schema 'schemaname'.
View 8 Replies
View Related
Jul 19, 2006
hi, i have one temporary table
#tmp_tbl
before i create a temporary table , i would like to drop it first, but i try
"drop table if exists #tmp_tbl "
it doesn't work , any help ? thanks in advance
View 3 Replies
View Related
Feb 16, 1999
Using SQL 6.5 SP4.
TIA.
Zak
View 2 Replies
View Related
Jul 23, 2005
hi, can anyone help me out with this report.I need to produce a report for the database level user permission.like for a database: DB_A, I want to output as such:[SELECT] [INSERT] [UPDATE] [DELETE] [CREATE TABLE] .... otherpublic G Dguest D D D D Ddbo G G G G Guser_A G D D D GG stands for Grant and D stands for Deny.I need not only to look at direct grant/deny but also whether that useris a member of fixed database role and fixed server role. like dbo willhave G on all.I am trying to look into sysprotects table and looking at lines with id= 0 but I just can't get to know what different action and protecttypenumbers means!Many thanks
View 1 Replies
View Related
May 26, 2008
Hi friends,
I have created a database DB1 using CREATE DATABASE DB1 command. Then i created login name using CREATE LOGIN login1 WITH PASSWORD = 'password1' command and created user name using CREATE USER user1 FOR LOGIN login1 command. Now i have to assign the user1 to the database DB1.
Any one please tell me how to assign DB1 access privilege to user1?
Thanks in Advance
Sathish kumar D
View 1 Replies
View Related
Mar 30, 2006
Hi,
My server administrator has created a few users as domain user privilege for connecting SQL server from my application. However it faild to connect, and
with Domain Admin privilege, my app is running ok.
I asked admin person to upgrade domain user privilege to connect SQL server,
but he couldn't do this for security reason, and told it should work with domain user.
Is it true for domain user to connect SQL server without any problem?
Do I need to add the user, domain user, into Logins of Security inside SQL server Enterprise Manager?
Or inside users of my database section of SQL server EManager?
Thanks,
dixon
View 1 Replies
View Related
Mar 26, 2015
How to grant DROP table permission within a database to an SQL login. I could see in Databae properties-> permission tab, there is an option to grant CREATE TABLE, EXECUTE permission etc, but not DROP table permission. How to grant it?
View 3 Replies
View Related
Jul 22, 2014
I have a requirement to allow a user to restore a database and then create database users and add them to the db_owner database role. The user must not have sysadmin rights on the server.
The database restore works ok by placing the user in the dbcreator role.
There is a stored procedure to create the database user and alter role membership, I want the user to execute the sp as a different, higher privilege account so as not to give the user underlying permission to create users in the database.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_create_db_users] Script Date: 22/07/2014 13:54:46 ******/
SET ANSI_NULLS ON
GO
[Code] ....
The user has execute permission on the stored procedure but keeps getting the error:
Msg 916, Level 14, State 1, Line 2
The server principal "Mydomainadmin1" is not able to access the database "Mydatabase" under the current security context.
Mydomainadmin1 has dbowner to Mydatabase and sysadmin rights for server. If the 'execute as' is changed to 'caller' and run by mydomainadmin1 it works so the issue is between the execute sp and the actual running of the procedure.
View 1 Replies
View Related
May 26, 2015
An old website I inherited uses sa to connect to SQL SessionState and had the details in the web.config. This is bad for security.The session state database is of -sstype "t" which is defined as:Temporary. Session state data is stored in the SQL Server tempdb database. Stored procedures for managing session state are installed in the SQL Server ASPState database. Data is not persisted if you restart SQL. This is the default.What kind of WIndows user, SQL Login, role and permissions do I need to create to make Session State secure? (Windows Server 2012 and SQL Server 2012 mixed mode authentication, Webfarm).
View 4 Replies
View Related
Oct 9, 2006
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies
View Related
Jul 6, 2005
The objective is to configure on a dev machine. Selecting New Database User is the easy part as the ***ASPNET user can be selected from the drop down listbox. What follows and what to do next is a myriad of choices. What needs to be done next? Is there a step-by-step document somewhere that you can refer?
View 1 Replies
View Related
Apr 9, 2007
so here i go explaining my problem: I create an empty database. then I use the .net aspregsql tool to make the tables and the procs and everything, and it does. when i view my database it has the 11 empty tables the sprocs and the roles and everything. BUT when I open up the "asp website configuration" to add some users, it gives the following error:EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'masfeni_fakebetsdb', owner 'dbo'.
View 6 Replies
View Related
Aug 24, 2006
Is it possible to grant all privilege for all tables of a specified database through script? Because i have to send the script to user side and i can't do it manually in Enterprise Manager.regards,
View 1 Replies
View Related
Apr 25, 2008
Hi,
I newly created one database (using creat database testdb ). After that i created login name and password for that database ( using create login login1 with password = 'pass1'; use testdb; command) and i created user for that login name ( using create user user1 for login login1 command).
Then i connected testdb database using login1. But when i trying to create table in that database, it thrown error. Anyone please tell me that how to assign all privileges to the user user1?
Sathish kumar D
View 3 Replies
View Related
May 22, 2006
I was trying to review some query statistics and received the following message:
SHOWPLAN permission denied in database Test
I gave the user permission by the following command:
Grant showplan to user.
I am curious as to how much perfomance does this effect? Is there an alternative?
regards
View 6 Replies
View Related
Jan 19, 2001
Hello together,
can anybody help me. I'm looking for an easy way to grant permissions to a user in all user databases. I already have a script which grants permission to all views and userdefined tables within one database, but since I have to run it in about 100 databases it's still quite timeconsuming.
Is there a way to execute that script in all user databases at once ???
Markus
View 2 Replies
View Related
Nov 2, 2007
I need an example of how to grant access to a SQL user to a DB. For the life of me I can't seem to get my syntax correct. My database name is TEST and my username is LEMME_IN and I want to grant the user "Public" access to the db with db_datareader, db_datawriter database role membership.
Thanks
View 1 Replies
View Related
May 29, 2007
what is the command to grant sysadmin to the user?
thanks
View 1 Replies
View Related
Feb 10, 2008
I am build an application that able to update insert delete an entries from my sql server, i create a new account in sql server management studio express so my app can connect to the database thru SQL Server Aurthentication. Delete & update method work well but my insert dont.
I change to the connection string to localhost ( WindowsAurthentication) and it works so i conclude that the problem lies on the sql user access. However I had already ticks everything on object explorer > security > logins > my user properties > Server Roles & User Mapping. What should i do inorder to grant insert/create database access to my sql user? Thanks.
View 1 Replies
View Related
Jan 3, 2008
How do I grant a user access to a particular project? I have set up some reports and want other users to look at them as a template to build their reports and keep getting projectname.rptproj.user is denied.
Thanks, Iris
View 5 Replies
View Related
Mar 22, 2007
We have a custom security working with forms authentication. When we browse to http://servername/reports the UILogon.aspx page comes up, and login with the Admin account is fine we can reach the report manager and see the folders. But when we login with account User1 , which is not an admin account.Login is fine but User1 does not see any folders in report manager. So how do i assign roles to User account when using custom security extension.
chi
View 1 Replies
View Related
Feb 19, 2008
I have a larger stored procedure that is running, but I am getting stuck on where I need to grant permissions to a user in a a different database on various functions and stored procedures. For example:
Code Snippet
use [Database1]
grant exec on [Database2].[dbo].[MyFunction] to bob
returns this error: Cannot find the user 'bob' , because it does not exist or you do not have permission.
However, I know 'bob' exists, plus when I change the use statement to Database2, the line of SQL works correctly. Given the nature of the overall stored procedure this will be running in, I won't have the ability to just change the use statement. Is it possible to grant permissions to a user on a different database without explicity having the use statement set to a particular database?
Thanks for any advice!
-Flea#
View 1 Replies
View Related
Jan 21, 2008
Hi to everybody,
I have a permission problems with user create for service.
I created an user without login in a database. I use this user for service broker activation procedure.So, this user have a certificate to exports in other database.
To generalize activation procedure of many service broker queue I try to use this piece of code :
DECLARE @QueueName nvarchar(MAX)
DECLARE @queue_id int
SELECT @queue_id = queue_id FROM sys.dm_broker_activated_tasks
WHERE spid = @@SPID
SELECT @QueueName = [name] FROM sys.service_queues
WHERE object_id = @queue_id
To use this DMV user need VIEW SERVER STATE grant but this is an user DB and I can't give this grant to user.
How I can do it?
Somebody can help me?
Thanks in advance
Luca
View 3 Replies
View Related
Jul 26, 2001
Hi,
I'm trying to run the Bulk Insert statement but in order for me the run it, i need to have the sysadmin permission. Can someone show me how to grant sysadmin permission to my SQL Server user? This is really urgent. Thank you in advance.
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
Feb 7, 2003
Hello Everyone,
I have an web application where the users has to run SQL scheduled jobs from the webpage. How to assign permission to a specific user to run specific jobs without making them a member of a Sysadmin role?
Any ideas you all smart people?
Thanks in advance!
Jannat.
View 3 Replies
View Related
May 5, 2015
i want to create new sql user and grant him two tables access. we have several databases created on same server so we want to allow only two table in ABC database. user should not be able to see other databases and their tables. And user also should not be able to access any other tables in ABC database except two tables.
is there any query to deny all tables in schema for all clauses (Select, Update, Insert) then grant two tables to user with select clause?
View 17 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 27, 2015
What's best practice (security wise) in granting a user access right to edit say the description of a job.
View 2 Replies
View Related
Sep 24, 2013
I'm trying to grant a user group select access to all the views in a database. I already made a query which creates a result set whit the SQL Syntax I (displayed below) need but it seems to be impossible to get this result set executed after creation.
use [AdventureWorksDW2008R2]
SELECT
'GRANT SELECT ON [' + SCHEMA_NAME(Schema_id) + '].[' + name + '] TO [DOMAINGROUP]'
FROM sys.views;
View 5 Replies
View Related