Changing SSN Filed to match correct format

Hi All
I asked this about a month ago and got a couple of answers but both were a
bit over my head so I am taking another shot, explaining it a bit more in
case I was not clear.
I inherited a database that did not put an input mask format in the ssn
field. The data entry people can now enter a persons ssn in any manner they
choose (123-45-6789, 123456789, abc123456, abc-12-3456, etc). Because I do
compare to other database sources that have it correctly formatted I get bad
matches (or worse no matches) because of the format. I need a way to
reformat the SSNum field so it is correctly formatted in the ###-##-####
manner (#'s in this case meaning alphanumeric, the dashes just need to be
there).
Any ideas?OK. I have it this far - -
If I use this formula:
SELECT LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum, 4)
as SSN, Clients.First_Name + ' ' + Clients.Last_Name
FROM Clients
It recodes the SSN as I need it. How do I get this result to the UPDATE
function inside the main db?
Possibly:
Update Clients
Set LEFT(ssnum, 3) + '-' + SUBSTRING(ssnum, 4, 2) + '-' + RIGHT(ssnum, 4)
'
"JOHN HARRIS" <harris1113@.fake.com> wrote in message
news:8AE9A112-DA51-474C-B1C7-8AB34229A813@.microsoft.com...
> Hi All
> I asked this about a month ago and got a couple of answers but both were a
> bit over my head so I am taking another shot, explaining it a bit more in
> case I was not clear.
> I inherited a database that did not put an input mask format in the ssn
> field. The data entry people can now enter a persons ssn in any manner
> they choose (123-45-6789, 123456789, abc123456, abc-12-3456, etc). Because
> I do compare to other database sources that have it correctly formatted I
> get bad matches (or worse no matches) because of the format. I need a way
> to reformat the SSNum field so it is correctly formatted in the
> ###-##-#### manner (#'s in this case meaning alphanumeric, the dashes just
> need to be there).
> Any ideas?

changing sql server name

Hi
The name of my sql server has an underscore in it and I need to get rid of
it in order to set up replication. BOL is a bit unclear as do I have to
change the name of the pc first of all (which also has underscore in name)
and then run sp_dropserver and sp_addserver. Really, my question is it
possible to rename sql server without first changing the name of the pc? So
can I just use the 2sp's without changing the name of the pc?
Thanks
Jonjo
Hi
You need to rename the PC. sp_dropserver and sp_addserver are there to fix
up the row in Master where the SQL Server queries to find out about itself.
It does not change the network name.
Regards
Mike
"jonjo" wrote:

> Hi
> The name of my sql server has an underscore in it and I need to get rid of
> it in order to set up replication. BOL is a bit unclear as do I have to
> change the name of the pc first of all (which also has underscore in name)
> and then run sp_dropserver and sp_addserver. Really, my question is it
> possible to rename sql server without first changing the name of the pc? So
> can I just use the 2sp's without changing the name of the pc?
> Thanks
> Jonjo

changing sql server name

Hi
The name of my sql server has an underscore in it and I need to get rid of
it in order to set up replication. BOL is a bit unclear as do I have to
change the name of the pc first of all (which also has underscore in name)
and then run sp_dropserver and sp_addserver. Really, my question is it
possible to rename sql server without first changing the name of the pc? So
can I just use the 2sp's without changing the name of the pc?
Thanks
JonjoHi
You need to rename the PC. sp_dropserver and sp_addserver are there to fix
up the row in Master where the SQL Server queries to find out about itself.
It does not change the network name.
Regards
Mike
"jonjo" wrote:

> Hi
> The name of my sql server has an underscore in it and I need to get rid of
> it in order to set up replication. BOL is a bit unclear as do I have to
> change the name of the pc first of all (which also has underscore in name)
> and then run sp_dropserver and sp_addserver. Really, my question is it
> possible to rename sql server without first changing the name of the pc? S
o
> can I just use the 2sp's without changing the name of the pc?
> Thanks
> Jonjo

