Query/test for db_owner role?

Is there a SQL query that can explicitly return the roles that the current
SQL user has been explicitly assigned? I'm trying to find out if the user
has been given the db_owner role (versus it being implied by the user being
an administrator).
Thanks!What version of SQL server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gbl...
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user being
> an administrator).
> Thanks!
>|||Would this work for you?
sp_helprolemember 'db_owner'
Ben Nevarez, MCDBA, OCP
Database Administrator
"R Baker" wrote:
> Is there a SQL query that can explicitly return the roles that the current
> SQL user has been explicitly assigned? I'm trying to find out if the user
> has been given the db_owner role (versus it being implied by the user being
> an administrator).
> Thanks!
>
>|||Yes, that might. I'll have to figure out how to process the result set using
the MFC CDatabase classes, but it seems doable.
Thanks.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...
> Would this work for you?
> sp_helprolemember 'db_owner'
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "R Baker" wrote:
>> Is there a SQL query that can explicitly return the roles that the
>> current
>> SQL user has been explicitly assigned? I'm trying to find out if the user
>> has been given the db_owner role (versus it being implied by the user
>> being
>> an administrator).
>> Thanks!
>>|||SQL 2000 or SQL 2005 (mainly SQL 2000 for now).
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:erz8UAWPGHA.3576@.TK2MSFTNGP15.phx.gbl...
> What version of SQL server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "R Baker" <nospamx@.nospamx.com> wrote in message
> news:%239MeR9VPGHA.3944@.tk2msftngp13.phx.gbl...
>> Is there a SQL query that can explicitly return the roles that the
>> current SQL user has been explicitly assigned? I'm trying to find out if
>> the user has been given the db_owner role (versus it being implied by the
>> user being an administrator).
>> Thanks!|||It is only one resultset, so you would process it like any resultset (as if you were sending a
SELECT from your app, or executing your own stored procedure which returns a result set).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"R Baker" <nospamx@.nospamx.com> wrote in message news:eTB3u8WPGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Yes, that might. I'll have to figure out how to process the result set using the MFC CDatabase
> classes, but it seems doable.
> Thanks.
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:33F82F3D-CDA3-4040-B160-22E6D96B3D55@.microsoft.com...
>> Would this work for you?
>> sp_helprolemember 'db_owner'
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "R Baker" wrote:
>> Is there a SQL query that can explicitly return the roles that the current
>> SQL user has been explicitly assigned? I'm trying to find out if the user
>> has been given the db_owner role (versus it being implied by the user being
>> an administrator).
>> Thanks!
>>
>

query to view current executing jobs

... I know i have asked this before and the response i got is run
sp_help_job.. Please bear with me as Im not a SQL guru . I would like to run
a script in QA and the output should give me the list of jobs that are
currently running. I have around 100 SQL Agent jobs on a server and instead
of refreshing my screen in EM to see the status of running, I want to see
those jobs only from within QA.
Can someone provide that query for me ? Would be highly appreciated.The procedure call is: exec msdb..sp_help_job
For each job, check the coding of current_execution_status:
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uO370WdGFHA.2784@.TK2MSFTNGP10.phx.gbl...
> .. I know i have asked this before and the response i got is run
> sp_help_job.. Please bear with me as Im not a SQL guru . I would like to
run
> a script in QA and the output should give me the list of jobs that are
> currently running. I have around 100 SQL Agent jobs on a server and
instead
> of refreshing my screen in EM to see the status of running, I want to see
> those jobs only from within QA.
> Can someone provide that query for me ? Would be highly appreciated.
>|||You could try the following query, instead of returning all the jobs, it
just returns current active jobs.
--find Jobs that are currently running:
exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
"JohnnyAppleseed" <someone@.microsoft.com> wrote in message
news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
> The procedure call is: exec msdb..sp_help_job
> For each job, check the coding of current_execution_status:
> 0 Returns only those jobs that are not idle or suspended.
> 1 Executing.
> 2 Waiting for thread.
> 3 Between retries.
> 4 Idle.
> 5 Suspended.
> 7 Performing completion actions.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uO370WdGFHA.2784@.TK2MSFTNGP10.phx.gbl...
> run
> instead
see
>|||Thanks Britney
Do you know what I can use to find just failed jobs ?
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ObkP23eGFHA.584@.TK2MSFTNGP14.phx.gbl...
> You could try the following query, instead of returning all the jobs, it
> just returns current active jobs.
>
> --find Jobs that are currently running:
>
> exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
>
>
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
to
> see
>|||This is the same too right
exec msdb..sp_help_job @.enabled=1 , @.execution_status = 1
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ObkP23eGFHA.584@.TK2MSFTNGP14.phx.gbl...
> You could try the following query, instead of returning all the jobs, it
> just returns current active jobs.
>
> --find Jobs that are currently running:
>
> exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
>
>
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
to
> see
>

