Database

Paul Conrad15-Sep-07 4:56

15-Sep-07 4:56

I forgot about TOP not allowing any variables.

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Giorgi Dalakishvili15-Sep-07 8:49

15-Sep-07 8:49

Just found out that in sql server 2005 TOP supports variables so you can write a parametrized sproc.

#region signature
my articles
#endregion

Paul Conrad15-Sep-07 9:35

15-Sep-07 9:35

That is interesting, because I tried and it complained that it wasn't valid.

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Giorgi Dalakishvili15-Sep-07 9:51

15-Sep-07 9:51

Did you put braces around the variable?

#region signature
my articles
#endregion

Paul Conrad15-Sep-07 10:03

15-Sep-07 10:03

I just tried and it works! Wow, I learned something new today Laugh | :laugh:

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Giorgi Dalakishvili15-Sep-07 10:05

15-Sep-07 10:05

Paul Conrad wrote:
Wow, I learned something new today

I also learned something new Smile | :)

#region signature
my articles
#endregion

Paul.B18-Sep-07 22:57

Paul.B

18-Sep-07 22:57

Okay, so as far as I understand it, having variable SQL queries in a stored procedure (except the obvious WHERE clause exception) is generally a good way to screw up the execution plan... So, seeing your responses to the question, why didn't you just do the following? I'm using the Northwind Database as the test DB.

CREATE PROC TestIdead @number smallint
AS
if @number = 10
    begin
       select top 10 * from Customers
    end
else if @number = 20
    begin
       select top 20 * from Customers
    end
else if @number = 30
   begin
      select top 30 * from Customers
   end

In hindsight, you could probably reduce the IF...ELSE stuff down to a CASE statement as well... Just wondering why you opt for dynamic SQL queries rather than a logic structure that would result in a faster executing stored procedure (because the queries are static and thus don't modify the execution plan)?

I'm a bit new to SQL and stored procs, so please enlighten me. Smile | :)

Giorgi Dalakishvili18-Sep-07 23:19

18-Sep-07 23:19

You don't need all those IF...Else stuff. Here is a sproc which works for all possible values

<br />
CREATE PROC TestIdead @number smallint<br />
AS<br />
select top (@number) from Customers<br />

#region signature
my articles
#endregion

Paul.B18-Sep-07 23:27

Paul.B

18-Sep-07 23:27

hehe... Unfortunately, I can't test that as I'm running against an SQL2000 server... and my SQL needs to be backwards compatible so... No variables in TOP statement for me!

Also, you didn't answer the question: Will the stored procedure I wrote run as fast or faster than the solution which uses the dynamic SQL query?

is there any possibility raise a problem

amraouf15-Sep-07 22:10

amraouf

15-Sep-07 22:10

use this:
create spTest ( @top int)
as
begin
declare @text nvarchar(500)

set @text = 'select top ' + @top + ' from table where 1=1'
sp_executesql @text
end

prasadbuddhika14-Sep-07 21:29

prasadbuddhika

14-Sep-07 21:29

in my application i create seperate logins for each user and user in db , is this ok for in the performence wise and what if i use one common lolgin and create users in the db is this more efficient than the first one. Smile | :)

Re: is there any possibility raise a problem

Paul Conrad15-Sep-07 4:07

Re: is there any possibility raise a problem

15-Sep-07 4:07

prasadbuddhika wrote:
use one common lolgin and create users in the db is this more efficient than the first one.

I have gone this route many of projects and have had no ill-effects from it.

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

prasadbuddhika15-Sep-07 16:54

prasadbuddhika

15-Sep-07 16:54

thanks,
is this means that i won't get any sort of db exception or error while many users are trying to access the db at the same time ?

SQL Security for your apps

Mark Cabbage14-Sep-07 20:06

Mark Cabbage

14-Sep-07 20:06

Hi everyone,

I was wondering how you all went about connecting to your databases from a security point of view. Some people say to use the SA password and embed security in your app with internal usernames/passwords, while others use Windows Security.

I think the SA password idea sounds simplier, and has the advantage of being able to limit what data the user gets to (eg: which clients information they can see) HOWEVER it doesn't stop someone using the password with MS Access to bypass your app and get the data.

So I was thinking the integrated security is best, and like any security, the use of groups would be best. My issue then becomes how to use those groups in SQL / VB.NET to enforce security, especially how would I enforce record level security (eg: which clients information they can see)