changing sql server name

Hi
The name of my sql server has an underscore in it and I need to get rid of
it in order to set up replication. BOL is a bit unclear as do I have to
change the name of the pc first of all (which also has underscore in name)
and then run sp_dropserver and sp_addserver. Really, my question is it
possible to rename sql server without first changing the name of the pc? So
can I just use the 2sp's without changing the name of the pc?
Thanks
JonjoHi
You need to rename the PC. sp_dropserver and sp_addserver are there to fix
up the row in Master where the SQL Server queries to find out about itself.
It does not change the network name.
Regards
Mike
"jonjo" wrote:
> Hi
> The name of my sql server has an underscore in it and I need to get rid of
> it in order to set up replication. BOL is a bit unclear as do I have to
> change the name of the pc first of all (which also has underscore in name)
> and then run sp_dropserver and sp_addserver. Really, my question is it
> possible to rename sql server without first changing the name of the pc? So
> can I just use the 2sp's without changing the name of the pc?
> Thanks
> Jonjo

Changing login permissions

I am trying to set up a secure system and would like some advice on how to do it as it is a bit tricky.

The underlying security is Windows Authentication but I need to set various permission levels in the application.
What I want to do is to allow users read-only access to a users table. Once they are validated and their permission level is determined, then I want them to be assigned to the role that is set for their permission level.

I have looked at Application Roles but when I try to set the role up using the sp_setapprole then I get network errors (odd). There does not seem to be an SP which assigns a user to a role.

So I do I go about this?

Many thanks for your help.
Ian Logan

You can add a user to a role using sp_addrolemember.
The errors that you get for sp_setapprole are strange. Do you also get them when you're connected locally? Can you post these errors?

Thanks
Laurentiu|||

Can you please clarify how users are validated in your application? Is it handled by your aplication or SQLServer? If this is the latter, then you can assign permissions in advance to windows groups and your windows users who are the members of those groups will get those permissions upon logon to SQLserver. If this is former (i.e. you have applicatoin users, rather than windows or SQLServer principals) then you can
1) provide set of wrappers (such as table-valued functions) to access your data, which take application user name as an argument and have security logic inside; or
2) you can create a set of SQLServer users with necessary permission granted to them, map your application users to them using "EXECUTE AS user" feature on the TSQL procedure level or inside your application.

You can use sp_setapprole for 2, but I" believe it can be fully replaced by "EXECUTE AS"
sp_addrolemember is also available to add users to db roles, but itis not recommended to call it during your application on permanent basis, both due to security and reformance reasons.

|||

I am passing the Role Name and the Password to sp_setapprole. The role does exist in the database and it has permissions set for the various objects that I want to use.

I seem to be able to run sp_setapprole from the application, but as soon as I try to access the database again then the following error arises:

"System.Data.SqlClient.SqlException: General Network Error. Check your network documentation"

Note that I am using the Microsoft.Practices.EnterpriseLibrary.Data application block for all database interfacing. The application is obviously .NET and it is SQL 2000.

Note also that the code I am using works fine if I do not use sp_setapprole, i.e. the user has Windows Authentication and Public rights. (I have yet to REVOKE ALL FROM PUBLIC).

Many Thanks
Ian

Code:

Public Shared Function SetDBRole() As String

' Create the Database object, using the default database service. The

' default database service is determined through configuration.

Dim db As Database = DatabaseFactory.CreateDatabase()

Dim sqlCommand As String = "sp_setapprole"

Dim dbCommandWrapper As DBCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand)

' Add paramters

' Input parameters can specify the input value

dbCommandWrapper.AddInParameter("@.RoleName", DbType.String, "MyAppRole")

dbCommandWrapper.AddInParameter("@.Password", DbType.String, "MyPassword")

db.ExecuteNonQuery(dbCommandWrapper)

End Function

|||Ruslan

The users are to be validated by the application. However Windows Authentication is being used to allow them access to SQL Server in the first place. The users must logon separately to the application using a different user name and password from their Windows logon.

