Hello
Is it possible to grant permissions like SELECT,INSERT,DELETE permissions to a database in SQL Server 2005 as we give it through SQL Server Management Studio.
Is it possible to grant permissions without specifying the username,passwd
Thanks In Advance
Poornima
Hi all,
Can I grant select only permission on all objects in the database? I have users that I need to give view access only on stored procedures, triggers, and functions. Thanks.
I am trying to write a SQL script that grants execute permissions on all stored procedures for a single user (test_user).
I am running SQL server 2005
Does anyone have a script to do this.
Many thanks
It seems that SQL7 has lost the "Grant All" option previously available in SQL 6.5. Is anybody aware of an equiviant in SQL7?
Any help would be gratefully received.
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
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#
Hi There
The backup database permission seems to only be at a database level , grant backup database and db_backupoperator role only exist at a database level.
However i want to permission to a user to backup any database, and he must not be a sysadmin. I cannot find a server level role that can backup databases.
How can i do this? There are alot of database so granting the permission for every database seems a waste of time, also one would have to remember every time a new database is created to grant this permission.
There must be a way to do this at a server level?
Thanx
I have a stored procedure in which at the bottom of the code, im granting execute permissions to a role I have defined. However, when I view the permissions on the procedure, the role isnt there, what could I be missing ? The procedures were all created under the default or dbo schema. I could manually give the permissions to the role, but id rather have it scripted.
help ?
is there an easy way I can give a specific user complete insert,update, and delete permissions on all tables,view, and sp in a db without having to set individually for all?
View 5 Replies View RelatedA question on the permissions hierarchy:
Since logins, database users, and database roles are both principals and securables - what does it mean to GRANT permission on a login/user/role to another principal? Does it mean that for a login - you can GRANT permission to EXECUTE AS that login or modify it, for example?
How do I grant a user permissions to only one table in a database. How would it affect him using our Main App which is NAV with regards to his user's permission in NAV
View 2 Replies View RelatedHi,
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.
set up asp .net user account on sql server 2005Question:
I've read the instructions in this article: http://www.netomatix.com/Development/aspnetuserpermissions.aspxBut do not know how to do this:You can grant 'Network Service' or 'ASPNET' user accounts permissions to connect to database.Please provide example on how to do this, thanks!
GRANT SELECT ON [dbo].[TblAreaCatmap] TO [admin] prevent grant from being automaticly add to each column?
Is there a way when you issue a grant select to a table or a view to not also grant select for each column.
The problem is when you use the grant command it automaticly adds the grant command to each column. I want to grant the permission at the table level so when the table is scripted it only has a single grant command instead of a grant for the table and a grant for each column which is not needed.
The sql managemnt studion interface will allow you to do this but onlt by using the interface. If you issue the above command from a query window it also creates A GRANT FOR EVERY COLUMN. How can I stop this behavior.
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'.
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?
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
Dim objConn As New SqlConnection Dim objCmd As New SqlCommand Dim Value As String = EventCmb.SelectedItem.ToString() objConn.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename='C:Documents and SettingsHPMy DocumentsVisual Studio 2005WebSitesFYP2App_DataEvent.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True" Try objConn.Open() objCmd.Connection = objConn objCmd.CommandType = CommandType.Text objCmd.CommandText = "SELECT EventTel FROM Event WHERE (EventID = @Value)" ' See how i changed Value to @Value. This is called a Named Parameter objCmd.Parameters.AddWithValue("@Value", Value) ' Add the @value withthe actual value that should be put. This makes it securer Dim RetVal As Object = objCmd.ExecuteScalar() ' This returns the First Column of the first row regardless of how much data is returned. If Not ((RetVal Is Nothing) Or (RetVal Is DBNull.Value)) Then ContactLbl.Text = RetVal.ToString() Else ' noting was returned End If Catch ex As Exception Throw ex Finally objConn.Close() End Try
There's an error for in line "Dim RetVal As Object = objCmd.ExecuteScalar() "
Error Message is as follow "Conversion failed when converting the nvarchar value 'LTA' to data type int."
It is due to "ExecuteScalar() " can only store int? I just need to display one data, and Value data comes form a combo box "EventCmb", which i wanted to find the selected String, compared to the "Event" database to get the "EventTel" data How do i solved this problem, any advice? Thanks!
I'm attempting to copy data from an Epicore server with multiple company
databases to another server. All this data will be placed in a single
table on the receiving server with the addition of an database identifier,
so that the receiving department will know, to which company the data
belongs. I have code that will dynamically create the SQL and then
execute it using EXEC (@SQL), and this works just fine acrosss multiple
databases. I've read in my Transact-SQL Programming book that using
EXEC() is not limited to 255 characters, but it still appears that my
dynamically created SQL is being shortened. The insert statement and the
select statement list each column, and here is that code:
SELECT @CMD = "INSERT ##APMaster(Company, timestamp, vendor_code,
pay_to_code, address_name,
short_name, addr1, addr2, addr3, addr4, addr5, addr6, addr_sort1,
addr_sort2, addr_sort3, address_type,
status_type, attention_name, attention_phone, contact_name, contact_phone,
tlx_twx, phone_1, phone_2,
tax_code, terms_code, fob_code, posting_code, location_code,
orig_zone_code, customer_code,
affiliated_vend_code, alt_vendor_code, comment_code, vend_class_code,
branch_code, pay_to_hist_flag,
item_hist_flag, credit_limit_flag, credit_limit, aging_limit_flag,
aging_limit, restock_chg_flag,
restock_chg, prc_flag, vend_acct, tax_id_num, flag_1099, exp_acct_code,
amt_max_check, lead_time,
doc_ctrl_num, one_check_flag, dup_voucher_flag, dup_amt_flag, code_1099,
user_trx_type_code, payment_code,
limit_by_home, rate_type_home, rate_type_oper, nat_cur_code,
one_cur_vendor, cash_acct_code, city, state,
postal_code, country, freight_code, url, note) SELECT '" + @DBName + "' AS
Company, timestamp, vendor_code,
pay_to_code, address_name, short_name addr1, addr2, addr3, addr4, addr5,
addr6,
addr_sort1, addr_sort2, addr_sort3, address_type, status_type,
attention_name, attention_phone, contact_name,
contact_phone, tlx_twx, phone_1, phone_2, tax_code, terms_code, fob_code,
posting_code, location_code,
orig_zone_code, customer_code, affiliated_vend_code, alt_vendor_code,
comment_code, vend_class_code,
branch_code, pay_to_hist_flag, item_hist_flag, credit_limit_flag,
credit_limit, aging_limit_flag,
aging_limit, restock_chg_flag, restock_chg, prc_flag, vend_acct,
tax_id_num, flag_1099, exp_acct_code,
amt_max_check, lead_time, doc_ctrl_num, one_check_flag, dup_voucher_flag,
dup_amt_flag, code_1099,
user_trx_type_code, payment_code, limit_by_home, rate_type_home,
rate_type_oper, nat_cur_code, one_cur_vendor,
cash_acct_code, city, state, postal_code, country, freight_code, url, note
FROM " + @DBName + "..APMaster"
Can anyone provide any insight to my problem?
TIA,
Chris
Hello,
I have the following code and it seems that it is not comparing the request_close_date. What I want to do is compare the request_close_date between two tables and if it is less than the date from the TEAM3B_PULL_TOTAL_TST then insert the data into RDD_UPDATE_TST. Any help will be appreciated. It also needs to insert data into RDD_UPDATE_TST if a request exists in REQUEST_BUS_REQ and BUS_REQ_DESCRIPTION. I think that I have that coded correctly but I am not certain.
Thanks in advance,
Anne
begin
Insert into RDD_UPDATE_TST(request,business_req_id,test_case_i d,test_case_descr,request_close_date)
select distinct request,business_req_id,test_case_id,test_case as test_case_descr,request_close_date
from TEAM3B_PULL_TOTAL_TST A
where not exists (select * from BUS_REQ_DESCRIPTION_TST B where A.REQUEST = B.REQUEST)
and not exists (select * from REQUEST_BUS_REQ_TST C where A.REQUEST = C.REQUEST)
and not exists (select * from RDD_UPDATE_TST D where A.REQUEST = D.REQUEST)
and exists (select * from RDD_UPDATE_TST where REQUEST_CLOSE_DATE <>B.REQUEST_CLOSE_DATE)
end
Hi im an newbee so im not the best but try to get there some day..
CREATE TABLE table_members
(id int(11) NOT NULL auto_increment,
fornamn char(60) default NULL,
efternamn char(60) default NULL,
address char(60) default NULL,
personnr INT(11) default 0,
smeknamn char(60) default NULL,
epost char(60) default NULL, i must be a @ value in it! any ides?
kon ENUM('M','K','V') default 'M', is this right, must only be enable to type in this Alphabets sings MKV!
losenord char(60) default NULL, verlosenord char(60) default NULL,
How do i get a or fix a retype field for this losenord and verlosenord thay must be the same value to be insered in to the sql!
PRIMARY KEY (id)
UNIQUE ('smeknamn');
Any ides are good
Help me...
I need C# Language ASP.Net coding...
i've two column... One for msg another for msgLastposted
in label control view msg(last visitor)....... that msg stored in my db.
Example Table Data
---------------------------------------------
MSg|MsgLastPosed
How edit forum |2007-03-12 10:50:25.747
How to Value Change|2007-03-12 10:56:36.373
Sql Command|2007-03-12 11:00:25.047
User Control|2007-03-12 11:02:10.793
How I can uninstall|2007-03-12 13:07:51.233
-----------------------------------------------
In table have many record..
label control display msg based on last visitor time.. after that again msg
changed( based on next last visitor time).. Continue for upto first 5 record msg automatically changed..
Hi all,
I want codeing for to find (subtotal & total)the following
Input
productname qty_A_grade Qty_B_grade
abc 10 5
def 5 5
subtotal
gh 10 10
ab 10 10
Total
output
productname qty_A_grade Qty_B_grade
abc 10 5
def 5 5
subtotal 15 10
gh 10 10
ab 10 10
Total 20 20
:This is a segement of VBscript for checking lastest Backup in SQLServer 2000:
'temporary variables to useDim sTemp1Dim i, sTemp2
'read start arguments' 1 => servername' 2 => path for logfile' . => excluded databasesDim objArgsSet objArgs = WScript.ArgumentsIf objArgs.Count < 2 Then 'Not enough parameters WScript.Quit 2 end If
If the above VBScript is converted to VB.NET, how can the lines containing a boldfaced WScript be coded? What namespace in .NET should be used for that? Thanks?
hi im a little bit confused. are the two pieces of code similar? what are the differences. i really need to know that coz i wont get access to a SQL machine until monday.
selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
group by sex
havingsex='M')
selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
wheresex='M')
also is it wise to use Group by and having in sub-queries?
Hi;Is there an **easy** way to tell tsql apart from standard sql?Will sqlserver run scripts written only in standard sql?What about variable definitions?Thanks in advanceSteve
View 8 Replies View RelatedI am attempting to build a query that will allow me to fetch data from a table for only the last seven days. This will be used for a daily report that will need to update the day to current date every time it is run. Any help would be greatly appreciated.
Thanks
Andrew
Hello,
I'm coding a stored procedure. I am creating a search function where I search multiple resultsets of data. I'm getting stuck on one part of the query, and I don't know what the best option is. Basically the query looks like this:
select * from Table where ...
and Official in (... problem area ...)
What I want is if the parameter passed in is null, return all of the valid values from the table that references this field (for example, if null is passed in, I want to pass in 'T', 'F', etc.). If the value is there, I want to pass in the value. I've been stuck on this and can't figure it out.
Any ideas? I don't want to use a dynamic query. Thanks,
Brian
Hey it's not often the blindman asks for advice on sql coding (never, I think), so here is an opportunity to solve a problem I've been knocking my head against for two days.
Here is sample code for setting up the problem:create table #blindman
(pkey smallint primary key,
fkey char(1),
updateddatetime)
insert into #blindman (pkey, fkey, updated)
select1, 'A', '1/1/2006'
UNION
select2, 'B', '1/1/2006'
UNION
select3, 'A', '1/2/2006'
UNION
select4, 'B', '1/2/2006'
UNION
select5, 'A', '1/4/2006'
UNION
select6, 'B', '1/2/2006'
UNION
select7, 'A', '1/3/2006'
UNION
select8, 'B', '1/3/2006'
UNION
select9, 'A', '1/5/2006'
UNION
select10, 'B', '1/5/2006'
drop table #blindman
Notice that for fkey 'B', there are two entries with '1/2/2006', and for fkey 'A' the updated values are not in synch with the order of the primary key.
The challenge: determine the next pkey for each pkey value, ordered by [updated], and using pkey as a tie-breaker when two records have the same [updated] value.
Here is the desired output for the sample data:pkey fkey updated nextpkey
------ ---- ---------- --------
1 A 2006-01-01 3
3 A 2006-01-02 7
7 A 2006-01-03 5
5 A 2006-01-04 9
2 B 2006-01-01 4
4 B 2006-01-02 6
6 B 2006-01-02 8
8 B 2006-01-03 10
Records 9 and 10 are missing because they have not succeeding records, though I'd be just has satisfied to include them with NULL as their nextpkey value.
Ideally, I want this as a VIEW.
Who's up for the challenge?
ok this is for a class assignment so if anyone doesnt want to help thats cool, but its just a small extra credit assignment and we havent gone over it in class and and the book, well is confusing to say the least so here are my questions:
What is the total weight of different colored parts?
ok so i have a parts table, with pcolor and pweight in it but i am unsure how to code that, here is kinda what i think it might be, i have no real way to check to see if it is correct though:
Select sum pweight
from parts
where distinct pcolor
looks horribly wrong so any help is appreciated. here is another one:
What colored part has total weight greater than 8 units?
select pcolor
from parts
where sum pweight > 8
???
i dunno lol.
there is a hint that says to use "GROUP BY" and "HAVING" but i dont see how that fits in... any ideas?
I am new to MS SQL coding and I am having a problem with date conversions. In PL/SQL, I could convert numeric months into character months in their own columns by using the DECODE function. An example would be:
DECODE(to_char(M.My_Table, 'MM'),
'04', 'Apr', '05', 'May', '06', 'Jun', '07',
'Jul', '08', 'Aug', '09', 'Sep', '10', 'Oct',
'11', 'Nov', '12', 'Dec', '01', 'Jan', '02',
'Feb', '03', 'Mar'),
DECODE(to_char(M.My_Table, 'MM'),
'04', 'Q1', '05', 'Q1', '06', 'Q1',
'07', 'Q2', '08', 'Q2', '09', 'Q2',
'10', 'Q3', '11', 'Q3', '12', 'Q3',
'01', 'Q4', '02', 'Q4', '03', 'Q4'),
How does one convert something like this in MS SQL?
Hi!If you like to spend some moments on my code examples,please look at it - usage is free (-:http://www.codeproject.com/useritems/TSQL_coding.aspFeedback welcome.GreetingsBjorn
View 1 Replies View RelatedHi. I'm looking at a problem and I can't find any solution short ofcoding.I have a pool of, say, 1000 PINS. I have 7 tables (buckets). Any ofthe 1000 PINS can be in 0, 1, 2, or... or all 7 buckets. So let's saythat-bucket A has 100 PINS-bucket B has 300 PINS-bucket C has 600 PINS-bucket D has 200 PINS-bucket E has 500 PINS-bucket F has 350 PINS-bucket G has 700 PINSI need to know, for each PIN, the number of buckets (tables) itbelongs to, and which ones, i.e:- PIN 1 belongs to A, C, D, so it belongs to 3 buckets- PIN 2 belongs to A, C, D, F, so it belongs to 4 buckets- PIN 3 belongs to A, so it belongs to 1 bucket- PIN 4 belongs to A, B, C, D, G, so it belongs to 5 buckets- PIN 5 belongs to ..., so it belongs to 0 bucketsetc, etcWhat would be the simplest way to achieve that, please ?Thank you very muchAlex.
View 5 Replies View Related