Thoughts everyone ??

Paul Conrad15-Sep-07 4:04

15-Sep-07 4:04

Mark Cabbage wrote:
Some people say to use the SA password

I've heard from numerous others that you should never use the sa password in your app. Create a user account for your app, granting it only the necessary permissions that it requires to get it's job done.

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

Mark Cabbage15-Sep-07 4:49

Mark Cabbage

15-Sep-07 4:49

Thanks for the reply Paul, however that doesn't solve the overall decision of using self-contained security accounts defined inside the account, or using Active Directory security.

Mike Dimmick16-Sep-07 6:37

Mike Dimmick

16-Sep-07 6:37

SQL Server's security model is pretty complicated.

The credentials you supply to the server map to a login. This login can be authenticated using SQL Server, by supplying a username and password, or can be authenticated by Windows (either through local accounts or domain accounts, if the server is a member of an NT or Active Directory domain). You can grant both Windows user accounts and Windows security groups a login. You do not supply a username or password for Windows Authentication - it uses the Windows credentials of the user running the client application, and the groups that they are members of. To use different credentials you must run the application as a different user.

By default, SQL Server authentication is disabled. The default configuration has an sa login which is SQL Server-authenticated, and allows the Windows security group BUILTIN\Administrators to log in as well.

Logins can be members of one or more server roles. These are fixed roles which have special permissions to manage or modify the server. All logins are members of the public server role - this cannot be removed. The only permission this role has in SQL Server 2005 is VIEW ANY DATABASE which means that the login can see all the databases on the system - without this, it can only see master, tempdb, and any databases it owns. sa and BUILTIN\Administrators are members of the sysadmin server role, which has full control over the database server. This is why you should never use sa in application code - it's simply far too privileged.

The set of server roles is fixed and, apart from public in SQL Server 2005, their permissions cannot be changed. In SQL Server 2005, many of the permissions can be granted individually to logins, while in SQL Server 2000 these permissions can only be granted through membership of the appropriate server role.

Just being granted a login to the server does not give any access to a database. The database must have a mapped user for the login. Again, the server roles do allow this to be overridden and in effect, members of the sysadmin server role have full control over every object in every database. Individual database users can be granted or denied permission to execute particular SQL statements on particular objects, and can also be granted permission to grant that permission to someone else.

Again, there are database roles which perform the function of groups of users. Again, there are predefined roles and everyone is a member of the public role. The login that owns the database is mapped to the dbo user and is a member of the db_owner role. Often a user is given the same name as the corresponding login.

There are also application roles which are independent of the user login. You change to an application role by running the sp_setapprole procedure. After this, the permissions are taken only from the specified application role, rather than using the user's permissions.

Permissions can be either GRANTed or DENYed. A login is allowed to do something if at least one of the roles that the login or user is a member of, or the login or user itself, is GRANTed the permission, and none of the above is DENYed. A DENY trumps any GRANTs. DENY is generally used only to exclude particular users that are members of a larger group which you want to GRANT permission to.

Getting down to the most commonly secured items, each of the basic query/DML statements SELECT, INSERT, UPDATE and DELETE are controllable for table and view objects, and SELECT and UPDATE for individual columns in a table. Users can be GRANTed permission to EXEC a stored procedure. The default is that the owner of an object has full control over it (users in a database can be granted permission to create their own objects, which in SQL Server 2000 results in a schema named by the creating user, while in SQL Server 2005 schemas are separate objects, so if granted permission another user can add new objects to the dbo schema) and the public role has no permissions at all (not denied, just not granted). The various other fixed database roles have specific permissions to all objects (e.g. db_datareader has SELECT permission).

What I commonly do is grant the public role the minimum permissions necessary to perform the application's tasks, generally firewalling the actual tables behind stored procedures and only granting the public database role permissions to EXEC those procedures. I then create a login for the application which is only a member of the public fixed server role, and map that to a user in the database that is also only a member of the public database role.

SQL Server does not have record-level permissions. You will need to do this at an application level (presumably through a stored procedure which the application can EXEC and don't grant permissions to SELECT from the table(s) it references). It may be possible to do this using some part of the Windows or SQL Server authentication model, but it's hard to discover Windows group or SQL Server role membership from within a stored procedure.

Stability. What an interesting concept. -- Chris Maunder