The application requires three tiers of user access (data entry, supervisor, etc) and the users of the application, plus their access level, are set up within the application.

The plan would be that then a new user is created then sp_addrolemember would be used to add then to the role. Then when they log in the users table would be looked up to determine their access level (role) and then sp_setapprole would be run to set their permissions.
Initially users would only have read-only access to the users table and no access to anything else. Actually, to be pedantic, nobody will have access to tables as I am using stored procedures for data access.

As for EXECUTE AS, I am using SQL 2000 and have not come across this. Is is 2005?

Kind Regards
Ian|||Ian, I don't understand why you both add users to a role and also calling sp_setapprole. I expect you could just use roles, have permissions assigned to them as appropriate, and then just have the users added to the appropriate role. The users would not be granted any permission directly, they would get their permissions from the role that they belong to. Wouldn't this address your security requirements?

Thanks
Laurentiu

PS: Yes, EXECUTE AS is a SQL 2005 feature.|||Laurentiu

Users would be added to a role when they were created within the application. However when they log in to the application then how is their role activated? I thought that you had to use sp_setapprole to do this.

As you may note from other parts to this thread, I am having problems using sp_setapprole. When I run it from SQL Query Analyser ( sp_setapprole "MyRole", "mypassword" ) then I get Msg 2762 saying that it has been invoked incorrectly. When I run it from .NET then I get the odd network error message as described in this thread.

I reckon that if I can get sp_setapprole to work then I should be there...

Kind Regards
Ian

|||

I've tried sp_setapprole on SQL 2000, and I would get error 2762 whenever I would try to set the approle again after it was already set. You can verify whether the approle is already set by executing:

select user_name()

This will return the approle name if it is already set.

If a SQL user is a member of a SQL database role, the role membership takes effect when the user connects to the database.

I still don't understand how authentication and authorization is handled by your app. You mentioned that:

"The users are to be validated by the application. However Windows Authentication is being used to allow them access to SQL Server in the first place. The users must logon separately to the application using a different user name and password from their Windows logon."

Does the app connect to SQL Server using the user's Windows credentials or by using some other Windows credentials? How are the users that you are adding to roles related to the users that connect to the app? Could you explain the steps that are involved when a person that uses the app logs in to it?

Thanks
Laurentiu

|||Laurentiu

I have discovered an article which explains some of the problems with sp_setapprole. It is 229564, and basically the connection pooling has to be disabled for sp_setapprole. Also, another post indicated that running sp_setapprole in SQL Query does not work, I think it was again due to connection issues. This looks like there may be a problem because connection pooling is useful and switching it off is a backward step.

This instance of SQL Server has only Windows Authentication.

1. The app connects using Windows Authentication.
2. On clicking OK on the app's Login, a user will be validated against a Users table in the database. This table will also contain their access level (1-3).
3. They will then be allocated one of three roles, based on their access level.

The user name and password in this table will be quite independent of their Windows user name and password as we want to make this as secure as possible. (We also need to encrypt at least one table as well - but that is another story).

The app will control the setting up of the users for the Users table. Only a superuser of the app will be able to do this. They will enter the user name, password and access level, and these will be stored in the Users table.

Kind regards
Ian
|||

I think I begin to understand. When you say roles, you really mean application roles, right, not database roles? You allocate a user to a role by calling sp_setapprole. I thought you were using both database roles and application roles.

I'll try to see if I can find anything else related to the limitations of using sp_setapprole with connection pooling.

Thanks
Laurentiu

|||Laurentiu

Any further thoughts on the connection pooling issue?

Kind Regards
Ian|||I inquired and there is no workaround on SQL Server 2000 to make sp_setapprole work with connection pooling.

Thanks
Laurentiu|||OK, many thanks. I will raise the connection pooling issue in another forum as I have some further questions about it.

Kind Regards
Ian Logan

Changing data type

