How to determine objects signed by certificate

Is there any way to determine what objects (stored procedures in my case) have been signed by a certificate? I could not find any system views/tables/functions that seem to give any information about certificates what so ever. Am I missing something or is that part of the security to prevent gaining details about certificate usage?

I'm not sure this posted correctly the first time...

Is there any way to determine what objects (stored procedures in my case) have been signed by a certificate? I could not find any system views/tables/functions that seem to give any information about certificates. There is sys.certificates which doesn't help as far as determining what it has been applied to and a syscerts that I came across in sys.columns, but can't actually query. Am I missing something or is that part of the security to prevent gaining details about certificate usage?

|||

try this:

declare @.thumb varbinary(32)
select @.thumb = thumbprint from sys.certificates where name = 'my_cert'
select object_name(major_id) from sys.crypt_properties where thumbprint = @.thumb
go

hth,

-Steven Gott

S/DET

SQL Server

|||

try this:

declare @.thumb varbinary(32)
select @.thumb = thumbprint from sys.certificates where name = 'your_cert'
select object_name(major_id) from sys.crypt_properties where thumbprint = @.thumb
go

hth,

-Steven Gott

S/DET

SQL Server

|||

Thanks, that is exactly what I was looking for.

How to determine objects signed by a certificate

Is there any way to determine what objects (stored procedures in my case) have been signed by a certificate? I could not find any system views/tables/functions that seem to give any information about certificates what so ever. Am I missing something or is that part of the security to prevent gaining details about certificate usage?

I'm not sure this posted correctly the first time...

Is there any way to determine what objects (stored procedures in my case) have been signed by a certificate? I could not find any system views/tables/functions that seem to give any information about certificates. There is sys.certificates which doesn't help as far as determining what it has been applied to and a syscerts that I came across in sys.columns, but can't actually query. Am I missing something or is that part of the security to prevent gaining details about certificate usage?

|||

try this:

declare @.thumb varbinary(32)
select @.thumb = thumbprint from sys.certificates where name = 'my_cert'
select object_name(major_id) from sys.crypt_properties where thumbprint = @.thumb
go

hth,

-Steven Gott

S/DET

SQL Server

|||

try this:

declare @.thumb varbinary(32)
select @.thumb = thumbprint from sys.certificates where name = 'your_cert'
select object_name(major_id) from sys.crypt_properties where thumbprint = @.thumb
go

hth,

-Steven Gott

S/DET

SQL Server

|||

Thanks, that is exactly what I was looking for.

How to determine if a user has a permission programmatically?

Hi,
I need to determine in advance if a user logged on to SQL Server 2005 can
execute certain privileged stored procedures and statements (such as
sp_addrolemember and ALTER LOGIN). To do this, I though I would need to
determine programmatically if a user has specific permissions (ALTER ANY
LOGIN, ALTER ANY CREDENTIAL, CONTROL SERVER, ALTER ANY USER, CREATE ROLE,
ALTER ANY ROLE).
Is there a way to perform this check programmatically via T-SQL? If this can
be done programmatically, is there a way to determine if a permission is
implied implicitly if it is not set up explicitly (e.g. via a group the user
belongs to, such as BUILTIN\Administrators)? Is there a better approach to
make the check?
Thanks,
AlekTry sp_helprotect , described here
http://msdn2.microsoft.com/en-us/library/aa933420(SQL.80).aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Alek" <alekDOTdavisATintelDOTcom> wrote in message
news:%23sEN40XHHHA.1064@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I need to determine in advance if a user logged on to SQL Server 2005 can
> execute certain privileged stored procedures and statements (such as
> sp_addrolemember and ALTER LOGIN). To do this, I though I would need to
> determine programmatically if a user has specific permissions (ALTER ANY
> LOGIN, ALTER ANY CREDENTIAL, CONTROL SERVER, ALTER ANY USER, CREATE ROLE,
> ALTER ANY ROLE).
> Is there a way to perform this check programmatically via T-SQL? If this
> can be done programmatically, is there a way to determine if a permission
> is implied implicitly if it is not set up explicitly (e.g. via a group the
> user belongs to, such as BUILTIN\Administrators)? Is there a better
> approach to make the check?
> Thanks,
> Alek
>|||In SQL Server 2005, you can use the has_perms_by_name
function to check the current users effective permissions on
securable.
http://msdn2.microsoft.com/en-us/library/ms189802.aspx
-Sue
On Mon, 11 Dec 2006 15:36:43 -0800, "Alek"
<alekDOTdavisATintelDOTcom> wrote:

