Does a role exist?

In my proc I have to add a user to a role in another database. I do it
in the following manner:
declare @.IIDdatabase varchar(255)
set @.IIDdatabase = 'IID'
SELECT @.cmd = N'USE ' + CAST(@.IIDdatabase as nvarchar) + @.crlf + N'exec
sp_addrolemember @.rolename = ''Role_MyCustomRole'', @.membername = ''' +
CAST(@.user as nvarchar) + '''' + @.crlf
EXEC @.STATUS = sp_executesql @.cmd
And this seems to work fine. However, how do I find out whether the
role (in the other database) exists to begin with? The difficulty here
is that the name of the database is passed in as a variable.
Thanks.Hi Frank,
I understand that you would like to judge wheter the database role is
existed before your execute your SQL statement.
If I have misunderstood, please let me know.
You may refer to the following stored procedure:
---
/* judge if a database role is existed in a database */
CREATE PROCEDURE proc_existrole
(
@.dbname nvarchar(50)=null,
@.rolename nvarchar(50)=null,
@.isexisted int output
)
as
declare @.strSQL nvarchar(200)
declare @.strCount nvarchar(50)
begin
if @.dbname is null or @.rolename is null
begin
set @.isexisted = -1
return
end
set @.strCount = N'@.nCount int output'
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
if @.isexisted>0
set @.isexisted = 1
else
set @.isexisted = 0
end
----
--
You can call it like this:
----
--
declare @.isexisted int
exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
if @.isexisted=0
begin
..
end
----
--
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||I am sorry, I should have mentioned. The code must work on SQL 2000 as
well as SQL 2005.
Charles Wang[MSFT] wrote:
> Hi Frank,
> I understand that you would like to judge wheter the database role is
> existed before your execute your SQL statement.
> If I have misunderstood, please let me know.
> You may refer to the following stored procedure:
> ---
> /* judge if a database role is existed in a database */
> CREATE PROCEDURE proc_existrole
> (
> @.dbname nvarchar(50)=null,
> @.rolename nvarchar(50)=null,
> @.isexisted int output
> )
> as
> declare @.strSQL nvarchar(200)
> declare @.strCount nvarchar(50)
> begin
> if @.dbname is null or @.rolename is null
> begin
> set @.isexisted = -1
> return
> end
> set @.strCount = N'@.nCount int output'
> set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
> +'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
> execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
> if @.isexisted>0
> set @.isexisted = 1
> else
> set @.isexisted = 0
> end
> ----
> --
> You can call it like this:
> ----
> --
> declare @.isexisted int
> exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
> if @.isexisted=0
> begin
> ..
> end
> ----
> --
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================>|||Frank,
This will still work on both 2000 and 2005, although it is certainly
deprecated.
SELECT name
FROM sysusers
WHERE issqlrole = 1
RLF
"Frank Rizzo" <none@.none.com> wrote in message
news:uEsuyFB5HHA.2108@.TK2MSFTNGP02.phx.gbl...
>I am sorry, I should have mentioned. The code must work on SQL 2000 as
>well as SQL 2005.
> Charles Wang[MSFT] wrote:
>> Hi Frank,
>> I understand that you would like to judge wheter the database role is
>> existed before your execute your SQL statement.
>> If I have misunderstood, please let me know.
>> You may refer to the following stored procedure:
>> ---
>> /* judge if a database role is existed in a database */
>> CREATE PROCEDURE proc_existrole
>> (
>> @.dbname nvarchar(50)=null,
>> @.rolename nvarchar(50)=null,
>> @.isexisted int output
>> )
>> as
>> declare @.strSQL nvarchar(200)
>> declare @.strCount nvarchar(50)
>> begin
>> if @.dbname is null or @.rolename is null
>> begin
>> set @.isexisted = -1
>> return
>> end
>> set @.strCount = N'@.nCount int output'
>> set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
>> +'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
>> execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
>> if @.isexisted>0
>> set @.isexisted = 1
>> else
>> set @.isexisted = 0
>> end
>> ----
>> --
>> You can call it like this:
>> ----
>> --
>> declare @.isexisted int
>> exec proc_existrole @.IIDdatabase,N'Role_MyCustomRole',@.isexisted output
>> if @.isexisted=0
>> begin
>> ..
>> end
>> ----
>> --
>> Hope this helps. If you have any other questions or concerns, please feel
>> free to let me know.
>> Best regards,
>> Charles Wang
>> Microsoft Online Community Support
>> ====================================================== When responding to
>> posts, please "Reply to Group" via your newsreader so that others may
>> learn and benefit from this issue.
>> ======================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights. ======================================================|||Hi Frank,
In this case, you can combine Russell's suggestion and mine into one
procedure like this:
----
--
/* judge if a database role is existed in a database */
ALTER PROCEDURE proc_existrole
(
@.dbname nvarchar(50)=null,
@.rolename nvarchar(50)=null,
@.isexisted int output
)
as
declare @.strSQL nvarchar(200)
declare @.strCount nvarchar(50)
declare @.productVersion nchar(1)
begin
if @.dbname is null or @.rolename is null
begin
set @.isexisted = -1
return
end
set @.strCount = N'@.nCount int output'
SELECT @.productVersion = SUBSTRING(CAST(SERVERPROPERTY ( 'PRODUCTVERSION' )
AS NVARCHAR(10)),1,1)
IF @.productVersion = '9'
BEGIN
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.sys.database_principals where type=''R'' and name='''+@.rolename + ''''
END
ELSE
BEGIN
set @.strSQL = N'select @.nCount = count(*) from ' + @.dbname
+'.dbo.sysusers where issqlrole=1 and name='''+@.rolename + ''''
END
execute sp_executesql @.strSQL, @.strCount,@.nCount = @.isexisted output
if @.isexisted>0
set @.isexisted = 1
else
set @.isexisted = 0
end
----
--
Please feel free to let us know if you need further assistance on this
issue. Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Hi Frank,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Charles Wang[MSFT] wrote:
> Hi Frank,
> I am interested in this issue. Would you mind letting me know the result of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
Thanks, your suggestion worked great.