Back To Basics: Logins and Users, what’s the difference?

Published On: 2008-04-11By:

Usually Logins and Users are words which are interchangeable with each other.  However in Microsoft SQL Server they are very different things.  Because everyone assumes that they are the same thing, it can get a little confusing.

Logins are created at the database server instance level, while uses are created at the database level.  In other words logins are used to allow a user to connect to the SQL service (also called an instance).  You can have a login defined without having access to any databases on the server.  In this case you would have a login, but no users defined.  The user is created within the database and when it’s created is mapped to a login (users can be created without mapping them to a login, but we’ll talk about that at some point in the future).  This mapping is what allows the person connecting to the instance to use resources within the database.

If the login was created directly within the database, each database would have to keep track of the usernames and passwords of everyone who needed access to the database, which would cause a security nightmare.  Using the login in each database idea, lets create a login in each database called user1.  We set the password for user1 the same on all the databases on the server.  We then backup the database, change the password for that user on all the databases, then restore the database.  We now have an out of sync password for a single database on the server.

 Because of this mapping between logins and users, if you create a SQL Login on your server and grant it rights to a database via a user then backup the database, and restore the database to another server after creating a login on the second server with the same name.  You would think that the login would have access to the database.  However you would be wrong.  This is because the SID of the login and the user are different.  You have to use the sp_change_users_login procedure to sync the user with the login.

Denny

Contact the Author | Contact DCAC

Back To Basics: Functions, the good and the bad

Published On: By:

Functions are create little blocks of code.  They are fantastic for converting data from one format to another, or for looking up other values based on a lookup.  However this comes at a price.  That price is CPU power.  Doing all these additional lookups can cause extra strain on the database server.  It’s often better for the database server to simply join to the table which you are doing the lookup against.  However you can guarantee the same lookup is done every time when a function is used.

There are two kinds of functions.  One is a scalar function and one is a table function.  A scalar function is used as a column of a select statement, or a value in an update statement.  A table function is used in place of a table, and can be called directly or as a member of a JOIN.  While functions are fairly simple in concept, they are in my opinion the most complex objects to create.

 A scalar function accepts one or more input parameters and returns a single value of a predetermined data type.  The CREATE FUNCTION command is used to create both scalar and table functions.  To create a scalar function we use code along these lines.

CREATE FUNCTION dbo.FindObjectId
(
  @ObjectName nvarchar(512)
)
RETURNS INT
AS
BEGIN
  DECLARE @ObjectId INT
  SELECT @ObjectId = ObjectId
  FROM sysobjects
  WHERE name = @ObjectName
END
GO

We use this function to return the object id of an object in the database (yes I’m aware that there are system functions to do this, but it’s an easy function to use as an example).

SELECT dbo.FindObjectId('sysobjects')

As we can see a single value is returned in a record set. We can also return more than one value in the record set by using the function against a table.

SELECT dbo.FindObjectId(name)
FROM sysobjects

Creating a table function using much the same syntax. In this case we will return all the object names which have an object type which matches our input parameter.


CREATE FUNCTION dbo.ShowObjects
(
  @ObjectType CHAR(2)
)
RETURNS @TableNames TABLE (name nvarchar(512))
AS
BEGIN
  INSERT INTO @TableNames
  (name)
  SELECT name
  FROM sysobjects
  WHERE xtype = @ObjectType
RETURN
END
GO
To use this function we use a basic select statement.SELECT *
FROM dbo.ShowObjects('U')

We can also use it as a JOIN member.


SELECT sysobjects.name, a.name
FROM sysobjects
JOIN dbo.ShowObjects('U') a ON sysobjects.name = a.name

If you want to get very complex you can use what’s called an inline table function or single statement table function. This is basically a regular table function, but you can only put a single select command within the table function. As you can see from the code below the results will be the same, but the single line table function is a bit shorter. Because we are not declaring a table variable and loading the data into that table variable, then reading from that table variable a single statement table function should have a slightly cheaper execution plan compared to the same SELECT statement within a multi-statement table function.


CREATE FUNCTION dbo.ShowObjects
(
  @ObjectType CHAR(2)
)
RETURNS TABLE
AS
BEGIN
  RETURN (SELECT name
  FROM sysobjects
  WHERE xtype = @ObjectType)