>Hi,
>I need to determine in advance if a user logged on to SQL Server 2005 can
>execute certain privileged stored procedures and statements (such as
>sp_addrolemember and ALTER LOGIN). To do this, I though I would need to
>determine programmatically if a user has specific permissions (ALTER ANY
>LOGIN, ALTER ANY CREDENTIAL, CONTROL SERVER, ALTER ANY USER, CREATE ROLE,
>ALTER ANY ROLE).
>Is there a way to perform this check programmatically via T-SQL? If this ca
n
>be done programmatically, is there a way to determine if a permission is
>implied implicitly if it is not set up explicitly (e.g. via a group the use
r
>belongs to, such as BUILTIN\Administrators)? Is there a better approach to
>make the check?
>Thanks,
>Alek
>|||Thank you Remus. This is very helpful.
Alek
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> w
rote
in message news:%233xgzrYHHHA.1248@.TK2MSFTNGP03.phx.gbl...
> Try sp_helprotect , described here
> http://msdn2.microsoft.com/en-us/library/aa933420(SQL.80).aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Alek" <alekDOTdavisATintelDOTcom> wrote in message
> news:%23sEN40XHHHA.1064@.TK2MSFTNGP04.phx.gbl...
>|||Thanks a lot Sue. This looks exactly like what I'm looking for. :-)
Alek
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:ng3sn2pdu6apq505ftk8mhnpp73l2i7mk7@.
4ax.com...
> In SQL Server 2005, you can use the has_perms_by_name
> function to check the current users effective permissions on
> securable.
> http://msdn2.microsoft.com/en-us/library/ms189802.aspx
> -Sue
> On Mon, 11 Dec 2006 15:36:43 -0800, "Alek"
> <alekDOTdavisATintelDOTcom> wrote:
>
>sql

How to deploy the database

Hi All,

Can anyone tell me,is it possible to create an exe or msi for sql stored procedures,tables and triggers?I want to deploy the database objects(stored proc,tables,views and functions) as an exe file..just like publishing and deploying the asp.net application.Is it possible for sql server database objects.Pls,let me know.

Thank U

You can generate scripts for them. Right click on db - > All Tasks -> Generate Scripts.. follow the wizard.

|||

I know that.But is it possible to create exe or msi package for the sql objects.just like deploying the asp.net application

|||

I'm sorry.I forgot to tell you(Dinakar).Thank You.

|||

There is an osql utility to compile the scripts from a file..

How to delete records on dependent tables? Thank You.

