I’ll be speaking at the NJ SQL Server User Group

Published On: 2008-04-14By:

The NJ SQL Server User Group has been kind enough to invite me to come speak to them about SQL Server Service Broker.

 I’ll be speaking up there on August, 19, 2008.

I’ll be giving a similar presentation to the one that I gave at the SoCal Code Camp in February 2008.  I’ll be revising the slide deck a little bit based on how that session went.  If you are in the area I’d be thrilled to have you come by, listen to me blather on about Service Broker for a bit, and chat.  The Address, directions and times are posted on the NJ SQL Server User Group web site.

See you in New Jersey.

Denny

Contact the Author | Contact DCAC

Back To Basics: How do I use sp_change_users_login?

Published On: 2008-04-11By:

The sp_change_users_login procedure has a specific purpose.  It’s used to identify and correct users within a database which do not have a corresponding logins.

You can specify the value of Report for the @Action input parameter to see any users which do not have a corresponding login.  This only needs to be done when you are restoring a database from one server to another, and the logins on each server were created with seperate SIDs.  An example of when this would happen is when you are restoring a database from Production to QA.

After identifying the users which are not synced to a login, use the update_one value for the @Action parameter and with the username of the user as the value for the @UserNamePattern input parameter, and the login name as the value of the @LoginName input parameter.  If the login doesn’t exist you can pass the password to the procedure for the @Password input parameter and the login will be created for you and mapped to the user.

Denny

Contact the Author | Contact DCAC

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

Published On: By:

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
1 430 431 432 433 434 452

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    Microsoft MVP    Microsoft Certified Master    VMWare vExpert
   Best Full-Service Cloud Technology Consulting Company    Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link