|
SQL Server has the concept of a login, which is global to a server, and a user, which is specific to a database.
Logins are what get authenticated - you must tell SQL Server either about a Windows username or group, or you must specify a username and password. You can assign fixed server roles to a login (alternatively you can say you make a login a member of a role). These roles control whether a login can create their own databases and various other sorts of administrative rights. You'll find these in Management Studio under Security, Logins.
Users are the same sort of concept within the database itself. A login must be granted access to, and mapped to a user in, a database to be able to access it (unless they're a member of the sysadmin fixed server role). The name of the user in the database can be different from the user's login - normally the database owner's login is given the name dbo within the database. Permissions in a database are assigned to a user or to a role - again, you can make users members of roles to make the permissions easier to manage. Create users in Management Studio by going to Database, Security, Users.
Normally I simply make users members of the public database role then set permissions for that role. I allow Management Studio to add the login to the database by checking the box next to the database in User Mapping in the login's Properties. If you need different permissions for different subsystems - probably a good idea - then create more logins and users.
I'm afraid I don't know much about using application roles. I can see they're there, and I think they would allow you to keep and use a single login but assign different permissions for different applications accessing the same database. For DNN this is almost certainly overkill. It looks like you need to code explicitly to use different application roles (the documentation mentions using sp_setapprole to change to a different role).
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi,
I've been googling all day, found info but it still doesn't work
I have a binary field of 50 bytes long. I need to load this from the database and
store it into a byte array in my application VC++ (NO .NET). But it seems I just can't convert the SafeArray data into a BYTE[]. I'm really stuck on this. I am getting despreratly, this shouldn't be this hard so what don't i see.
...
_RecordsetPtr rsFoo;
_variant_t vtHolder;
int iMyID;
BYTE* pMyData = new BYTE[50];
try
{
rsFoo.CreateInstance(__uuidof(Recordset));
rsFoo->Open((_bstr_t)strQuery, GetConnectionPtr().GetInterfacePtr(), adOpenDynamic,adLockReadOnly, adCmdText);
if(rsFoo->adoEOF && rsFoo->BOF)
return;
vtHolder = rsFoo->GetCollect("ID");
if(vtHolder.vt != NULL)
{
iMyId = (int)vtHolder;
}
vtHolder = rsFoo->GetCollect("Data");
if(vtHolder.vt != NULL)
{
....
....
}
}
catch...
codito ergo sum
|
|
|
|
|
Hi,
I am trying to set one of my data cloumn to auto-increment but there isn't any option in the column properties to do that. I am using VS 2005 to add column to my database, so can someone point me in the right direction? Thanks! And btw, I looked at some of the help which uses VB codes to set the column datatype and set autoincrement to true and stuff... How do you work those thing? haha.. Sorry no experience in VB.
-=Ken=-
|
|
|
|
|
Add the identity attribute to your column
|
|
|
|
|
when i try to attach database to sqlserver
ths error appear
"Server: Msg 602, Level 21, State 50, Line 1
Could not find row in sysindexes for database ID 8, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. Connection Broken"
i cann't connect to db what is the soulation?!
ma_refay
|
|
|
|
|
Hi, I am asked to work on ASP.NET2.0 web development project which is already in development stage. This project development is in ASP.NET2.0 and SQL Express.
I have downloaded the project related files from the server.
I saw few files as having ".dtsx" extenstion. and one script file (text file, consisting a script to create tables.)
I have following questions.
1. What is .dtsx file meant for?
2. How can I run the query to create datatables? I mean here, in SQL 2000, I used query analyzer to execute queries. How can I do it in SQLExpress? Can I execute the query from VS.NET2005?
Please let me know. Thanks in advance.
|
|
|
|
|
Hi,
Are there any techniques /rules/measures that should be followed to develop stored procedures so that the efficieny of the appliaiton increases.
Regards,
Uma
|
|
|
|
|
|
* Don't use cursors (there cursed)
* Set NoCount off
* have efficient SQL SELECT statements
* Sometimes using temp tables is quicker than complex joins from many tables
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
You could use the "show plan" in SQL200 or "display estimated execution plan" in SQL2005 to see exactly how costly each process is and maybe modify it accordingly.
Thanks.
|
|
|
|
|
I have an ASP datagrid in which the user can look up an existing record and update the fields, or insert a new record. So I have a couple of questions...
1. Is it considered better to delete the old record and insert the new (updated) record? I am rewriting this application and the old programmer did it this way which seemed odd.
2. If it is better to update, how do I program in SQL
if partNumber already exists, update
if PartNumber does not exist, insert
Thanks!
|
|
|
|
|
leckey wrote: 2. If it is better to update, how do I program in SQL
if partNumber already exists, update
if PartNumber does not exist, insert
You could use a stored procedure to do this.
|
|
|
|
|
something like this. 0 is default value for PartNumber and if it does not exist than it will be created and a new part number will be returned,
create proc myProc
@PartNumber int = 0,
@PartDesc varchar
AS
Declare @partNo_Used
if @PartNumber = 0
Begin
-- Insert into Table
insert parts ( partdesc ) Values ( @PartDesc)
--- Get identity or part number
Set @partNo_Used = @@Indentity
End
ELSE
Begin
-- Update Record
update parts
SET partdesc = @PartDesc
Where Partnumber = @PartNumber
@partNo_Used = @PartNumber
End
-- Return Part and Desc
SELECT @partNo_Used , @PartDesc
GO
---------------------------------------------------------------------------
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Hi
I wanted to know how should i deal with huge databases.
i mean is there any special technique ?
or i can do processes things with those simple command and queries?
if there is, please give me resource url and Book names.
(it is a Database project for a Store and I've been told it has a giant DataBase and your application should be fast and ...)
thanks
-- modified at 5:50 Monday 3rd July, 2006
|
|
|
|
|
|
How huge is huge. I've tun dbs upto 10Gb with no real problems. If you want realtime analysis then you have to get inventive with your data design, indexes etc..
you can split tables across databases or even tune your disks and datafiles as well i.e have data and indexes on seperate raid arrays or even have different tables on different drives etc...
This all depends wether its transactions, logging or reporting etc... As Colin said its all realative to your application.
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
hi everybody,
too many boys now angry with this problem.. and too many versions of this problem
this is mine:
i have no virtual machine and no servers (duing the installaion)
1. i have .net framework 2.0 and i have Ms visual studio 2005 .
2. i tried to install Ms SQL server 2005 Developer edition.
in first step [Server components, tools, Books Online, and samples] the installation sayas that net FRM 2.0 and support files required.
3. i click the install button and wow!! "error 193 installation Net Framework 2.0."
i cant understand, i have net framework 2.0 and it cant install it. :S
i uninstalled all visual studio 2005 and net framework 2.0 and all other sql services.
and the same problem appears again.
Note: i run the installation program from full DVD [includes x86 x64 and itanium]
and no servers are running while installations.
i closed antivirus and firewall programmes while installation.
but the same error - that damned "error 193"
and please help me, and feel good to help a good boy 
|
|
|
|
|
Hi
I'm trying to read RTF and Word files from blob columns in SQL Server, but the files can't be opened in Word when i retreive them. What am I doing wrong?
<br />
FileStream fileStream = new FileStream(filename, FileMode.Create, FileAccess.Write);<br />
BinaryWriter binaryWriter = new BinaryWriter(fileStream);<br />
<br />
long totalBytes = sqlDataReader.GetBytes(blobColumn, 0, null, 0, int.MaxValue);<br />
<br />
byte[] outbyte = new byte[totalBytes];<br />
sqlDataReader.GetBytes(blobColumn, 0, outbyte, 0, (int)totalBytes); <br />
binaryWriter.Write(outbyte, 0, (int)totalBytes);<br />
binaryWriter.Flush();<br />
<br />
binaryWriter.Close();<br />
fileStream.Close();<br />
|
|
|
|
|
Code looks okay.
|
|
|
|
|
I have a small access database at whitch I connect through OleDb. Nothing going bad so far.
I use C# for the client, so I connect using OleDBConnections, and querry the database through OleDbDataAdapters.
The problem apears at date/time fields in the database: using the data adapter the fields are revealed to the client as DateTime objects, but at update or insert commands I get an error message informing me that the date argument is mismatched.
After searching the net I found that date/time objects in Access should be passed into update/insert commands like strings between # chars. If I do like this the whole thing is ok, but is there any way that I can make the data adapter work with access?
Thank you.
protected internal static readonly ... and I wish the list could continue ...
|
|
|
|
|
I have found what the problem was.
In my access database I had a field with a reserved name, and the command builder didn't sourounded it between []. So afther changing the field's name all things went ok.
protected internal static readonly ... and I wish the list could continue ...
|
|
|
|
|
in reply of moving database in sql Colin Angus Mackay once wrote :
Two ways I can think of.
1. Detach the database move the files and then attach at the other end. (You'll then have to wire up the logins again as the login ids will be different because they are stored in the master db)
2. Create a backup of the database, move the backup file to the new system, then restore the backup (you'll again have to wire up the logins to accounts for the same reasons given above).
now i'm havg same problem(i'm using sql server 2005 express edition), i hav tried both ways but at second server when i click database diagrams then it gives masseage that no valid owner of this database exits so can't work with database diagrams, in database properties i tried to enter different owners but didn't worked so what should i do??
further more what does this mean "(You'll then have to wire up the logins again as the login ids will be different because they are stored in the master db)" i just cant get it?
May God help u solv ur problems as well,thx in advance!!
|
|
|
|
|
|
what about this problem???
"now i'm havg same problem(i'm using sql server 2005 express edition), i hav tried both ways but at second server when i click database diagrams then it gives masseage that no valid owner of this database exits so can't work with database diagrams, in database properties i tried to enter different owners but didn't worked so what should i do??
further more what does this mean "(You'll then have to wire up the logins again as the login ids will be different because they are stored in the master db)" i just cant get it?"
May God help u solv ur problems as well,thx in advance!!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
