Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@.Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:
SET @.SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @.Code + ''')'
EXEC sp_executesql @.SQL
(CAT_ASA is the linked-server's name)
Then, i would use @.@.ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!I never worked with an ASA6 db but how about using four-part naming instead of OpenQuery? In a normal query, you can use variables in your where clauses. So, if the column type of CODE is not something out of the ordinary and recognized by SQL Server, everything should run fine. There could be interface problems but usually with a query as simple as yours, it should work.
This is a simple solution that doesn't really answer your question. Consider it as a possible workaround.
Good luck,
Skip.|||Thanks for your answer, Skip. I also tried using a four part name, but SQL server gave me a message saying that the ODBC Interface doesnt support four-part names. I tried with a 3 part name (linkedservername.database.table), but it still doesnt works. The error was diferent (so, I supose that the names with this ODBC interface must have three parts). I read in another thread that the only way to make a query to a linked server was using OPENQUERY or OPENROWSET. Im not really sure about that, but i tried many ways using 3 or 4 part names and it never worked.|||In addition i tried something like this:
SELECT * FROM OPENQUERY(CAT_ASA,'SELECT code FROM COUNTRIES') WHERE code=@.Code
Here i dont have to use an EXEC, so it works in a function, and i can filter the results with a condition. The problem is (sorry for not saying it before) that i wrote a very simple example, but the real query has 4 nested joins, and (because of performance) i should make it in only 1 query.
Thats why I cannot make something like this:
SELECT * FROM OPENQUERY(CAT_ASA,'SELECT * FROM Table1')
INNER JOIN (OPENQUERY(CAT_ASA,'SELECT * FROM Table2') ON ... )
Because i would make 4 OPENQUERY, which results in a very poor performance (10/14 secs per query!!!).
Another solution would be making the join inside the OPENQUERY, and filtering the results in SQL Server, like this:
SELECT * FROM OPENQUERY(CAT_ASA,'SELECT * FROM Table1 inner join (Table2 inner join (Table3 inner join Table 4 on...) on...)....
WHERE ...
Obviously this is worse than using 4 openquerys, because four joins without conditions (except on PKs) would return a very big quantity of records (in the order of 6.000.000.000!!!!!) and, after the conditions, that number would be reduced to 0 or 1 record (remember, i must check only the EXISTENCE of a record). That would be very inefficient.
So, I think in two ways for solving this:
1) Using the right part names (3 or 4), and making a normal query.
2) Find another method to execute a string query (or, more precisely, to determine if a string query has results), that can be used inside a function.
Thanks
Showing posts with label function. Show all posts
Showing posts with label function. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
How to determine ROWCOUNT without executing the select statement
I'm selecting data from a large table in a paged manner by using the
ROW_NUMBER ranking function in a function like the one shown below... I
want the function to also return the total number of rows in the dataset
AFTER the primary select is issued but before the paged data is selected
out. In other words, if there are 10,000 rows, and 3500 of them get
selected by the where clause, but the paging only returns 101 .. 200, I
want the total rows output to be set to 3500.
The way this is written currently, I use @.@.ROWCOUNT, but only get the
page size (eg 100). Is there an easy way to get the primary data set
size without resorting to memory or temporary tables, and without
executing the main query twice'
-mdb
#############################
PROCEDURE [dbo].[GetTablePagedAndSorted]
(
@.tableName nvarchar(100),
@.columnList varchar(2000),
@.sortExpression nvarchar(100),
@.whereClause varchar(2000),
@.startRowIndex int,
@.maximumRows int,
@.totalRows int OUTPUT
) AS
IF (LEN(@.whereClause) = 0) SET @.whereClause = '1=1'
-- Issue query
DECLARE @.sql nvarchar(4000)
SET @.sql = 'SELECT ' + @.columnList + ',RowRank '
SET @.sql = @.sql + ' FROM (
SELECT ' + @.columnList + ', ROW_NUMBER() OVER (ORDER BY ' +
@.sortExpression + ') AS RowRank
FROM ' + @.tableName + '
WHERE (' + @.whereClause + ')
) AS TableWithRowNumbers '
IF (@.maximumRows > 0)
BEGIN
SET @.sql = @.sql + '
WHERE RowRank >= ' + CONVERT(nvarchar(10), @.startRowIndex) +
'
AND RowRank < (' + CONVERT(nvarchar(10), @.startRowIndex) +
' + ' + CONVERT(nvarchar(10), @.maximumRows) + ')'
END
-- Execute the SQL query
PRINT @.sql
EXEC sp_executesql @.sql
SET @.totalRows = @.@.ROWCOUNT
######################################You could put the result into a temp. table. Or you could create a SELECT
COUNT(*) to get the count before you do the actual SELECT. I guess the
question is how important is it to know the intermediate result set size -
is it worth the extra inefficiency?
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
> I'm selecting data from a large table in a paged manner by using the
> ROW_NUMBER ranking function in a function like the one shown below... I
> want the function to also return the total number of rows in the dataset
> AFTER the primary select is issued but before the paged data is selected
> out. In other words, if there are 10,000 rows, and 3500 of them get
> selected by the where clause, but the paging only returns 101 .. 200, I
> want the total rows output to be set to 3500.
> The way this is written currently, I use @.@.ROWCOUNT, but only get the
> page size (eg 100). Is there an easy way to get the primary data set
> size without resorting to memory or temporary tables, and without
> executing the main query twice'
> -mdb
> #############################
> PROCEDURE [dbo].[GetTablePagedAndSorted]
> (
> @.tableName nvarchar(100),
> @.columnList varchar(2000),
> @.sortExpression nvarchar(100),
> @.whereClause varchar(2000),
> @.startRowIndex int,
> @.maximumRows int,
> @.totalRows int OUTPUT
> ) AS
> IF (LEN(@.whereClause) = 0) SET @.whereClause = '1=1'
> -- Issue query
> DECLARE @.sql nvarchar(4000)
> SET @.sql = 'SELECT ' + @.columnList + ',RowRank '
> SET @.sql = @.sql + ' FROM (
> SELECT ' + @.columnList + ', ROW_NUMBER() OVER (ORDER BY ' +
> @.sortExpression + ') AS RowRank
> FROM ' + @.tableName + '
> WHERE (' + @.whereClause + ')
> ) AS TableWithRowNumbers '
> IF (@.maximumRows > 0)
> BEGIN
> SET @.sql = @.sql + '
> WHERE RowRank >= ' + CONVERT(nvarchar(10), @.startRowIndex) +
> '
> AND RowRank < (' + CONVERT(nvarchar(10), @.startRowIndex) +
> ' + ' + CONVERT(nvarchar(10), @.maximumRows) + ')'
> END
> -- Execute the SQL query
> PRINT @.sql
> EXEC sp_executesql @.sql
> SET @.totalRows = @.@.ROWCOUNT
> ######################################
>|||"Mike C#" <xyz@.xyz.com> wrote in
news:OG9RqcZIIHA.4584@.TK2MSFTNGP03.phx.gbl:
> You could put the result into a temp. table. Or you could create a
> SELECT COUNT(*) to get the count before you do the actual SELECT. I
> guess the question is how important is it to know the intermediate
> result set size - is it worth the extra inefficiency?
> "Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
> message news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
>> I'm selecting data from a large table in a paged manner by using the
>> ROW_NUMBER ranking function in a function like the one shown below...
>> I want the function to also return the total number of rows in the
>> dataset AFTER the primary select is issued but before the paged data
>> is selected out. In other words, if there are 10,000 rows, and 3500
>> of them get selected by the where clause, but the paging only returns
>> 101 .. 200, I want the total rows output to be set to 3500.
Thanks, but as I said I do not want to use temporary tables, as this will
absolutely swamp my tempdb due to the potential size of the query results.
I considered your other suggestion previously, but then the question is how
do I get the results of an EXEC into a variable? Keep in mind that I have
to build the sql dynamically, and thus require sp_executesql. The
following syntax doesn't work:
SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
If I can find the solution to how to set a variable based on a dynamic sql
statement then I will be set.
-mdb|||Michael Bray <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16:
> Thanks, but as I said I do not want to use temporary tables, as this
> will absolutely swamp my tempdb due to the potential size of the query
> results. I considered your other suggestion previously, but then the
> question is how do I get the results of an EXEC into a variable? Keep
> in mind that I have to build the sql dynamically, and thus require
> sp_executesql. The following syntax doesn't work:
> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
> If I can find the solution to how to set a variable based on a dynamic
> sql statement then I will be set.
>
OK I found the solution - sp_executesql can actually accept input and
output parameters!!
See the wonderful article at:
http://www.sommarskog.se/dynamic_sql.html
-mdb|||Oops, I see you already found that
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E26438875B7mbrayctiusacom@.207.46.248.16...
> Michael Bray <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
> news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16:
>> Thanks, but as I said I do not want to use temporary tables, as this
>> will absolutely swamp my tempdb due to the potential size of the query
>> results. I considered your other suggestion previously, but then the
>> question is how do I get the results of an EXEC into a variable? Keep
>> in mind that I have to build the sql dynamically, and thus require
>> sp_executesql. The following syntax doesn't work:
>> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
>> If I can find the solution to how to set a variable based on a dynamic
>> sql statement then I will be set.
> OK I found the solution - sp_executesql can actually accept input and
> output parameters!!
> See the wonderful article at:
> http://www.sommarskog.se/dynamic_sql.html
> -mdb|||sp_executesql does take output params
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16...
> "Mike C#" <xyz@.xyz.com> wrote in
> news:OG9RqcZIIHA.4584@.TK2MSFTNGP03.phx.gbl:
>> You could put the result into a temp. table. Or you could create a
>> SELECT COUNT(*) to get the count before you do the actual SELECT. I
>> guess the question is how important is it to know the intermediate
>> result set size - is it worth the extra inefficiency?
>> "Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
>> message news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
>> I'm selecting data from a large table in a paged manner by using the
>> ROW_NUMBER ranking function in a function like the one shown below...
>> I want the function to also return the total number of rows in the
>> dataset AFTER the primary select is issued but before the paged data
>> is selected out. In other words, if there are 10,000 rows, and 3500
>> of them get selected by the where clause, but the paging only returns
>> 101 .. 200, I want the total rows output to be set to 3500.
> Thanks, but as I said I do not want to use temporary tables, as this will
> absolutely swamp my tempdb due to the potential size of the query results.
> I considered your other suggestion previously, but then the question is
> how
> do I get the results of an EXEC into a variable? Keep in mind that I have
> to build the sql dynamically, and thus require sp_executesql. The
> following syntax doesn't work:
> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
> If I can find the solution to how to set a variable based on a dynamic sql
> statement then I will be set.
> -mdb
ROW_NUMBER ranking function in a function like the one shown below... I
want the function to also return the total number of rows in the dataset
AFTER the primary select is issued but before the paged data is selected
out. In other words, if there are 10,000 rows, and 3500 of them get
selected by the where clause, but the paging only returns 101 .. 200, I
want the total rows output to be set to 3500.
The way this is written currently, I use @.@.ROWCOUNT, but only get the
page size (eg 100). Is there an easy way to get the primary data set
size without resorting to memory or temporary tables, and without
executing the main query twice'
-mdb
#############################
PROCEDURE [dbo].[GetTablePagedAndSorted]
(
@.tableName nvarchar(100),
@.columnList varchar(2000),
@.sortExpression nvarchar(100),
@.whereClause varchar(2000),
@.startRowIndex int,
@.maximumRows int,
@.totalRows int OUTPUT
) AS
IF (LEN(@.whereClause) = 0) SET @.whereClause = '1=1'
-- Issue query
DECLARE @.sql nvarchar(4000)
SET @.sql = 'SELECT ' + @.columnList + ',RowRank '
SET @.sql = @.sql + ' FROM (
SELECT ' + @.columnList + ', ROW_NUMBER() OVER (ORDER BY ' +
@.sortExpression + ') AS RowRank
FROM ' + @.tableName + '
WHERE (' + @.whereClause + ')
) AS TableWithRowNumbers '
IF (@.maximumRows > 0)
BEGIN
SET @.sql = @.sql + '
WHERE RowRank >= ' + CONVERT(nvarchar(10), @.startRowIndex) +
'
AND RowRank < (' + CONVERT(nvarchar(10), @.startRowIndex) +
' + ' + CONVERT(nvarchar(10), @.maximumRows) + ')'
END
-- Execute the SQL query
PRINT @.sql
EXEC sp_executesql @.sql
SET @.totalRows = @.@.ROWCOUNT
######################################You could put the result into a temp. table. Or you could create a SELECT
COUNT(*) to get the count before you do the actual SELECT. I guess the
question is how important is it to know the intermediate result set size -
is it worth the extra inefficiency?
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
> I'm selecting data from a large table in a paged manner by using the
> ROW_NUMBER ranking function in a function like the one shown below... I
> want the function to also return the total number of rows in the dataset
> AFTER the primary select is issued but before the paged data is selected
> out. In other words, if there are 10,000 rows, and 3500 of them get
> selected by the where clause, but the paging only returns 101 .. 200, I
> want the total rows output to be set to 3500.
> The way this is written currently, I use @.@.ROWCOUNT, but only get the
> page size (eg 100). Is there an easy way to get the primary data set
> size without resorting to memory or temporary tables, and without
> executing the main query twice'
> -mdb
> #############################
> PROCEDURE [dbo].[GetTablePagedAndSorted]
> (
> @.tableName nvarchar(100),
> @.columnList varchar(2000),
> @.sortExpression nvarchar(100),
> @.whereClause varchar(2000),
> @.startRowIndex int,
> @.maximumRows int,
> @.totalRows int OUTPUT
> ) AS
> IF (LEN(@.whereClause) = 0) SET @.whereClause = '1=1'
> -- Issue query
> DECLARE @.sql nvarchar(4000)
> SET @.sql = 'SELECT ' + @.columnList + ',RowRank '
> SET @.sql = @.sql + ' FROM (
> SELECT ' + @.columnList + ', ROW_NUMBER() OVER (ORDER BY ' +
> @.sortExpression + ') AS RowRank
> FROM ' + @.tableName + '
> WHERE (' + @.whereClause + ')
> ) AS TableWithRowNumbers '
> IF (@.maximumRows > 0)
> BEGIN
> SET @.sql = @.sql + '
> WHERE RowRank >= ' + CONVERT(nvarchar(10), @.startRowIndex) +
> '
> AND RowRank < (' + CONVERT(nvarchar(10), @.startRowIndex) +
> ' + ' + CONVERT(nvarchar(10), @.maximumRows) + ')'
> END
> -- Execute the SQL query
> PRINT @.sql
> EXEC sp_executesql @.sql
> SET @.totalRows = @.@.ROWCOUNT
> ######################################
>|||"Mike C#" <xyz@.xyz.com> wrote in
news:OG9RqcZIIHA.4584@.TK2MSFTNGP03.phx.gbl:
> You could put the result into a temp. table. Or you could create a
> SELECT COUNT(*) to get the count before you do the actual SELECT. I
> guess the question is how important is it to know the intermediate
> result set size - is it worth the extra inefficiency?
> "Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
> message news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
>> I'm selecting data from a large table in a paged manner by using the
>> ROW_NUMBER ranking function in a function like the one shown below...
>> I want the function to also return the total number of rows in the
>> dataset AFTER the primary select is issued but before the paged data
>> is selected out. In other words, if there are 10,000 rows, and 3500
>> of them get selected by the where clause, but the paging only returns
>> 101 .. 200, I want the total rows output to be set to 3500.
Thanks, but as I said I do not want to use temporary tables, as this will
absolutely swamp my tempdb due to the potential size of the query results.
I considered your other suggestion previously, but then the question is how
do I get the results of an EXEC into a variable? Keep in mind that I have
to build the sql dynamically, and thus require sp_executesql. The
following syntax doesn't work:
SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
If I can find the solution to how to set a variable based on a dynamic sql
statement then I will be set.
-mdb|||Michael Bray <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16:
> Thanks, but as I said I do not want to use temporary tables, as this
> will absolutely swamp my tempdb due to the potential size of the query
> results. I considered your other suggestion previously, but then the
> question is how do I get the results of an EXEC into a variable? Keep
> in mind that I have to build the sql dynamically, and thus require
> sp_executesql. The following syntax doesn't work:
> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
> If I can find the solution to how to set a variable based on a dynamic
> sql statement then I will be set.
>
OK I found the solution - sp_executesql can actually accept input and
output parameters!!
See the wonderful article at:
http://www.sommarskog.se/dynamic_sql.html
-mdb|||Oops, I see you already found that
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E26438875B7mbrayctiusacom@.207.46.248.16...
> Michael Bray <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
> news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16:
>> Thanks, but as I said I do not want to use temporary tables, as this
>> will absolutely swamp my tempdb due to the potential size of the query
>> results. I considered your other suggestion previously, but then the
>> question is how do I get the results of an EXEC into a variable? Keep
>> in mind that I have to build the sql dynamically, and thus require
>> sp_executesql. The following syntax doesn't work:
>> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
>> If I can find the solution to how to set a variable based on a dynamic
>> sql statement then I will be set.
> OK I found the solution - sp_executesql can actually accept input and
> output parameters!!
> See the wonderful article at:
> http://www.sommarskog.se/dynamic_sql.html
> -mdb|||sp_executesql does take output params
"Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in message
news:Xns99E2544576DFAmbrayctiusacom@.207.46.248.16...
> "Mike C#" <xyz@.xyz.com> wrote in
> news:OG9RqcZIIHA.4584@.TK2MSFTNGP03.phx.gbl:
>> You could put the result into a temp. table. Or you could create a
>> SELECT COUNT(*) to get the count before you do the actual SELECT. I
>> guess the question is how important is it to know the intermediate
>> result set size - is it worth the extra inefficiency?
>> "Michael Bray" <mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote in
>> message news:Xns99E1B6650917Embrayctiusacom@.207.46.248.16...
>> I'm selecting data from a large table in a paged manner by using the
>> ROW_NUMBER ranking function in a function like the one shown below...
>> I want the function to also return the total number of rows in the
>> dataset AFTER the primary select is issued but before the paged data
>> is selected out. In other words, if there are 10,000 rows, and 3500
>> of them get selected by the where clause, but the paging only returns
>> 101 .. 200, I want the total rows output to be set to 3500.
> Thanks, but as I said I do not want to use temporary tables, as this will
> absolutely swamp my tempdb due to the potential size of the query results.
> I considered your other suggestion previously, but then the question is
> how
> do I get the results of an EXEC into a variable? Keep in mind that I have
> to build the sql dynamically, and thus require sp_executesql. The
> following syntax doesn't work:
> SET @.totalRows = (EXEC sp_executesql @.mySqlStatement) ' doesn't parse
> If I can find the solution to how to set a variable based on a dynamic sql
> statement then I will be set.
> -mdb
How to determine min. datetime in mdx
Say I have 2 columns, id and date as follow:
id date
1 11-Jan-2007
1 18-Jun-2007
2 21-Mar-2007
2 19-Sep-2007
How to write a mdx function to filter out on each id with the min date as follow:
id date
1 11-Jan-2007
2 21-Mar-2007
I would use ID as an dimension and this table as fact table - the date for a measure with the min aggregate function and then "select Dimension.ID on rows, measures.mindate on columns from cube"
HANNES
Friday, March 23, 2012
How to Determine if a user is a member of the System Admin role?
Is there a script/function that can be used to determine if a user (granted
login/access via NT Group Membership) is a member of the System Administrato
r
group?
We had an issue where a user was a member of multiple NT Global Groups, one
of which was a member of (had) the System Admin role. Our application check
s
to see if the NT Group for our Application has DBO rights, but this returned
false ... yet the user would (by default) create objects (views/tables) in
dbo. We finally traced this down via Enterprise Mgr, Security, Server Roles
and dbl-clicked "System Administrators" and found that there were unexpected
groups there, and our user(s) were in one or more of these groups. How can w
e
determine this via code/script and then "turn it off" for our database (of
course it is possible that a user that is a member of another group MAY need
SA rights in another database)?
Thank you,
Brad
--
Brad Ashforth> Is there a script/function that can be used to determine if a user
> (granted
> login/access via NT Group Membership) is a member of the System
> Administrator
> group?
SELECT IS_SRVROLEMEMBER('sysadmin')
> How can we
> determine this via code/script and then "turn it off" for our database (of
> course it is possible that a user that is a member of another group MAY
> need
> SA rights in another database)?
In SQL 2000, there are only 2 cases where objects will be created in the dbo
schema by default: 1) user is the database owner and 2) user is a
sysadmin role member. The query 'SELECT USER' will return 'dbo' in both
cases.
I'm not sure I understand what you mean by 'turn it off'. Do you mean that
you want the default schema to be other than 'dbo' for the dbo user? Have
you considered schema-qualifying object names so that the default schema
isn't relevant?
Hope this helps.
Dan Guzman
SQL Server MVP
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:5BEE8141-69CC-415A-A57E-48C875CB31AE@.microsoft.com...
> Is there a script/function that can be used to determine if a user
> (granted
> login/access via NT Group Membership) is a member of the System
> Administrator
> group?
> We had an issue where a user was a member of multiple NT Global Groups,
> one
> of which was a member of (had) the System Admin role. Our application
> checks
> to see if the NT Group for our Application has DBO rights, but this
> returned
> false ... yet the user would (by default) create objects (views/tables) in
> dbo. We finally traced this down via Enterprise Mgr, Security, Server
> Roles
> and dbl-clicked "System Administrators" and found that there were
> unexpected
> groups there, and our user(s) were in one or more of these groups. How can
> we
> determine this via code/script and then "turn it off" for our database (of
> course it is possible that a user that is a member of another group MAY
> need
> SA rights in another database)?
> Thank you,
> Brad
> --
> Brad Ashforth|||Hello Brad,
As for a windows user account(or group) or a sqlserver account, before we
check if it is of sysadmin role (in the server instance), we should first
check if it's a server login(principal) on that server instance. For this,
we can use some T-SQL query to lookup all the principals of sysadmin role
in the master db. It'll be a bit different for SQL 2005 and SQL 2000:
============2005===========
select p1.Name as Role_name, p2.Name as Member_name from
sys.server_role_members r1 inner join sys.server_principals p1
on r1.Role_principal_id = p1.Principal_id
inner join sys.server_principals p2
on r1.Member_principal_id = p2.Principal_id
=========================
As you can see, we need to query multiple catalog views in sys schema.
While in SQL server 2000, we can diretly query the "syslogins" table in
master db, and this table contains a "sysadmin" column indicate whether the
certain principal is of sysadmin role.
==============2000==================
select * from syslogins
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Brad,
How are you doing on this issue or does our suggestion help you some? If
there is still anything we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Brad,
How are you doing on this issue or does our suggestion help you some? If
there is still anything we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
login/access via NT Group Membership) is a member of the System Administrato
r
group?
We had an issue where a user was a member of multiple NT Global Groups, one
of which was a member of (had) the System Admin role. Our application check
s
to see if the NT Group for our Application has DBO rights, but this returned
false ... yet the user would (by default) create objects (views/tables) in
dbo. We finally traced this down via Enterprise Mgr, Security, Server Roles
and dbl-clicked "System Administrators" and found that there were unexpected
groups there, and our user(s) were in one or more of these groups. How can w
e
determine this via code/script and then "turn it off" for our database (of
course it is possible that a user that is a member of another group MAY need
SA rights in another database)?
Thank you,
Brad
--
Brad Ashforth> Is there a script/function that can be used to determine if a user
> (granted
> login/access via NT Group Membership) is a member of the System
> Administrator
> group?
SELECT IS_SRVROLEMEMBER('sysadmin')
> How can we
> determine this via code/script and then "turn it off" for our database (of
> course it is possible that a user that is a member of another group MAY
> need
> SA rights in another database)?
In SQL 2000, there are only 2 cases where objects will be created in the dbo
schema by default: 1) user is the database owner and 2) user is a
sysadmin role member. The query 'SELECT USER' will return 'dbo' in both
cases.
I'm not sure I understand what you mean by 'turn it off'. Do you mean that
you want the default schema to be other than 'dbo' for the dbo user? Have
you considered schema-qualifying object names so that the default schema
isn't relevant?
Hope this helps.
Dan Guzman
SQL Server MVP
"Brad Ashforth" <banospam@.nospam.nospam> wrote in message
news:5BEE8141-69CC-415A-A57E-48C875CB31AE@.microsoft.com...
> Is there a script/function that can be used to determine if a user
> (granted
> login/access via NT Group Membership) is a member of the System
> Administrator
> group?
> We had an issue where a user was a member of multiple NT Global Groups,
> one
> of which was a member of (had) the System Admin role. Our application
> checks
> to see if the NT Group for our Application has DBO rights, but this
> returned
> false ... yet the user would (by default) create objects (views/tables) in
> dbo. We finally traced this down via Enterprise Mgr, Security, Server
> Roles
> and dbl-clicked "System Administrators" and found that there were
> unexpected
> groups there, and our user(s) were in one or more of these groups. How can
> we
> determine this via code/script and then "turn it off" for our database (of
> course it is possible that a user that is a member of another group MAY
> need
> SA rights in another database)?
> Thank you,
> Brad
> --
> Brad Ashforth|||Hello Brad,
As for a windows user account(or group) or a sqlserver account, before we
check if it is of sysadmin role (in the server instance), we should first
check if it's a server login(principal) on that server instance. For this,
we can use some T-SQL query to lookup all the principals of sysadmin role
in the master db. It'll be a bit different for SQL 2005 and SQL 2000:
============2005===========
select p1.Name as Role_name, p2.Name as Member_name from
sys.server_role_members r1 inner join sys.server_principals p1
on r1.Role_principal_id = p1.Principal_id
inner join sys.server_principals p2
on r1.Member_principal_id = p2.Principal_id
=========================
As you can see, we need to query multiple catalog views in sys schema.
While in SQL server 2000, we can diretly query the "syslogins" table in
master db, and this table contains a "sysadmin" column indicate whether the
certain principal is of sysadmin role.
==============2000==================
select * from syslogins
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Brad,
How are you doing on this issue or does our suggestion help you some? If
there is still anything we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Brad,
How are you doing on this issue or does our suggestion help you some? If
there is still anything we can help, please feel free to post here.
Regards,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
How to determine default values of a field during runtime
Hi, i am using the function below (visual basic 6) to determine several
properties of
each field in a recordset (SQL server) and all is working well. My
problem is that I
dont know how to find the default value of a field from the table. Does
anyone have
any code suggestions that would get me the default value
For Each F In rstProgressData.Fields
If F.Type <> adChapter Then
If F.Name <> "upsize_ts" Then
rstDest.AddNew
rstDest!Progress = F.Name
Select Case F.Type
Case adChar, adVarWChar, adVarChar
rstDest!ProgressFieldType = "String"
rstDest!ProgressFieldSize = F.DefinedSize
Case adBoolean
rstDest!ProgressFieldType = "Boolean"
Case adSmallInt, adUnsignedTinyInt, adInteger
rstDest!ProgressFieldType = "Integer"
Case adDecimal, adNumeric
rstDest!ProgressFieldType = "Decimal"
Case adDBTimeStamp
rstDest!ProgressFieldType = "DateTime"
Case 203
rstDest!ProgressFieldType = "Memo"
End Select
If (F.Attributes And adFldIsNullable) = adFldIsNullable Then
rstDest!ProgressFieldNullable = True
End If
rstDest.Update
End If
End If
Next FThats hard to see where your recordset is based on if you don=B4t send
the query with you, but otherwise the information can be queried
through the INFORMATION_SCHEMA Views:
Select Column_default from INFORMATION_SCHEMA.COLUMNS
Where table_name =3D '<SomeTable>'
HTH, Jens Suessmeyer.|||The code has to be generic so that it can deal with any sql statement.
As above for any query I can get the field size, type, name & if its
nullable, I just need to find out what syntax to use to get the default
value e.g F.Type gives me type. F has been defined as an ADODB.Field.sql
properties of
each field in a recordset (SQL server) and all is working well. My
problem is that I
dont know how to find the default value of a field from the table. Does
anyone have
any code suggestions that would get me the default value
For Each F In rstProgressData.Fields
If F.Type <> adChapter Then
If F.Name <> "upsize_ts" Then
rstDest.AddNew
rstDest!Progress = F.Name
Select Case F.Type
Case adChar, adVarWChar, adVarChar
rstDest!ProgressFieldType = "String"
rstDest!ProgressFieldSize = F.DefinedSize
Case adBoolean
rstDest!ProgressFieldType = "Boolean"
Case adSmallInt, adUnsignedTinyInt, adInteger
rstDest!ProgressFieldType = "Integer"
Case adDecimal, adNumeric
rstDest!ProgressFieldType = "Decimal"
Case adDBTimeStamp
rstDest!ProgressFieldType = "DateTime"
Case 203
rstDest!ProgressFieldType = "Memo"
End Select
If (F.Attributes And adFldIsNullable) = adFldIsNullable Then
rstDest!ProgressFieldNullable = True
End If
rstDest.Update
End If
End If
Next FThats hard to see where your recordset is based on if you don=B4t send
the query with you, but otherwise the information can be queried
through the INFORMATION_SCHEMA Views:
Select Column_default from INFORMATION_SCHEMA.COLUMNS
Where table_name =3D '<SomeTable>'
HTH, Jens Suessmeyer.|||The code has to be generic so that it can deal with any sql statement.
As above for any query I can get the field size, type, name & if its
nullable, I just need to find out what syntax to use to get the default
value e.g F.Type gives me type. F has been defined as an ADODB.Field.sql
Subscribe to:
Posts (Atom)