Use databases a bit, but new to SQL Server. We just want to change a column of existing SQL Server 2005 data from a string data type to one of the UNiCODE data types, such as DT_WSTR or DT.NTEXT (such as one can use for various data mining tasks, etc.). It seems to do this one needs to "the data conversion transformation editor". To use that one has to have a package and a project?

Does any one have a full script or set of steps to do the full set of steps for what should be a simple task? This would be a great example for BOL, but each atomistic bit of BOL refers to another, and one gets lost in the circle when a complete example is needed for fundamantal housekeeping tasks.

Yes you need a project and a package. To get started with SSIS projects and packages, you should run through the SSIS tutorial. See http://msdn2.microsoft.com/en-us/library/ms170419(SQL.90).aspx - the steps of the tutorial take you through the project and package creation and into working with data flow

When you need to convert data, this BOL entry gives the steps for using the Data Conversion Component. http://msdn2.microsoft.com/en-us/library/ms140321.aspx

Donald

|||

Fairly new to SQL Server 2005, so please excuse a more basic question. I very much appreciate any further hint or clarification that you can provide!

It is sometime at least appears rather unclear to quickly see the necessary "big picture" in SQL 2005! For example, when is it best to use "graphical tools" (with projects and packages), or, can one use simple Transact- SQL statements to perhaps best perform the same (relatively simple) operation? As here, for example, to change column data type, could I not use the SQL commands ALTER TABLE, and/or, say CAST and CONVERT? Do these transforms work into the Unicode data types, such as DT_WSTR?

I very much appreciate any further hint or thought!

|||

J. Lewis wrote:

Fairly new to SQL Server 2005, so please excuse a more basic question. I very much appreciate any further hint or clarification that you can provide!

It is sometime at least appears rather unclear to quickly see the necessary "big picture" in SQL 2005! For example, when is it best to use "graphical tools" (with projects and packages), or, can one use simple Transact- SQL statements to perhaps best perform the same (relatively simple) operation? As here, for example, to change column data type, could I not use the SQL commands ALTER TABLE, and/or, say CAST and CONVERT? Do these transforms work into the Unicode data types, such as DT_WSTR?

I very much appreciate any further hint or thought!

J,

There appears to be some confusion between SQL Server Database Engine and SQL Server Integration Services.

Tables are stored in SQL Server database engine and can be manipulated using ALTER TABLE.

CAST and CONVERT are T-SQL fuctions. T-SQL is a programming language used to manipulate the DATA that is stored in tables (note the distinction here between ALTER TABLE which only operates on the table itself).

DT_WSTR is a data type within SQL Server Integration Services. It is NOT a data type within SQL Server Database Engine. Hence, CAST and CONVERT will not work on columns of type DT_WSTR.

With all that in mind, can you explain again exactly what it is you require to be able to do?

-Jamie

|||

Thank you greatly -- your explanation is really clear and very helpful. One does not always see the "big picture", when just looking at individual BOL pages!

What trying to do is is set-up to use the Term Extraction Transformation which as we understand it requires use of the DT_WSTR or DT_NTEXT data types. This is a fairly limited, focused job we were trying to complete in SQL Server 200 5. It had sadly, frankly not fully hit us that there were different data types across various components of SQL Server.

As we have a bit "in /out" job to do here, we are now at least hoping to find a simple, but reasonably complete, example script to set up a project/package to read in an input file, convert a data type in a column, and then run a Term Extraction.

Thank you for your help.

|||

Term Extraction Transform is part of SQL Server Integration Services so you are in the right place.

It sounds like you are a beginner so I would recommend you first watch this webcast: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032289998&EventCategory=5&culture=en-US&CountryCode=US and this: http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032273477&EventCategory=5&culture=en-US&CountryCode=US to introduce yourself to the product.

As for term extraction, I haven't seen much material although I vaguely recall a webcast that Donald Farmer (further up this thread) did in which it was mentioned. I can't find that webcast though. Hopefully Donald will reply and let you know.

-Jamie