END
GO

Denny

Contact the Author | Contact DCAC

Back To Basics: Stored Procedures, the work horse of the database

Published On: 2008-04-10By:

Stored procedures are extremely useful objects.  Not only do they store T/SQL scripts for later execution, but they also provide us with an extremely important security barrier between the user interface and the database.  The security barrier is used to prevent the users from needing SELECT, INSERT, UPDATE and/or DELETE rights directly to the database tables and views. 

This is done through what is called permissions chaining.  When a user has rights to execute a stored procedure they are given temporary rights to use the table objects within the procedures which are used by the table.

Creating stored procedures is very easy.  Take your Transact SQL code and put it below the CREATE PROCEDURE command, and end the batch.  Like all other database objects the name of the stored procedure must be unique within the schema (or owner for SQL 2000 and below).  As an example lets create a stored procedure which returns the names of all the tables in the current database.

CREATE PROCEDURE ShowTables AS
SELECT schema_name(schema_id), name
FROM sys.tables
GO

As you can see the basic syntax is very simple.  To run this stored procedure we simply run the stored procedure name.

exec ShowTables

You can add in input parameters to handle filtering, or which would need to be inserted into a table.  An input parameter is simply a variable which you set when you run the procedure.  You can access the value of the input parameter within the stored procedure as you would any other variable.  Let’s look at the same procedure but this time we want to filter the tables by the first letter.

CREATE PROCEDURE ShowTables
  @FilterChar NVARCHAR(2)
AS
SET @FilterChar = @FilterChar + '%'
SELECT schema_name(schema_id), name
FROM sys.tables
WHERE name LIKE @FilterChar
GO

In this example as you can see we take the input parameter, and add the % wild card, then use the variable to filter down the records to see only the records which start with the character we supply.  Running the stored procedure with an input parameter is just as easy.

exec ShowTables @FilterChar=N'C'

We can also use output parameters to get values back from the stored procedures.  Output parameters are used basically in the same way that input parameters are, however you add the OUTPUT keyword after the parameter.  Within the stored procedure simply set the output variable to the value you want it to return to the calling code.  This can be done anywhere within the stored procedure, as long as the variable still holds the value when the stored procedure has completed it’s execution the value will be returned to the calling code.  First lets look at the code to create the stored procedure.

 CREATE PROCEDURE ShowTables
  @FilterChar NVARCHAR(2),
  @RowCount INT OUTPUT
AS
SET @FilterChar = @FilterChar + ‘%’
SELECT schema_name(schema_id), name
FROM sys.tables
WHERE name LIKE @FilterChar

SET @RowCount = @@ROWCOUNT
GO

We run the stored procedure in much the same way we do with the input parameter.

DECLARE @RowCount INT
exec ShowTables @FilterChar=N’C’, @RowCount=@RowCount OUTPUT
SELECT @RowCount
 

In this case we are simply returning the row count as a second record set, but you’ll get the basic idea.

As I wrote earlier you can add records to a table with the stored procedure.

CREATE PROCEDURE InsertTable
  @Id INT,
  @Value VARCHAR(20)
AS
INSERT INTO SomeTable
(Id, Value)
VALUE
(@Id, @Value)
GO

As you can see it’s a very basic method.  It’s a regular insert statement with the parameters passed to it.

I know that this was a bit longer than the other posts, but I hope that you found it worth while.

Denny

Contact the Author | Contact DCAC

Back To Basics: Views, what exactly are they?

Published On: By:

Views are strange objects.  They look like tables, can be queried like a table, but they don’t store any actual data.  Think of them as a virtual table which has pointers back to the source tables.  Views can combine data from one or more tables via joins just like a select statement.

Using views does not help or hurt your query performance.  However if you create an index on your view, it will help your performance, however it will increase your storage as SQL will now need to keep the data for the columns of the index from the view on the disk within the index.

Personally I’m not a fan of views, as it distorts the schema of the database.  However like all objects within the database there is a time and a place for thier use.

Denny

Contact the Author | Contact DCAC
1 466 467 468 469 470 488

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
American Business Award 2021 Gold    American Business Award 2021 Bronze    FT Americas’ Fastest Growing Companies 2021   INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award
FT Americas’ Fastest Growing Companies 2020       Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award
Share via
Copy link