query to list db and recovery model

I want a query that would list all databases on a server along with its
current recovery model setting such as
DB1 Simple
DB2 FullHassan wrote:
> I want a query that would list all databases on a server along with
> its current recovery model setting such as
> DB1 Simple
> DB2 Full
Exec sp_MSForEachDB 'SELECT ''?'' as "Database",
DATABASEPROPERTYEX(''?'', ''Recovery'') as "Recovery"'
or
Create Table #DBRecovery (
db_name nvarchar(128),
recovery nvarchar(30) )
Insert Into #DBRecovery
Exec sp_MSForEachDB 'SELECT ''?'', CAST(DATABASEPROPERTYEX(''?'',
''Recovery'') as nvarchar(30))'
select * from #DBRecovery
drop table #DBRecovery
David Gugick
Imceda Software
www.imceda.com|||Hi Hassan,
If you want it just as an information, you can use sp_helpdb to get the list
of all the database and their recovery properties, assuming you have access
to all the databases in the server.
--
Thanks
Yogish|||No need to get complicated...
select [name],databasepropertyex([name],'Recovery') as [Recovery]
from master.dbo.sysdatabases
order by [name]
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eb399oELFHA.1948@.TK2MSFTNGP14.phx.gbl...
>I want a query that would list all databases on a server along with its
> current recovery model setting such as
> DB1 Simple
> DB2 Full
>|||"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message news:<e8VIdEOLFHA.568@.TK2MSFTNGP09.phx.gbl>...
> No need to get complicated...
> select [name],databasepropertyex([name],'Recovery') as [Recovery]
> from master.dbo.sysdatabases
> order by [name]
> --
> HTH
>
Jasper
Excellent simple answer. My question is: why does this work? I didn't
realise that databasepropertyex could use the fieldname in that way.
Is this a general featrure of T-SQL to recognize a field reference
before interpreting as a string?
Steve|||All functions work this way. The function takes a string as parameter. That string can be a
constant, as in 'pubs', or a column name which derives the value for each row for the specified
column used in a SELECT statement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"steve" <stevester@.freeuk.com> wrote in message
news:73627c14.0504150739.64492ae8@.posting.google.com...
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:<e8VIdEOLFHA.568@.TK2MSFTNGP09.phx.gbl>...
>> No need to get complicated...
>> select [name],databasepropertyex([name],'Recovery') as [Recovery]
>> from master.dbo.sysdatabases
>> order by [name]
>> --
>> HTH
> Jasper
> Excellent simple answer. My question is: why does this work? I didn't
> realise that databasepropertyex could use the fieldname in that way.
> Is this a general featrure of T-SQL to recognize a field reference
> before interpreting as a string?
> Steve

query to identify all fields in a table

Newby question. We have a new vendor who will be needing to do data
convertion from our current SQL app into his new application, as part of the
implimentation. I need to identify the complete file structure in order to
get a quote for that part of the project.
I know how to export the list of the tables, but is there a similar way to
export the list of fields. OR..any query possibities that could be run per
table or mutiple tables that would list just the fields - no data?
Any help or ideas would be greatly appreciated - thanks in advance for your
time and help!
Cindy B
Hi,
im sure there is a way to query the system tables in order get the lis of
fields, but don't ask me how ;)
however, you may want to use the following excel vba macro:
Sub GetFields()
Dim A As Long
Dim TableName As String
Dim RS As ADODB.Recordset
Dim Conn As New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Password=[PASSWORD];Persist
Security Info=True;User ID=[USERNAME];Initial Catalog=[DATABASE];Data
Source=[SERVERNAME]"
Conn.Open
TableName = InputBox("Enter table name: ")
If TableName = "" Then Exit Sub
Set RS = Conn.Execute("SELECT TOP 1 * FROM " & TableName)
Range("A1:A1000").Clear
For A = 1 To RS.Fields.Count
Range("A" & A).Value = RS.Fields(A - 1).Name
Next
RS.Close
Conn.Close
End Sub
just provide the necessary connection information in the connection string
(without the [ ] ), add a reference to the latest "microsoft activex data
objects" and run the macro. it will list all fields of a given table in the
current excel worksheet
"Cindy B" wrote:

> Newby question. We have a new vendor who will be needing to do data
> convertion from our current SQL app into his new application, as part of the
> implimentation. I need to identify the complete file structure in order to
> get a quote for that part of the project.
> I know how to export the list of the tables, but is there a similar way to
> export the list of fields. OR..any query possibities that could be run per
> table or mutiple tables that would list just the fields - no data?
> Any help or ideas would be greatly appreciated - thanks in advance for your
> time and help!
> --
> Cindy B
|||One way against the ANSI INFORMATIO_SCHEMA views
select c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.NUMERIC_P RECISION from
INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY c.TABLE_NAME,c.ORDINAL_POSITION
http://sqlservercode.blogspot.com/

