Drop role, user, login

Okay I figured out how to determine if stored procs and funcs exist before dropping them.

How do I do the same for ROLE, LOGIN, USER?

I want get rid of annoying messages in my scripts when trying to drop something that doesn't exist.

Server 2005 and Server Express 2005

Thanks

For logins you can query sys.server_principals, for users you can query sys.database_principals or USER_ID() and for roles sys.database_principals. Ex:

-- logins (if you want to drop certificate based logins then you need to check for other types)

if exists(select * from sys.server_principals

where type IN ('S', 'U', 'G') and name = @.name)

begin

set @.name = quotename('somelogin')

exec('drop login ' + @.name)

end

-- users

if USER_ID(@.name) is not null

begin

set @.name = quotename('someuser')

exec('drop user ' + @.name)

end

-- users

if exists(select * from sys.database_principals

where type IN ('S') and name = @.name)

begin

set @.name = quotename('someuser')

exec('drop user ' + @.name)

end

-- roles

if exists(select * from sys.database_principals

where type IN ('R') and name = @.name)

begin

set @.name = quotename('someuser')

exec('drop role ' + @.name)

end

See the BOL "security catalog views" topic for more details.

|||Many thanks

DROP PROCS

Is it necessary to drop & recreate all procedures and triggers every
few months? If yes, why
?.
No; where did you hear that?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"docsql" <docsql@.noemail.nospam> wrote in message
news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
> Is it necessary to drop & recreate all procedures and triggers every
> few months? If yes, why
> ?.
>
>
|||Actually it was a question on a supplemental questionnaire for a DBA
position. Do you think it might be true for other databases?
Sybase/oracle?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> No; where did you hear that?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>
|||Sounds like a trick question to me. Your other questions, too. I would be
very cautious about taking this job if I were you.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>
|||By the way, they might be looking for recompilation (perhaps whoever wrote
the test didn't know how to recompile stored procedures and thought they had
to be dropped and re-created?) ... That's my only guess.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>

DROP PROCS

Is it necessary to drop & recreate all procedures and triggers every
few months? If yes, why
?.No; where did you hear that?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
> Is it necessary to drop & recreate all procedures and triggers every
> few months? If yes, why
> ?.
>
>|||Actually it was a question on a supplemental questionnaire for a DBA
position. Do you think it might be true for other databases?
Sybase/oracle?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> No; where did you hear that?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>> Is it necessary to drop & recreate all procedures and triggers every
>> few months? If yes, why
>> ?.
>>
>|||Sounds like a trick question to me. Your other questions, too. I would be
very cautious about taking this job if I were you.
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>> No; where did you hear that?
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "docsql" <docsql@.noemail.nospam> wrote in message
>> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>> Is it necessary to drop & recreate all procedures and triggers
>> every few months? If yes, why
>> ?.
>>
>>
>|||By the way, they might be looking for recompilation (perhaps whoever wrote
the test didn't know how to recompile stored procedures and thought they had
to be dropped and re-created?) ... That's my only guess.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>> No; where did you hear that?
>>
>> --
>> Adam Machanic
>> Pro SQL Server 2005, available now
>> http://www.apress.com/book/bookDisplay.html?bID=457
>> --
>>
>> "docsql" <docsql@.noemail.nospam> wrote in message
>> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>> Is it necessary to drop & recreate all procedures and triggers
>> every few months? If yes, why
>> ?.
>>
>>
>

DROP PROCS

Is it necessary to drop & recreate all procedures and triggers every
few months? If yes, why
?.No; where did you hear that?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
> Is it necessary to drop & recreate all procedures and triggers every
> few months? If yes, why
> ?.
>
>|||Actually it was a question on a supplemental questionnaire for a DBA
position. Do you think it might be true for other databases?
Sybase/oracle?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
> No; where did you hear that?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:u%23J7LRH7FHA.956@.TK2MSFTNGP10.phx.gbl...
>|||Sounds like a trick question to me. Your other questions, too. I would be
very cautious about taking this job if I were you.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>|||By the way, they might be looking for recompilation (perhaps whoever wrote
the test didn't know how to recompile stored procedures and thought they had
to be dropped and re-created?) ... That's my only guess.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:%231jyCmH7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Actually it was a question on a supplemental questionnaire for a DBA
> position. Do you think it might be true for other databases?
> Sybase/oracle?
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uCEDuSH7FHA.3976@.TK2MSFTNGP15.phx.gbl...
>

Drop all column extended propterties

Need help with admin scripts.

Have written stored procs to insert/update/drop column extended props but can't quite figure out how to drop all extended props for a particular table::column.

Tried stored proc to cursor on result

fn_listextendedproperty

but couldn't get that to work.

Where are xtended props stored in db?

This will work. Never touch the system tables, and it is not even reasonable to do it in 2005. This will work (I will leave it to you to parameterize):

CREATE TABLE T1 (id int , name char (20));
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO
EXEC sp_addextendedproperty 'schmaption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

declare @.cursor cursor, @.property sysname
set @.cursor = cursor for
select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.property

if @.@.fetch_status <> 0 break

EXEC sp_dropextendedproperty @.property, 'schema', dbo, 'table', 'T1', 'column', id;
end

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

GO
DROP TABLE T1;
GO

Consider posting a suggestion on the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx) requesting this feature. Post here if you do and ask for votes ( I will second your motion.)

Drop all column extended propterties

Need help with admin scripts.

Have written stored procs to insert/update/drop column extended props but can't quite figure out how to drop all extended props for a particular table::column.

Tried stored proc to cursor on result

fn_listextendedproperty

but couldn't get that to work.

Where are xtended props stored in db?

This will work. Never touch the system tables, and it is not even reasonable to do it in 2005. This will work (I will leave it to you to parameterize):

CREATE TABLE T1 (id int , name char (20));
GO
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO
EXEC sp_addextendedproperty 'schmaption', 'Employee ID', 'schema', dbo, 'table', 'T1', 'column', id;
GO

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

declare @.cursor cursor, @.property sysname
set @.cursor = cursor for
select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.property

if @.@.fetch_status <> 0 break

EXEC sp_dropextendedproperty @.property, 'schema', dbo, 'table', 'T1', 'column', id;
end

select name
from fn_listextendedproperty(NULL, 'schema','dbo','table','T1','column','id')

GO
DROP TABLE T1;
GO

Consider posting a suggestion on the feedback center (http://lab.msdn.microsoft.com/productfeedback/default.aspx) requesting this feature. Post here if you do and ask for votes ( I will second your motion.)