Hi All,
Ingres has some functions for converting UUIDs. Specifically, they are
uuid_to_char() and uuid_from_char(). I'm new to MS SQL and would like
to write some built-in functions (or any other solution) that will
allow me to use the same code in MS SQL and Ingres.
Example:
select uuid_to_char(xyz_uuid) from xyz
I have written a simple built-in function to do this and it works just
fine. The one fly in the ointment is that the "database owner" seems
to be required to execute it.
"select uuid_to_char(xyz_uuid) from xyz" yields "'uuid_to_char' is not
a recognized function name".
"select dbo.uuid_to_char(xyz_uuid) from xyz" works just fine.
I have found in the documentation where it says that the owner is
required in this case.
Is there some other technique available to eliminate use of the owner
name?
Thanks!
Troy RudolphThere is, but it's undocumented/unsupported, and I guess that you wouldn't
want to rely on such a technique:
- create the function in master
- function name must start with fn_
- owner must be system_function_schema
- name must be all lowercase
- function should be created when the 'allow updates' server configuration
option is turned on
If you follow all the above requirements, you don't need to owner qualify
it, plus you can invoke it from any database. But again, I wouldn't rely on
an unsupported technique in a production environment.
The only supported way to invoke a scalar UDF in a query is to owner qualify
it, I'm afraid.
BG, SQL Server MVP
www.SolidQualityLearning.com
<Troy.Rudolph@.ca.com> wrote in message
news:1125340946.797761.324100@.z14g2000cwz.googlegroups.com...
> Hi All,
> Ingres has some functions for converting UUIDs. Specifically, they are
> uuid_to_char() and uuid_from_char(). I'm new to MS SQL and would like
> to write some built-in functions (or any other solution) that will
> allow me to use the same code in MS SQL and Ingres.
> Example:
> select uuid_to_char(xyz_uuid) from xyz
> I have written a simple built-in function to do this and it works just
> fine. The one fly in the ointment is that the "database owner" seems
> to be required to execute it.
> "select uuid_to_char(xyz_uuid) from xyz" yields "'uuid_to_char' is not
> a recognized function name".
> "select dbo.uuid_to_char(xyz_uuid) from xyz" works just fine.
> I have found in the documentation where it says that the owner is
> required in this case.
> Is there some other technique available to eliminate use of the owner
> name?
> Thanks!
> Troy Rudolph
>|||Thank you for the quick reply.
You mentioned that the function name has to start with "fn_". It that
true for invocation as well as definition? In other words, do I have
to invoke it like this:
select fn_uuid_to_char(xyz_uuid) from uuid
If so, it really won't help.
Thanks again
Troy|||> You mentioned that the function name has to start with "fn_". It that
> true for invocation as well as definition? In other words, do I have
> to invoke it like this:
Yep.
> If so, it really won't help.
Probably for the best. ;-)
BG, SQL Server MVP
www.SolidQualityLearning.com
<Troy.Rudolph@.ca.com> wrote in message
news:1125342504.627559.165260@.o13g2000cwo.googlegroups.com...
> Thank you for the quick reply.
> You mentioned that the function name has to start with "fn_". It that
> true for invocation as well as definition? In other words, do I have
> to invoke it like this:
> select fn_uuid_to_char(xyz_uuid) from uuid
> If so, it really won't help.
> Thanks again
> Troy
>|||Yes, defined as fn_myfunction, then called as fn_myfunction. There is no
shortcut.
I really would not recommend it for any custom database. Maybe, in some
exceptional situations I would use it for very generic functions that
Microsoft should have provided out of the box, never for customer or
business specific functions.
Reasons not to take this road:
- You have to do a lot of 'special' stuff. And everyone who ever has to
maintain this function needs to know all this. It would definitely add
to the cost of maintaining the software
- It is undocumented and therefore unsupported. Although unlikely, its
behavior could change with the next service pack
- Microsoft could decide to add system function and 'accidentally'
choose the same function name as you did
Gert-Jan
Troy.Rudolph@.ca.com wrote:
> Thank you for the quick reply.
> You mentioned that the function name has to start with "fn_". It that
> true for invocation as well as definition? In other words, do I have
> to invoke it like this:
> select fn_uuid_to_char(xyz_uuid) from uuid
> If so, it really won't help.
> Thanks again
> Troy
Showing posts with label built-in. Show all posts
Showing posts with label built-in. Show all posts
Tuesday, February 14, 2012
Built-in db_execute right
I noticed you are taking suggestions for SP2 release of SQL 2005. One thing that would be nice would be a built in security role of db_execute that would give execute rights to all stored procedures in a database and/or db_executeUser to execute only user stored procedures in a database. It would simplify releasing for me. I always seem to forget to give the user account rights to execute everytime I add a new stored procedure.
Thanx,
I second the request..
Brien King
brien@.classic-soft.com
|||
Actually, there is a way to do this in 2005. I missed it through several passes of learning the security infrastructure and it's a pretty neat capability.
The first thing to understand is that you grant permissions on a securable to a principle. Once you've gotten there, it gets interesting when you realize that a Database User is a principle and a schema is a securable. So, if you were to put all of your procs into a single schema and then grant execute permissions on the schema to the user, they would be able to execute all of the procs in that schema. Now going one step up, you can also grant execute permissions on a database which means that any proc within the database can be executed by the user that you granted permission to.
Run the following example.
1. Connect to an instance as the sysadmin
2. Create a new login (for this example mine will be called test and will be a SQL Server login)
3. Add this login to the AdventureWorks database without any permissions
4. Open a new query window with a connection using the login created in step 2
5. Run exec AdventureWorks.dbo.usp_GetManagerEmployees 3
6. You should get a permissions error
7. Open a new query window and connect it as sysadmin (technically database owner authority which is the same account in this case). Switch database context to AdventureWorks.
8. Execute the following statement GRANT EXECUTE ON SCHEMA::dbo TO test
9. Now switch back to the other query window and rerun the proc. You should now get a result
10. But, if you try to execute any proc in the HumanResources schema, you will get a permissions error.
11. Now switch back to the sysadmin connection. Execute: REVOKE EXECUTE ON SCHEMA::dbo TO test
12. Verify that you no longer have permissions
13. Now execute GRANT EXECUTE TO test within the sysadmin connection
14. Switch back to the other connection. Re-execute the procedure and verify that you can now execute it. As a matter of fact, you can execute any proc in any schema within the database at this point
So, there really isn't a need to a database role to accomplish this, because it is already there. In fact, this capability can also be done with SELECT, INSERT, UPDATE, and DELETE, so it really should be used instead of db_datareader and db_datawriter. (I would expect to see these roles disappear in the future.)
Built-in data types in SQL Express: best practices?
Greetings,
I think these should be rather simple questions, yet I spent a number of hours last night digging through the forums here and msdn and couldn't find any satisfactory answers. Basically, there tend to be types of information that are commonly saved in most databases, like names, addresses, phone numbers, email addresses, etc...and there are a variety of built in data types in SQL Server. What are the best built in datatypes for some of the common entries in a sql database. Also, there are a number of character based types and I am curious why one would be more useful in certain situations than another. Why is there char( ), nchar( ), varchar( ), nvarchar( ) and text datatypes? Why so many? Also, what is the "text" datatype and when is it most likely to be used? There is very little about the text type that I can find in the msdn or SQL Server docs...aside from the fact that it's text. On top of all this, there's numerous binary types as well. I'm really not getting the reason behind all these different basic types and why I would want to use one over the other in any specific instance.
TIA,
Mark
Hi,
there are sveeral things to keep in mind if you choose a datatype:
-Size: In your example CHAR / NCHAR / VARCHAR /NVARCHAR. THe NTypes requires twice the storage capacity than the other types, because they are unicode ready and are able to store unicode which the others aren′t. So, if you have unicode you should take them, otherwise you can leave them out. THE VAR types are variable in size, which means that not the whole length is reserved in SQL Server at storage time if its not used yet. Sample: You have a VARCHAR(4000) and a CHAR(4000) column. They both can store up to 4000 characters, but the CHAR column will reserve the space even if you enter only 1 character. The other one will only store the one character. if additional data is inserted in that column a pointer will point to the rest of the string. So you will have to decide between storage capacity or access speed. In the numeric section you might have noticed TINYINT / SMALLINT / INT /BIGINT. If you only want to store for example state of a specific thing like "Enrolled" with the ID 1, "Approved" with the ID 2 and so on for about 10 entried int he table you would probably take TINYINT because this is capaple for 256 entries (range is 0-255). For number above 255 you will take the next appropiate type like SMALLINT with a range of 0-32768. So in conclusion you will take the data type that will fit your problem but not waste any space in your database, becausse bigger datatypes will need more space for reservation.
-Functionality: For some Datatype you will have a lack of functionality. Let me explain: VARCHAR and Text both stores strings. TEXT can store up to 2GB, so why not always take TEXT. Beside that TEXT will be deprected in further version as there is a new data type which is called VARCHAR(MAX) text can support only some string operations in SQL Server. Also the client haviour of text is different, which forces you to specially handle it within your client code. So use VARCHAR(MAX) to store text bigger than 8000 character in a row. Some datatype like the TEXT and IMAGE won′t even allow Grouping whereas VARCHAR(MAX) will allow it.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
This is the most helpful information I've come across since I started working with SQL Express last year. I've ALWAYS just selected TEXT and INT datatypes because I couldn't find anything anywhere that laid bare the functionality of SQL datatypes as well as these 2 paragraphs do.
So, are you saying the IMAGE datatype is being phased out and the VARCHAR(MAX) is a preferrable alternative? Thank gawd for that bit of info, because not even an hour ago, I embarked upon my first adventure into storing images in a datatable and had selected (of course) IMAGE because it appeared to be the ideal datatype.
Of course, that is only the first of a thousand walls I'll stumble into before I actually see my images being stored (and accessed) in the table properly.
I hereby nominate Jens post for inclusion in the actual SQL documentation.
If someone will second the nomination, the motion will carry, and then be ignored entirely by the author of the actual documentation (who I believe is Gavin from the HBO series Kids In The Hall - http://en.wikipedia.org/wiki/Kids_in_the_Hall#Gavin - someone might be able to refute that though.)
|||Thanks for that
If you store binary data in SQL Server 2005, you should use the VARBINARY(MAX) type, for Text you should choose the VARCHAR(MAX) type.
Image is sort of a misunderstanding word as you don′t actually only store images in there :-D
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Not to digress, but you seem to be well-written and exceedingly well-versed in SQL...perhaps you might could make my transition into database IMAGE storage a bit smoother?
I've assigned the Image Column's datatype to VARBINARY(MAX).
In Visual Basic Express 2005, after saving the Table Definition, I open the Table Data to enter my records. Of course, entering the typed data is no problem, but I can't copy and paste specific images into individual records.
Should I assume that I have to INSERT the image via some convoluted and complex QUERY or some otherwise bizarre code sequence...or is it just plain impossible.
If there is no method that can be summarized in 3 or 4 lines of text, don't even waste your time posting it....maybe a link to the tome that offers the instructions (other than the 'official' documentation)
|||I agree, and I second your nomination k10wn. Motion passed. Jens pearls of wisdom may now offically be ignored and buried under tons of far less worthy posts.|||Another quick question about the datatypes:
What exactly does the 'SQL_VARIANT' type do?
|||Actually thats pretty simple:Take a look here: http://www.akadia.com/services/dotnet_read_write_blob.html
The method getPhoto will get the byte array which can be stored in SQL Server. You can′t copy & paste the file into SQL Server. The same for getting data back, you retrieve the data as a blob / byte array and either assign that to a control or write the stream to a file. So actually it can be summarized to 3/4 lines of code like the following:
BinaryReader br = new BinaryReader(fs);
byte[] photo = br.ReadBytes((int)fs.Length);
//Clean the house
br.Close();
fs.Close();
HTH, Jens Suessmeyer.http://www.sqlserver2005.de
|||SQL Variant is a untyped datatype which can store every data type in SQL Server exept thise one mentioned in the BOL (like Text, Image etc.) You probably use it if you don′t know the expected type which is entered. I would not recommend using this type and always use stongly type data.
More on this on your BOL or on: http://msdn2.microsoft.com/de-de/library/ms173829.aspx
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
|||
Thanks alot for your time...
I guess I should clarify:
I was actually suggesting that if your reply required more than 3 or 4 sentences in actual post itself, then you needn't waste your valuable time* on my frivolous, n00b questions. If that was the case, I hoped you might could provide a hyperlink to where I could read up on it.
But here you not only replied with a concise, plethora of lines in the post itself, but you provided example code (limited to a concise 3 or 4 lines) AND you posted a link to more detailed information....
I'm not worthy....
Again, thanks alot for the help!
*Time that might be better spent working on important projects like 
|||
Quite possibly the most helpful forum post I've ever clapped my ever so tired eyes on - thanks!
Craig
Built-in data types in SQL Express: best practices?
Greetings,
I think these should be rather simple questions, yet I spent a number of hours last night digging through the forums here and msdn and couldn't find any satisfactory answers. Basically, there tend to be types of information that are commonly saved in most databases, like names, addresses, phone numbers, email addresses, etc...and there are a variety of built in data types in SQL Server. What are the best built in datatypes for some of the common entries in a sql database. Also, there are a number of character based types and I am curious why one would be more useful in certain situations than another. Why is there char( ), nchar( ), varchar( ), nvarchar( ) and text datatypes? Why so many? Also, what is the "text" datatype and when is it most likely to be used? There is very little about the text type that I can find in the msdn or SQL Server docs...aside from the fact that it's text. On top of all this, there's numerous binary types as well. I'm really not getting the reason behind all these different basic types and why I would want to use one over the other in any specific instance.
TIA,
Mark
Hi,
there are sveeral things to keep in mind if you choose a datatype:
-Size: In your example CHAR / NCHAR / VARCHAR /NVARCHAR. THe NTypes requires twice the storage capacity than the other types, because they are unicode ready and are able to store unicode which the others aren′t. So, if you have unicode you should take them, otherwise you can leave them out. THE VAR types are variable in size, which means that not the whole length is reserved in SQL Server at storage time if its not used yet. Sample: You have a VARCHAR(4000) and a CHAR(4000) column. They both can store up to 4000 characters, but the CHAR column will reserve the space even if you enter only 1 character. The other one will only store the one character. if additional data is inserted in that column a pointer will point to the rest of the string. So you will have to decide between storage capacity or access speed. In the numeric section you might have noticed TINYINT / SMALLINT / INT /BIGINT. If you only want to store for example state of a specific thing like "Enrolled" with the ID 1, "Approved" with the ID 2 and so on for about 10 entried int he table you would probably take TINYINT because this is capaple for 256 entries (range is 0-255). For number above 255 you will take the next appropiate type like SMALLINT with a range of 0-32768. So in conclusion you will take the data type that will fit your problem but not waste any space in your database, becausse bigger datatypes will need more space for reservation.
-Functionality: For some Datatype you will have a lack of functionality. Let me explain: VARCHAR and Text both stores strings. TEXT can store up to 2GB, so why not always take TEXT. Beside that TEXT will be deprected in further version as there is a new data type which is called VARCHAR(MAX) text can support only some string operations in SQL Server. Also the client haviour of text is different, which forces you to specially handle it within your client code. So use VARCHAR(MAX) to store text bigger than 8000 character in a row. Some datatype like the TEXT and IMAGE won′t even allow Grouping whereas VARCHAR(MAX) will allow it.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
This is the most helpful information I've come across since I started working with SQL Express last year. I've ALWAYS just selected TEXT and INT datatypes because I couldn't find anything anywhere that laid bare the functionality of SQL datatypes as well as these 2 paragraphs do.
So, are you saying the IMAGE datatype is being phased out and the VARCHAR(MAX) is a preferrable alternative? Thank gawd for that bit of info, because not even an hour ago, I embarked upon my first adventure into storing images in a datatable and had selected (of course) IMAGE because it appeared to be the ideal datatype.
Of course, that is only the first of a thousand walls I'll stumble into before I actually see my images being stored (and accessed) in the table properly.
I hereby nominate Jens post for inclusion in the actual SQL documentation.
If someone will second the nomination, the motion will carry, and then be ignored entirely by the author of the actual documentation (who I believe is Gavin from the HBO series Kids In The Hall - http://en.wikipedia.org/wiki/Kids_in_the_Hall#Gavin - someone might be able to refute that though.)
|||Thanks for that
If you store binary data in SQL Server 2005, you should use the VARBINARY(MAX) type, for Text you should choose the VARCHAR(MAX) type.
Image is sort of a misunderstanding word as you don′t actually only store images in there :-D
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Not to digress, but you seem to be well-written and exceedingly well-versed in SQL...perhaps you might could make my transition into database IMAGE storage a bit smoother?
I've assigned the Image Column's datatype to VARBINARY(MAX).
In Visual Basic Express 2005, after saving the Table Definition, I open the Table Data to enter my records. Of course, entering the typed data is no problem, but I can't copy and paste specific images into individual records.
Should I assume that I have to INSERT the image via some convoluted and complex QUERY or some otherwise bizarre code sequence...or is it just plain impossible.
If there is no method that can be summarized in 3 or 4 lines of text, don't even waste your time posting it....maybe a link to the tome that offers the instructions (other than the 'official' documentation)
|||I agree, and I second your nomination k10wn. Motion passed. Jens pearls of wisdom may now offically be ignored and buried under tons of far less worthy posts.|||Another quick question about the datatypes:
What exactly does the 'SQL_VARIANT' type do?
|||Actually thats pretty simple:Take a look here: http://www.akadia.com/services/dotnet_read_write_blob.html
The method getPhoto will get the byte array which can be stored in SQL Server. You can′t copy & paste the file into SQL Server. The same for getting data back, you retrieve the data as a blob / byte array and either assign that to a control or write the stream to a file. So actually it can be summarized to 3/4 lines of code like the following:
BinaryReader br = new BinaryReader(fs);
byte[] photo = br.ReadBytes((int)fs.Length);
//Clean the house
br.Close();
fs.Close();
HTH, Jens Suessmeyer.http://www.sqlserver2005.de
|||SQL Variant is a untyped datatype which can store every data type in SQL Server exept thise one mentioned in the BOL (like Text, Image etc.) You probably use it if you don′t know the expected type which is entered. I would not recommend using this type and always use stongly type data.
More on this on your BOL or on: http://msdn2.microsoft.com/de-de/library/ms173829.aspx
HTH, jens Suessmeyer.
http://www.sqlserver2005.de|||
Thanks alot for your time...
I guess I should clarify:
I was actually suggesting that if your reply required more than 3 or 4 sentences in actual post itself, then you needn't waste your valuable time* on my frivolous, n00b questions. If that was the case, I hoped you might could provide a hyperlink to where I could read up on it.
But here you not only replied with a concise, plethora of lines in the post itself, but you provided example code (limited to a concise 3 or 4 lines) AND you posted a link to more detailed information....
I'm not worthy....
Again, thanks alot for the help!
*Time that might be better spent working on important projects like 
|||
Quite possibly the most helpful forum post I've ever clapped my ever so tired eyes on - thanks!
Craig
Built-in data types in SQL Express: best practices?
Greetings,
I think these should be rather simple questions, yet I spent a number of hours last night digging through the forums here and msdn and couldn't find any satisfactory answers. Basically, there tend to be types of information that are commonly saved in most databases, like names, addresses, phone numbers, email addresses, etc...and there are a variety of built in data types in SQL Server. What are the best built in datatypes for some of the common entries in a sql database. Also, there are a number of character based types and I am curious why one would be more useful in certain situations than another. Why is there char( ), nchar( ), varchar( ), nvarchar( ) and text datatypes? Why so many? Also, what is the "text" datatype and when is it most likely to be used? There is very little about the text type that I can find in the msdn or SQL Server docs...aside from the fact that it's text. On top of all this, there's numerous binary types as well. I'm really not getting the reason behind all these different basic types and why I would want to use one over the other in any specific instance.
TIA,
Mark
Hi,
there are sveeral things to keep in mind if you choose a datatype:
-Size: In your example CHAR / NCHAR / VARCHAR /NVARCHAR. THe NTypes requires twice the storage capacity than the other types, because they are unicode ready and are able to store unicode which the others aren′t. So, if you have unicode you should take them, otherwise you can leave them out. THE VAR types are variable in size, which means that not the whole length is reserved in SQL Server at storage time if its not used yet. Sample: You have a VARCHAR(4000) and a CHAR(4000) column. They both can store up to 4000 characters, but the CHAR column will reserve the space even if you enter only 1 character. The other one will only store the one character. if additional data is inserted in that column a pointer will point to the rest of the string. So you will have to decide between storage capacity or access speed. In the numeric section you might have noticed TINYINT / SMALLINT / INT /BIGINT. If you only want to store for example state of a specific thing like "Enrolled" with the ID 1, "Approved" with the ID 2 and so on for about 10 entried int he table you would probably take TINYINT because this is capaple for 256 entries (range is 0-255). For number above 255 you will take the next appropiate type like SMALLINT with a range of 0-32768. So in conclusion you will take the data type that will fit your problem but not waste any space in your database, becausse bigger datatypes will need more space for reservation.
-Functionality: For some Datatype you will have a lack of functionality. Let me explain: VARCHAR and Text both stores strings. TEXT can store up to 2GB, so why not always take TEXT. Beside that TEXT will be deprected in further version as there is a new data type which is called VARCHAR(MAX) text can support only some string operations in SQL Server. Also the client haviour of text is different, which forces you to specially handle it within your client code. So use VARCHAR(MAX) to store text bigger than 8000 character in a row. Some datatype like the TEXT and IMAGE won′t even allow Grouping whereas VARCHAR(MAX) will allow it.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
This is the most helpful information I've come across since I started working with SQL Express last year. I've ALWAYS just selected TEXT and INT datatypes because I couldn't find anything anywhere that laid bare the functionality of SQL datatypes as well as these 2 paragraphs do.
So, are you saying the IMAGE datatype is being phased out and the VARCHAR(MAX) is a preferrable alternative? Thank gawd for that bit of info, because not even an hour ago, I embarked upon my first adventure into storing images in a datatable and had selected (of course) IMAGE because it appeared to be the ideal datatype.
Of course, that is only the first of a thousand walls I'll stumble into before I actually see my images being stored (and accessed) in the table properly.
I hereby nominate Jens post for inclusion in the actual SQL documentation.
If someone will second the nomination, the motion will carry, and then be ignored entirely by the author of the actual documentation (who I believe is Gavin from the HBO series Kids In The Hall - http://en.wikipedia.org/wiki/Kids_in_the_Hall#Gavin - someone might be able to refute that though.)
|||Thanks for that
If you store binary data in SQL Server 2005, you should use the VARBINARY(MAX) type, for Text you should choose the VARCHAR(MAX) type.
Image is sort of a misunderstanding word as you don′t actually only store images in there :-D
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Not to digress, but you seem to be well-written and exceedingly well-versed in SQL...perhaps you might could make my transition into database IMAGE storage a bit smoother?
I've assigned the Image Column's datatype to VARBINARY(MAX).
In Visual Basic Express 2005, after saving the Table Definition, I open the Table Data to enter my records. Of course, entering the typed data is no problem, but I can't copy and paste specific images into individual records.
Should I assume that I have to INSERT the image via some convoluted and complex QUERY or some otherwise bizarre code sequence...or is it just plain impossible.
If there is no method that can be summarized in 3 or 4 lines of text, don't even waste your time posting it....maybe a link to the tome that offers the instructions (other than the 'official' documentation)
|||I agree, and I second your nomination k10wn. Motion passed. Jens pearls of wisdom may now offically be ignored and buried under tons of far less worthy posts.|||Another quick question about the datatypes:
What exactly does the 'SQL_VARIANT' type do?
|||Actually thats pretty simple:Take a look here: http://www.akadia.com/services/dotnet_read_write_blob.html
The method getPhoto will get the byte array which can be stored in SQL Server. You can′t copy & paste the file into SQL Server. The same for getting data back, you retrieve the data as a blob / byte array and either assign that to a control or write the stream to a file. So actually it can be summarized to 3/4 lines of code like the following:
BinaryReader br = new BinaryReader(fs);
byte[] photo = br.ReadBytes((int)fs.Length);
//Clean the house
br.Close();
fs.Close();
HTH, Jens Suessmeyer.http://www.sqlserver2005.de
|||SQL Variant is a untyped datatype which can store every data type in SQL Server exept thise one mentioned in the BOL (like Text, Image etc.) You probably use it if you don′t know the expected type which is entered. I would not recommend using this type and always use stongly type data.
More on this on your BOL or on: http://msdn2.microsoft.com/de-de/library/ms173829.aspx
HTH, jens Suessmeyer.
http://www.sqlserver2005.de|||
Thanks alot for your time...
I guess I should clarify:
I was actually suggesting that if your reply required more than 3 or 4 sentences in actual post itself, then you needn't waste your valuable time* on my frivolous, n00b questions. If that was the case, I hoped you might could provide a hyperlink to where I could read up on it.
But here you not only replied with a concise, plethora of lines in the post itself, but you provided example code (limited to a concise 3 or 4 lines) AND you posted a link to more detailed information....
I'm not worthy....
Again, thanks alot for the help!
*Time that might be better spent working on important projects like
|||
Quite possibly the most helpful forum post I've ever clapped my ever so tired eyes on - thanks!
Craig
Built-In Administrators disabled! Please help!
We're in a big mess here:
One of our groups has taken it upon themselves to cleanup some of their SQL
servers of old and unwanted logins. The first thing to go on the first
machine was the 'built-in administrators' group. Now, the SQL server is
unreachable. Can't register to another machine, can't make changes, NOTHING
.
Can someone provide some guidance to enabling the account, please?
Also, I wasn't aware that disabling the built-in would cause this problem,
especially since we have alternate accounts that act as db owners and
administrators. is the built-in account somehing we should just put on the
side and leave alone? Thanks!add a login account for the Local System account
using sp_grantlogin [NT Authority\System] and ensure that the login acco
unt
is a member of the sysadmin fixed server role.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"D Lee" <DLee@.discussions.microsoft.com> wrote in message
news:4FC1DBEE-21BE-4B9F-9B99-AA616A5ACB26@.microsoft.com...
> We're in a big mess here:
> One of our groups has taken it upon themselves to cleanup some of their
> SQL
> servers of old and unwanted logins. The first thing to go on the first
> machine was the 'built-in administrators' group. Now, the SQL server is
> unreachable. Can't register to another machine, can't make changes,
> NOTHING.
> Can someone provide some guidance to enabling the account, please?
> Also, I wasn't aware that disabling the built-in would cause this problem,
> especially since we have alternate accounts that act as db owners and
> administrators. is the built-in account somehing we should just put on
> the
> side and leave alone? Thanks!|||hillary's idea sounds right to me. I will add that disabling the local
admins is a great security idea but the genius left out a step.
Create a SQL Server Administrators group in the domain. Add that group to
the local administrators group on the box. Add that group to the SQL Server
and make it a sysadmin. NOW take out the local administrators and you'll be
fine.
BTW: don't forget to add people to the SQL Server Administrators group.
d.
"D Lee" <DLee@.discussions.microsoft.com> wrote in message
news:4FC1DBEE-21BE-4B9F-9B99-AA616A5ACB26@.microsoft.com...
> We're in a big mess here:
> One of our groups has taken it upon themselves to cleanup some of their
SQL
> servers of old and unwanted logins. The first thing to go on the first
> machine was the 'built-in administrators' group. Now, the SQL server is
> unreachable. Can't register to another machine, can't make changes,
NOTHING.
> Can someone provide some guidance to enabling the account, please?
> Also, I wasn't aware that disabling the built-in would cause this problem,
> especially since we have alternate accounts that act as db owners and
> administrators. is the built-in account somehing we should just put on
the
> side and leave alone? Thanks!
One of our groups has taken it upon themselves to cleanup some of their SQL
servers of old and unwanted logins. The first thing to go on the first
machine was the 'built-in administrators' group. Now, the SQL server is
unreachable. Can't register to another machine, can't make changes, NOTHING
.
Can someone provide some guidance to enabling the account, please?
Also, I wasn't aware that disabling the built-in would cause this problem,
especially since we have alternate accounts that act as db owners and
administrators. is the built-in account somehing we should just put on the
side and leave alone? Thanks!add a login account for the Local System account
using sp_grantlogin [NT Authority\System] and ensure that the login acco
unt
is a member of the sysadmin fixed server role.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"D Lee" <DLee@.discussions.microsoft.com> wrote in message
news:4FC1DBEE-21BE-4B9F-9B99-AA616A5ACB26@.microsoft.com...
> We're in a big mess here:
> One of our groups has taken it upon themselves to cleanup some of their
> SQL
> servers of old and unwanted logins. The first thing to go on the first
> machine was the 'built-in administrators' group. Now, the SQL server is
> unreachable. Can't register to another machine, can't make changes,
> NOTHING.
> Can someone provide some guidance to enabling the account, please?
> Also, I wasn't aware that disabling the built-in would cause this problem,
> especially since we have alternate accounts that act as db owners and
> administrators. is the built-in account somehing we should just put on
> the
> side and leave alone? Thanks!|||hillary's idea sounds right to me. I will add that disabling the local
admins is a great security idea but the genius left out a step.
Create a SQL Server Administrators group in the domain. Add that group to
the local administrators group on the box. Add that group to the SQL Server
and make it a sysadmin. NOW take out the local administrators and you'll be
fine.
BTW: don't forget to add people to the SQL Server Administrators group.
d.
"D Lee" <DLee@.discussions.microsoft.com> wrote in message
news:4FC1DBEE-21BE-4B9F-9B99-AA616A5ACB26@.microsoft.com...
> We're in a big mess here:
> One of our groups has taken it upon themselves to cleanup some of their
SQL
> servers of old and unwanted logins. The first thing to go on the first
> machine was the 'built-in administrators' group. Now, the SQL server is
> unreachable. Can't register to another machine, can't make changes,
NOTHING.
> Can someone provide some guidance to enabling the account, please?
> Also, I wasn't aware that disabling the built-in would cause this problem,
> especially since we have alternate accounts that act as db owners and
> administrators. is the built-in account somehing we should just put on
the
> side and leave alone? Thanks!
Built-In Administrators disabled! Please help!
We're in a big mess here:
One of our groups has taken it upon themselves to cleanup some of their SQL
servers of old and unwanted logins. The first thing to go on the first
machine was the 'built-in administrators' group. Now, the SQL server is
unreachable. Can't register to another machine, can't make changes, NOTHING.
Can someone provide some guidance to enabling the account, please?
Also, I wasn't aware that disabling the built-in would cause this problem,
especially since we have alternate accounts that act as db owners and
administrators. is the built-in account somehing we should just put on the
side and leave alone? Thanks!
add a login account for the Local System account
using sp_grantlogin [NT Authority\System] and ensure that the login account
is a member of the sysadmin fixed server role.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"D Lee" <DLee@.discussions.microsoft.com> wrote in message
news:4FC1DBEE-21BE-4B9F-9B99-AA616A5ACB26@.microsoft.com...
> We're in a big mess here:
> One of our groups has taken it upon themselves to cleanup some of their
> SQL
> servers of old and unwanted logins. The first thing to go on the first
> machine was the 'built-in administrators' group. Now, the SQL server is
> unreachable. Can't register to another machine, can't make changes,
> NOTHING.
> Can someone provide some guidance to enabling the account, please?
> Also, I wasn't aware that disabling the built-in would cause this problem,
> especially since we have alternate accounts that act as db owners and
> administrators. is the built-in account somehing we should just put on
> the
> side and leave alone? Thanks!
|||hillary's idea sounds right to me. I will add that disabling the local
admins is a great security idea but the genius left out a step.
Create a SQL Server Administrators group in the domain. Add that group to
the local administrators group on the box. Add that group to the SQL Server
and make it a sysadmin. NOW take out the local administrators and you'll be
fine.
BTW: don't forget to add people to the SQL Server Administrators group.
d.
"D Lee" <DLee@.discussions.microsoft.com> wrote in message
news:4FC1DBEE-21BE-4B9F-9B99-AA616A5ACB26@.microsoft.com...
> We're in a big mess here:
> One of our groups has taken it upon themselves to cleanup some of their
SQL
> servers of old and unwanted logins. The first thing to go on the first
> machine was the 'built-in administrators' group. Now, the SQL server is
> unreachable. Can't register to another machine, can't make changes,
NOTHING.
> Can someone provide some guidance to enabling the account, please?
> Also, I wasn't aware that disabling the built-in would cause this problem,
> especially since we have alternate accounts that act as db owners and
> administrators. is the built-in account somehing we should just put on
the
> side and leave alone? Thanks!
One of our groups has taken it upon themselves to cleanup some of their SQL
servers of old and unwanted logins. The first thing to go on the first
machine was the 'built-in administrators' group. Now, the SQL server is
unreachable. Can't register to another machine, can't make changes, NOTHING.
Can someone provide some guidance to enabling the account, please?
Also, I wasn't aware that disabling the built-in would cause this problem,
especially since we have alternate accounts that act as db owners and
administrators. is the built-in account somehing we should just put on the
side and leave alone? Thanks!
add a login account for the Local System account
using sp_grantlogin [NT Authority\System] and ensure that the login account
is a member of the sysadmin fixed server role.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"D Lee" <DLee@.discussions.microsoft.com> wrote in message
news:4FC1DBEE-21BE-4B9F-9B99-AA616A5ACB26@.microsoft.com...
> We're in a big mess here:
> One of our groups has taken it upon themselves to cleanup some of their
> SQL
> servers of old and unwanted logins. The first thing to go on the first
> machine was the 'built-in administrators' group. Now, the SQL server is
> unreachable. Can't register to another machine, can't make changes,
> NOTHING.
> Can someone provide some guidance to enabling the account, please?
> Also, I wasn't aware that disabling the built-in would cause this problem,
> especially since we have alternate accounts that act as db owners and
> administrators. is the built-in account somehing we should just put on
> the
> side and leave alone? Thanks!
|||hillary's idea sounds right to me. I will add that disabling the local
admins is a great security idea but the genius left out a step.
Create a SQL Server Administrators group in the domain. Add that group to
the local administrators group on the box. Add that group to the SQL Server
and make it a sysadmin. NOW take out the local administrators and you'll be
fine.
BTW: don't forget to add people to the SQL Server Administrators group.
d.
"D Lee" <DLee@.discussions.microsoft.com> wrote in message
news:4FC1DBEE-21BE-4B9F-9B99-AA616A5ACB26@.microsoft.com...
> We're in a big mess here:
> One of our groups has taken it upon themselves to cleanup some of their
SQL
> servers of old and unwanted logins. The first thing to go on the first
> machine was the 'built-in administrators' group. Now, the SQL server is
> unreachable. Can't register to another machine, can't make changes,
NOTHING.
> Can someone provide some guidance to enabling the account, please?
> Also, I wasn't aware that disabling the built-in would cause this problem,
> especially since we have alternate accounts that act as db owners and
> administrators. is the built-in account somehing we should just put on
the
> side and leave alone? Thanks!
Built-In Administrators disabled! Please help!
We're in a big mess here:
One of our groups has taken it upon themselves to cleanup some of their SQL
servers of old and unwanted logins. The first thing to go on the first
machine was the 'built-in administrators' group. Now, the SQL server is
unreachable. Can't register to another machine, can't make changes, NOTHING.
Can someone provide some guidance to enabling the account, please?
Also, I wasn't aware that disabling the built-in would cause this problem,
especially since we have alternate accounts that act as db owners and
administrators. is the built-in account somehing we should just put on the
side and leave alone? Thanks!add a login account for the Local System account
using sp_grantlogin [NT Authority\System] and ensure that the login account
is a member of the sysadmin fixed server role.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"D Lee" <DLee@.discussions.microsoft.com> wrote in message
news:4FC1DBEE-21BE-4B9F-9B99-AA616A5ACB26@.microsoft.com...
> We're in a big mess here:
> One of our groups has taken it upon themselves to cleanup some of their
> SQL
> servers of old and unwanted logins. The first thing to go on the first
> machine was the 'built-in administrators' group. Now, the SQL server is
> unreachable. Can't register to another machine, can't make changes,
> NOTHING.
> Can someone provide some guidance to enabling the account, please?
> Also, I wasn't aware that disabling the built-in would cause this problem,
> especially since we have alternate accounts that act as db owners and
> administrators. is the built-in account somehing we should just put on
> the
> side and leave alone? Thanks!|||hillary's idea sounds right to me. I will add that disabling the local
admins is a great security idea but the genius left out a step.
Create a SQL Server Administrators group in the domain. Add that group to
the local administrators group on the box. Add that group to the SQL Server
and make it a sysadmin. NOW take out the local administrators and you'll be
fine.
BTW: don't forget to add people to the SQL Server Administrators group.
d.
"D Lee" <DLee@.discussions.microsoft.com> wrote in message
news:4FC1DBEE-21BE-4B9F-9B99-AA616A5ACB26@.microsoft.com...
> We're in a big mess here:
> One of our groups has taken it upon themselves to cleanup some of their
SQL
> servers of old and unwanted logins. The first thing to go on the first
> machine was the 'built-in administrators' group. Now, the SQL server is
> unreachable. Can't register to another machine, can't make changes,
NOTHING.
> Can someone provide some guidance to enabling the account, please?
> Also, I wasn't aware that disabling the built-in would cause this problem,
> especially since we have alternate accounts that act as db owners and
> administrators. is the built-in account somehing we should just put on
the
> side and leave alone? Thanks!
One of our groups has taken it upon themselves to cleanup some of their SQL
servers of old and unwanted logins. The first thing to go on the first
machine was the 'built-in administrators' group. Now, the SQL server is
unreachable. Can't register to another machine, can't make changes, NOTHING.
Can someone provide some guidance to enabling the account, please?
Also, I wasn't aware that disabling the built-in would cause this problem,
especially since we have alternate accounts that act as db owners and
administrators. is the built-in account somehing we should just put on the
side and leave alone? Thanks!add a login account for the Local System account
using sp_grantlogin [NT Authority\System] and ensure that the login account
is a member of the sysadmin fixed server role.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"D Lee" <DLee@.discussions.microsoft.com> wrote in message
news:4FC1DBEE-21BE-4B9F-9B99-AA616A5ACB26@.microsoft.com...
> We're in a big mess here:
> One of our groups has taken it upon themselves to cleanup some of their
> SQL
> servers of old and unwanted logins. The first thing to go on the first
> machine was the 'built-in administrators' group. Now, the SQL server is
> unreachable. Can't register to another machine, can't make changes,
> NOTHING.
> Can someone provide some guidance to enabling the account, please?
> Also, I wasn't aware that disabling the built-in would cause this problem,
> especially since we have alternate accounts that act as db owners and
> administrators. is the built-in account somehing we should just put on
> the
> side and leave alone? Thanks!|||hillary's idea sounds right to me. I will add that disabling the local
admins is a great security idea but the genius left out a step.
Create a SQL Server Administrators group in the domain. Add that group to
the local administrators group on the box. Add that group to the SQL Server
and make it a sysadmin. NOW take out the local administrators and you'll be
fine.
BTW: don't forget to add people to the SQL Server Administrators group.
d.
"D Lee" <DLee@.discussions.microsoft.com> wrote in message
news:4FC1DBEE-21BE-4B9F-9B99-AA616A5ACB26@.microsoft.com...
> We're in a big mess here:
> One of our groups has taken it upon themselves to cleanup some of their
SQL
> servers of old and unwanted logins. The first thing to go on the first
> machine was the 'built-in administrators' group. Now, the SQL server is
> unreachable. Can't register to another machine, can't make changes,
NOTHING.
> Can someone provide some guidance to enabling the account, please?
> Also, I wasn't aware that disabling the built-in would cause this problem,
> especially since we have alternate accounts that act as db owners and
> administrators. is the built-in account somehing we should just put on
the
> side and leave alone? Thanks!
Subscribe to:
Posts (Atom)