query to identify all fields in a table

Newby question. We have a new vendor who will be needing to do data
convertion from our current SQL app into his new application, as part of the
implimentation. I need to identify the complete file structure in order to
get a quote for that part of the project.
I know how to export the list of the tables, but is there a similar way to
export the list of fields. OR..any query possibities that could be run per
table or mutiple tables that would list just the fields - no data?
Any help or ideas would be greatly appreciated - thanks in advance for your
time and help!
Cindy BHi,
im sure there is a way to query the system tables in order get the lis of
fields, but don't ask me how ;)
however, you may want to use the following excel vba macro:
Sub GetFields()
Dim A As Long
Dim TableName As String
Dim RS As ADODB.Recordset
Dim Conn As New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Password=[PASSWORD];Persist
Security Info=True;User ID=[USERNAME];Initial Catalog=[DATABASE];Dat
a
Source=[SERVERNAME]"
Conn.Open
TableName = InputBox("Enter table name: ")
If TableName = "" Then Exit Sub
Set RS = Conn.Execute("SELECT TOP 1 * FROM " & TableName)
Range("A1:A1000").Clear
For A = 1 To RS.Fields.Count
Range("A" & A).Value = RS.Fields(A - 1).Name
Next
RS.Close
Conn.Close
End Sub
just provide the necessary connection information in the connection string
(without the [ ] ), add a reference to the latest "microsoft activex dat
a
objects" and run the macro. it will list all fields of a given table in the
current excel worksheet
"Cindy B" wrote:

> Newby question. We have a new vendor who will be needing to do data
> convertion from our current SQL app into his new application, as part of t
he
> implimentation. I need to identify the complete file structure in order t
o
> get a quote for that part of the project.
> I know how to export the list of the tables, but is there a similar way to
> export the list of fields. OR..any query possibities that could be run pe
r
> table or mutiple tables that would list just the fields - no data?
> Any help or ideas would be greatly appreciated - thanks in advance for you
r
> time and help!
> --
> Cindy B|||One way against the ANSI INFORMATIO_SCHEMA views
select c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.NUMERIC_PRECISION from
INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY c.TABLE_NAME,c.ORDINAL_POSITION
http://sqlservercode.blogspot.com/

query to identify all fields in a table

Newby question. We have a new vendor who will be needing to do data
convertion from our current SQL app into his new application, as part of the
implimentation. I need to identify the complete file structure in order to
get a quote for that part of the project.
I know how to export the list of the tables, but is there a similar way to
export the list of fields. OR..any query possibities that could be run per
table or mutiple tables that would list just the fields - no data?
Any help or ideas would be greatly appreciated - thanks in advance for your
time and help!
--
Cindy BHi,
im sure there is a way to query the system tables in order get the lis of
fields, but don't ask me how ;)
however, you may want to use the following excel vba macro:
Sub GetFields()
Dim A As Long
Dim TableName As String
Dim RS As ADODB.Recordset
Dim Conn As New ADODB.Connection
Conn.ConnectionString = "Provider=SQLOLEDB.1;Password=[PASSWORD];Persist
Security Info=True;User ID=[USERNAME];Initial Catalog=[DATABASE];Data
Source=[SERVERNAME]"
Conn.Open
TableName = InputBox("Enter table name: ")
If TableName = "" Then Exit Sub
Set RS = Conn.Execute("SELECT TOP 1 * FROM " & TableName)
Range("A1:A1000").Clear
For A = 1 To RS.Fields.Count
Range("A" & A).Value = RS.Fields(A - 1).Name
Next
RS.Close
Conn.Close
End Sub
just provide the necessary connection information in the connection string
(without the [ ] ), add a reference to the latest "microsoft activex data
objects" and run the macro. it will list all fields of a given table in the
current excel worksheet
"Cindy B" wrote:
> Newby question. We have a new vendor who will be needing to do data
> convertion from our current SQL app into his new application, as part of the
> implimentation. I need to identify the complete file structure in order to
> get a quote for that part of the project.
> I know how to export the list of the tables, but is there a similar way to
> export the list of fields. OR..any query possibities that could be run per
> table or mutiple tables that would list just the fields - no data?
> Any help or ideas would be greatly appreciated - thanks in advance for your
> time and help!
> --
> Cindy B|||One way against the ANSI INFORMATIO_SCHEMA views
select c.TABLE_NAME,c.COLUMN_NAME,c.DATA_TYPE,c.NUMERIC_PRECISION from
INFORMATION_SCHEMA.TABLES t
join INFORMATION_SCHEMA.COLUMNS c on t.TABLE_NAME = c.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY c.TABLE_NAME,c.ORDINAL_POSITION
http://sqlservercode.blogspot.com/