Hello,
I am creating my first procedures in SQL using SQL 2005.
I have 3 tables, with the following columns:
Surveys - [SurveyId](PK) and [SurveyName]
Questions - [SurveyId](FK), [SurveyQuestionId](PK) and [SurveyQuestion]
Answers - [SurveyQuestionId](FK), [SurveyAnswerId](PK) and
[SurveyAnswer]
Each survay can include various questions and each question can include
several answers.
This is way I am using the Foreign Keys in both Questions and Answers
tables. To relate the tables.
I created a procedure which deletes a Survey given its SurveyId. This is
part is done.
I also need to delete all the questions dependent on that survey and all
the answers dependent on those questions.
How can I delete survey, its questions and their answers when receiving
the SurveyId?
Thank You Very Much,
Miguel
Here is the code of the procedure that I created which in this moment
only deletes the survey from the Surveys table:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Surveys_DeleteSurvey]
-- Procedure Parameters
@.SurveyId As uniqueidentifier
AS
BEGIN
-- Check if SurveyId is null
IF( @.SurveyId IS NULL )
RETURN -1
ELSE
BEGIN
-- Return '-1' if a survey with SurveyId given value is not found
IF( NOT EXISTS( SELECT @.SurveyId FROM dbo.Surveys WHERE @.SurveyId =
SurveyId ) )
RETURN -1
END
-- Delete the survey with SurveyId given value
DELETE FROM dbo.Surveys WHERE @.SurveyId = SurveyId
-- Return '0' when successful
RETURN 0
ENDYou just need to add cascade delete to your foreign key constraints and the
database will do this automatically.
This assumes that you always want to delete the related records.
"Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
news:%232tUEB1TGHA.6048@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am creating my first procedures in SQL using SQL 2005.
> I have 3 tables, with the following columns:
> Surveys - [SurveyId](PK) and [SurveyName]
> Questions - [SurveyId](FK), [SurveyQuestionId](PK) and [SurveyQuestion]
> Answers - [SurveyQuestionId](FK), [SurveyAnswerId](PK) and
> [SurveyAnswer]
> Each survay can include various questions and each question can include
> several answers.
> This is way I am using the Foreign Keys in both Questions and Answers
> tables. To relate the tables.
> I created a procedure which deletes a Survey given its SurveyId. This is
> part is done.
> I also need to delete all the questions dependent on that survey and all
> the answers dependent on those questions.
> How can I delete survey, its questions and their answers when receiving
> the SurveyId?
> Thank You Very Much,
> Miguel
> Here is the code of the procedure that I created which in this moment
> only deletes the survey from the Surveys table:
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
> ALTER PROCEDURE [dbo].[Surveys_DeleteSurvey]
> -- Procedure Parameters
> @.SurveyId As uniqueidentifier
> AS
> BEGIN
> -- Check if SurveyId is null
> IF( @.SurveyId IS NULL )
> RETURN -1
> ELSE
> BEGIN
> -- Return '-1' if a survey with SurveyId given value is not found
> IF( NOT EXISTS( SELECT @.SurveyId FROM dbo.Surveys WHERE @.SurveyId =
> SurveyId ) )
> RETURN -1
> END
> -- Delete the survey with SurveyId given value
> DELETE FROM dbo.Surveys WHERE @.SurveyId = SurveyId
> -- Return '0' when successful
> RETURN 0
> END
>|||Hi,
Could you, please, explain how to add cascade delete to my foreign key
constraints.
I am starting with SQL and I have no idea how to do that.
Thanks,
Miguel
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:OsssqG1TGHA.2656@.TK2MSFTNGP10.phx.gbl:
> You just need to add cascade delete to your foreign key constraints and th
e
> database will do this automatically.
> This assumes that you always want to delete the related records.
> "Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
> news:%232tUEB1TGHA.6048@.TK2MSFTNGP11.phx.gbl...|||It is best to look it up in Books OnLine, or check with your DBA.
Here is an example of the syntax, however.
ALTER TABLE [owner].[tablename] ADD CONSTRAINT
[constraintname] Foreign KEY
(
[Columnname]
) REFERENCES [owner].[OtherTablename] (
[Columnname]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
"Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
news:ujxYNCOVGHA.4300@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Could you, please, explain how to add cascade delete to my foreign key
> constraints.
> I am starting with SQL and I have no idea how to do that.
> Thanks,
> Miguel
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:OsssqG1TGHA.2656@.TK2MSFTNGP10.phx.gbl:
>
the
[SurveyQuestion]
include
is
all
receiving
>

How to Delete Multiple Stored Procedures ?

I would Like to delete all stored procedures from the database using sql statement, wild cards with DROP PROCEDURE , dont work
ThanksOriginally posted by pkrol
I would Like to delete all stored procedures from the database using sql statement, wild cards with DROP PROCEDURE , dont work
Thanks
Use PL/SQL:

BEGIN
FOR r in (SELECT object_name FROM user_objects WHERE object_type = 'PROCEDURE' )
LOOP
EXECUTE IMMEDIATE 'DROP PROCEDURE '||r.object_name;
END LOOP;
END;
/

How to delete a group of stored procedures

For reasons I won't go into I have a need to delete over a 100 stored procedures from a database. The stored procedures have a common prefix such as dbo.aspnet_createUser.

I'm trying to come up with something that will allow me to delete all procedures that contain aspnet in their name.

The only thing close I've found is CONTAINS but I get an error I can use it against sysobjects because it isn't indexed.

Any ideas of how I can go about doing this other than deleting each one by hand?

ThanksExecute this in query analyzer or management studio, copy the output, paste

to the top pane, and run again:

SELECT 'DROP PROCEDURE dbo.'+ROUTINE_NAME+';'

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE='PROCEDURE'

AND ROUTINE_SCHEMA='dbo'

AND ROUTINE_NAME LIKE 'aspnet[_]%'

wrote in message

news:d05d74cc-5383-4e2c-93ab-3182561f2726@.discussions.microsoft.com...

> For reasons I won't go into I have a need to delete over a 100 stored

> procedures from a database. The stored procedures have a common prefix

> such as dbo.aspnet_createUser.

>

> I'm trying to come up with something that will allow me to delete all

> procedures that contain aspnet in their name.

>

> The only thing close I've found is CONTAINS but I get an error I can use

> it against sysobjects because it isn't indexed.

>

> Any ideas of how I can go about doing this other than deleting each one

> by hand?

>

> Thanks

>|||

Thank you! Worked like a champ.

Could you by chance recommend a good book with examples for somebody interested in learning Transact-SQL to come up with statements such as you provided?

Thanks again!