Query to check current executing SQL Agent jobs

Is there a query I can run to find current running SQL Agent jobs ?> Is there a query I can run to find current running SQL Agent jobs ?
Use sp_help_job, check the current_execution_status column in the output.
Statuses are not described in Books OnLine; you can find the description at
http://www.win2000mag.com/SQLServer...588/42588.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Query to check current executing SQL Agent jobs

Is there a query I can run to find current running SQL Agent jobs ?
> Is there a query I can run to find current running SQL Agent jobs ?
Use sp_help_job, check the current_execution_status column in the output.
Statuses are not described in Books OnLine; you can find the description at
http://www.win2000mag.com/SQLServer/...88/42588.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Query to check current executing SQL Agent jobs

Is there a query I can run to find current running SQL Agent jobs ?> Is there a query I can run to find current running SQL Agent jobs ?
Use sp_help_job, check the current_execution_status column in the output.
Statuses are not described in Books OnLine; you can find the description at
http://www.win2000mag.com/SQLServer...588/42588.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Query to check current executing SQL Agent jobs

Is there a query I can run to find current running SQL Agent jobs ?> Is there a query I can run to find current running SQL Agent jobs ?
Use sp_help_job, check the current_execution_status column in the output.
Statuses are not described in Books OnLine; you can find the description at
http://www.win2000mag.com/SQLServer/Article/ArticleID/42588/42588.html.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Query to Backup Current Database

Hi

Is there any query to backup and restore a database?In my application User has to Backup the current database and restore them at any time.I just need query which backup the database in a specified location and restore it.

Thanks

See BACKUP and RESTORE on Books Online..|||

In the simplest forms:

BACKUP DATABASE myDb TO DISK = 'X:\myDb.BAK'

RESTORE DATABASE myDb FROM DISK = 'X:\myDb.BAK'

There's a little bit more to it though, so please refer to BOL (Books On Line) for additional info on backup and restore, so you can decide upon the exact syntax to use.

/Kenneth

|||

Thanks Mr.Kenneth.Plz suggest some online books or give link regarding backup and restore...Plz

Finally thanks a lot Mr.Kenneth.

|||thanks Manivannan Suggest some online books or links|||when they mention BOL( Books Online), they are referring to Microsoft's Online Documentation for SQL Server 2005.

the homepage for it is here

the download for the english(may 2007) version is here

Query the Security Login or Security User of the current person logged in

How do you find out which group membership "Login or User" the current user has permissions to. I currently use Suser_name() for the current username but how do I see what security login or security user he is assosicated to?

I didn't understand what you meant by Security login/Security User.

Do you meant schema name of the current user.. If yes then the following query will help you.

Code Snippet

Select

Loginname = SUSER_SNAME()

, SchemaName = USER_NAME()

|||

Hi,

I think you want this:

Code Snippet

SELECT

G.name As "group_name",

U.name As "user_name"

FROM

sys.database_role_members M

JOIN

sys.sysusers G WITH(NOLOCK)ON M.role_principal_id = G.uid

JOIN

sys.sysusers U WITH(NOLOCK)ON M.member_principal_id = U.uid

WHERE

U.name = @.user_name

Regards,

Janos

|||I ran this as my sysadmin and it through back dbo, but when running as a user I get blanks. Where is it pulling the information from?|||

Hi,

why do not use the system stored procedure sp_helpuser?

Code Snippet

declare @.username sysname

set @.username =user_name()

execsp_helpuser @.username

Query the Security Login or Security User of the current person logged in

How do you find out which group membership "Login or User" the current user has permissions to. I currently use Suser_name() for the current username but how do I see what security login or security user he is assosicated to?

I didn't understand what you meant by Security login/Security User.

Do you meant schema name of the current user.. If yes then the following query will help you.

Code Snippet

Select

Loginname = SUSER_SNAME()

, SchemaName = USER_NAME()

|||

Hi,

I think you want this:

Code Snippet

SELECT

G.name As "group_name",

U.name As "user_name"

FROM

sys.database_role_members M

JOIN

sys.sysusers G WITH(NOLOCK) ON M.role_principal_id = G.uid

JOIN

sys.sysusers U WITH(NOLOCK) ON M.member_principal_id = U.uid

WHERE

U.name = @.user_name

Regards,

Janos

|||I ran this as my sysadmin and it through back dbo, but when running as a user I get blanks. Where is it pulling the information from?|||

Hi,

why do not use the system stored procedure sp_helpuser?

Code Snippet

declare @.username sysname

set @.username = user_name()

exec sp_helpuser @.username