windows security group and fixed/use defined roles 1

[jordanking]

Hello,

I am just learning about SQL server 2005 security. Is it possible to assign a windows security group to a fixed or user defined database role? I have created a new role wihtin a repective DB in sql 2005, but I am having trouble locating the windows groups when I try to add members to that user defined role.

Thanks

.....
I'd rather be surfing

 

[jrbarnett]

Yes it is.
You need to add the group to SQL Server as a login, then grant them membership of your role within your database.

If it is a local group, the syntax is BUILTIN\groupname, if it is within an Active Directory domain, you need to use DOMAIN\groupname.

John

 

[jordanking]

thanks,



.....
I'd rather be surfing

 

[jordanking]

one more question,

I have the role working correctly for views and most stored procedures, but there are a couple of stored procedures that I can not get to run without full admin access.

I want the procedures to have executable only rights with the new role I created. But when a member of the role tries to execute that procedure (from an access project .accdr front end) it fails and crashes the access front end. These procedures use a dynamic sql search, do you have any ideas why this might be?

Thanks again

the following is the procedure that fails:

ALTER PROCEDURE [dbo].[ps_Residential_SEARCH]
---Declare input variables
                 @OrderBy			nvarchar(50)= NULL,		--column name to odrder by
				 @OrderByDir		nvarchar(1) = NULL,		--Direction of sort 'A' = ascending 'D' = Descending
				 @intCustomerID		int			= NULL,  
                 @intCompanyID		int			= NULL,
				 @dtServiceStart	datetime	= NULL,
				 @dtServiceEnd		datetime	= NULL,   
                 @chrBuildingNum	nvarchar(15)= NULL,
                 @chrRoad			nvarchar(40)= NULL,  
                 @chrSuite			nvarchar(6)	= NULL, 
                 @chrAlternate		nvarchar(50)= NULL,
                 @intRegionID		int		    = NULL,
                 @intAddressTypeID	int			= NULL,   
                 @blnErequest		bit			= NULL,
				 @blnSignsAllowed	bit			= NULL,
                 @blnStatus			bit			= NULL,
				 @blnBill			bit			= NULL,
				 @LocalRows			int			= 0 OUTPUT,
				 @ReturnValue		int			= 0 OUTPUT,
				 @LocalError		int			= 0 OUTPUT,
				 @OutMessage		nvarchar(500) OUTPUT   
AS
BEGIN TRY
	---SET NOCOUNT ON added to prevent extra result sets from
	---interfering with SELECT statements.
	SET NOCOUNT ON;
	---Declare local scope variables
	DECLARE @sql        nvarchar(4000),
			@paramlist  nvarchar(4000)

	---Create the SQL string that will be dynamic
	SELECT @sql =	'SELECT   dbo.Residential.intResidential, dbo.Company.intCompanyID, dbo.Residential.intCustomerID, dbo.Customer.chrLastName + '', '' + dbo.Customer.chrFirstName AS chrCustomer, 
							  dbo.Company.chrName + '' '' + ISNULL(dbo.Company.chrBranch, '''') AS chrCompany, dbo.AddressLkup.intServiceID, 
							  dbo.ResidentialService.chrServiceCode, dbo.AddressLkup.chrPrefix, dbo.AddressLkup.chrSuite, dbo.Address.intAddressID, dbo.Address.chrBuildingNum, dbo.Address.chrRoad, 
							  dbo.Address.intRegionID, dbo.Address.intQuadrantID, dbo.Address.intAddressTypeID, dbo.Address.intZoneID, dbo.ResidentialZone.intMLS, 
							  dbo.AddressType.chrName, dbo.Address.chrAlternate, dbo.Address.blnSignsAllowed, dbo.Residential.blnBill, dbo.Residential.blnStatus, 
							  dbo.Residential.dtServiceDate, dbo.Residential.intUserId, dbo.fx_FORMAT_Address(dbo.AddressLkup.chrSuite, dbo.Address.chrBuildingNum, dbo.Address.chrRoad, 
							  dbo.AddressRegion.chrRegion, dbo.AddressQuadrant.chrDisplay) AS chrAddress
					FROM      dbo.ResidentialZone RIGHT OUTER JOIN
							  dbo.Address ON dbo.ResidentialZone.intZoneID = dbo.Address.intZoneID LEFT OUTER JOIN
							  dbo.AddressType ON dbo.Address.intAddressTypeID = dbo.AddressType.intAddressTypeID LEFT OUTER JOIN
							  dbo.AddressQuadrant ON dbo.Address.intQuadrantID = dbo.AddressQuadrant.intQuadrantID RIGHT OUTER JOIN
							  dbo.AddressLkup ON dbo.Address.intAddressID = dbo.AddressLkup.intAddressID LEFT OUTER JOIN
							  dbo.ResidentialService ON dbo.AddressLkup.intServiceID = dbo.ResidentialService.intServiceID RIGHT OUTER JOIN
							  dbo.ResidentialTransaction RIGHT OUTER JOIN
							  dbo.Residential ON dbo.ResidentialTransaction.intResidentialTransID = dbo.Residential.intResidentialTrans ON 
							  dbo.AddressLkup.intResidentialID = dbo.Residential.intResidential LEFT OUTER JOIN
							  dbo.Customer ON dbo.Residential.intCustomerID = dbo.Customer.intCustomerID LEFT OUTER JOIN
							  dbo.Company ON dbo.Customer.intCompanyID = dbo.Company.intCompanyID LEFT OUTER JOIN
							  dbo.AddressRegion ON dbo.Address.intRegionID = dbo.AddressRegion.intRegionID
					WHERE	  1 = 1'

	---Use is null to determin if parameter was passed, if so append to SQL string...
	IF @intCustomerID IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Customer.intCustomerID = @intCustomerID'

	IF @intCompanyID IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Company.intCompanyID = @intCompanyID'

	IF @dtServiceStart IS NOT NULL AND @dtServiceEnd IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Residential.dtServiceDate >= @dtServiceStart AND dbo.Residential.dtServiceDate < @dtServiceEnd + 1'  

	IF @dtServiceStart IS NOT NULL AND @dtServiceEnd IS NULL                               
	   SELECT @sql = @sql + ' AND dbo.Residential.dtServiceDate >= @dtServiceStart AND dbo.Residential.dtServiceDate < @dtServiceStart + 1' 

	IF @chrBuildingNum IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Address.chrBuildingNum LIKE ''%'' + @chrBuildingNum + ''%''' 

	IF @chrRoad IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Address.chrRoad LIKE ''%'' + @chrRoad + ''%''' 

	IF @chrSuite IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.AddressLkup.chrSuite LIKE ''%'' + @chrSuite + ''%''' 

	IF @chrAlternate IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Address.chrAlternate LIKE ''%'' + @chrAlternate + ''%''' 

	IF @intRegionID IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Address.intRegionID = @intRegionID'

	IF @intAddressTypeID IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Address.intAddressTypeID = @intAddressTypeID '

	IF @blnErequest IS NOT NULL
	BEGIN
		IF @blnErequest = (1)                               
			SELECT @sql = @sql + ' AND dbo.Residential.intUserId > 0'
		IF @blnErequest = 0
			SELECT @sql = @sql + ' AND (dbo.Residential.intUserId = 0 OR dbo.Residential.intUserId IS NULL) '			
	END

	IF @blnSignsAllowed IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Address.blnSignsAllowed = @blnSignsAllowed '

	IF @blnStatus IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Residential.blnStatus = @blnStatus '

	IF @blnBill IS NOT NULL                               
	   SELECT @sql = @sql + ' AND dbo.Residential.blnBill = @blnBill '

	---Set the order by staement
	IF @OrderBy IS NOT NULL
		SELECT @sql = @sql + ' ORDER BY' +
		CASE
			WHEN @OrderBy = 'Realtor'		AND @OrderByDir = 'D'	THEN ' chrCustomer DESC'
			WHEN @OrderBy = 'Realtor'		AND @OrderByDir != 'D'	THEN ' chrCustomer'
			WHEN @OrderBy = 'Company'		AND @OrderByDir = 'D'	THEN ' chrCompany DESC'
			WHEN @OrderBy = 'Company'		AND @OrderByDir != 'D'	THEN ' chrCompany'
			WHEN @OrderBy = 'Date'			AND @OrderByDir = 'D'	THEN ' dbo.Residential.dtServiceDate DESC'
			WHEN @OrderBy = 'Date'			AND @OrderByDir != 'D'	THEN ' dbo.Residential.dtServiceDate'
			WHEN @OrderBy = 'Building'		AND @OrderByDir = 'D'	THEN ' dbo.Address.chrBuildingNum DESC'
			WHEN @OrderBy = 'Building'		AND @OrderByDir != 'D'	THEN ' dbo.Address.chrBuildingNum'
			WHEN @OrderBy = 'Street'		AND @OrderByDir = 'D'	THEN ' dbo.Address.chrRoad DESC'
			WHEN @OrderBy = 'Street'		AND @OrderByDir != 'D'	THEN ' dbo.Address.chrRoad'
			WHEN @OrderBy = 'Region'		AND @OrderByDir = 'D'	THEN ' dbo.AddressRegion.chrRegion DESC'
			WHEN @OrderBy = 'Region'		AND @OrderByDir != 'D'	THEN ' dbo.AddressRegion.chrRegion'
			WHEN @OrderBy = 'Active'		AND @OrderByDir = 'D'	THEN ' dbo.Residential.blnStatus DESC'
			WHEN @OrderBy = 'Active'		AND @OrderByDir != 'D'	THEN ' dbo.Residential.blnStatus'
			---Use default sort order for returned recordset.
			ELSE ' dbo.Residential.dtServiceDate DESC'
		END
	ELSE
		---Use default sort order for returned recordset.
		SELECT @sql = @sql + ' ORDER BY dbo.Residential.dtServiceDate DESC'
	----Set the parameter list for the sp_executesql call
	 SELECT @paramlist =   ' @OrderBy			nvarchar(50),
							 @OrderByDir		nvarchar(1),
							 @intCustomerID		int,  
							 @intCompanyID		int,
							 @dtServiceStart	datetime,
							 @dtServiceEnd		datetime,   
							 @chrBuildingNum	nvarchar(15),
							 @chrRoad			nvarchar(40),  
							 @chrSuite			nvarchar(6), 
							 @chrAlternate		nvarchar(50),
							 @intRegionID		int,
							 @intAddressTypeID	int,   
							 @blnErequest		bit,
							 @blnSignsAllowed	bit,
							 @blnStatus			bit,
							 @blnBill			bit'

	---Execute the function sp_executesql that takes the following parameters: 
	---	sql string, parameter list, and then the declared parameters in the same order as the parmaeter list

	EXEC sp_executesql	@sql, @paramlist, @OrderBy, @OrderByDir, @intCustomerID, @intCompanyID, @dtServiceStart, @dtServiceEnd, @chrBuildingNum, @chrRoad, @chrSuite, @chrAlternate, @intRegionID, @intAddressTypeID, @blnErequest, @blnSignsAllowed, @blnStatus, @blnBill
	---if no errors, set the return value to 0 and return the number of rows in the search results

	SELECT @LocalRows = @@ROWCOUNT, @ReturnValue = 0
	SELECT @LocalRows, @ReturnValue
END TRY
BEGIN CATCH
	---If errors occured - capture error information and return to calling application
	SELECT @ReturnValue = 1, @LocalError = ERROR_NUMBER(), @OutMessage = ('ROWS AFFECTED = ' + CAST(@@ROWCOUNT as nvarchar(6)) + ' SEVERITY = ' + CAST(ERROR_SEVERITY() as nvarchar(3)) + ' STATE = ' + CAST(ERROR_STATE() as nvarchar(2)) + ' PROCEDURE = ' + ERROR_PROCEDURE() + ' LINE = ' + CAST(ERROR_LINE() as nvarchar(3)) + ' MESSAGE ' + ERROR_MESSAGE())
	SELECT @LocalError, @ReturnValue, @OutMessage, ERROR_MESSAGE(),ERROR_PROCEDURE(),ERROR_LINE()
END CATCH

.....
I'd rather be surfing

 

[jordanking]

could it be that I call "sp_executesql" which I believe is a procedurte from the master DB. I tried to set a custom role in the master db granting execute permission to the same user, but it does not work.

.....
I'd rather be surfing

 

[jrbarnett]

Connect to SQL Server with management studio using one of the accounts in which this query doesn't work.
Run some of these components individually: see which ones work and which don't.

Compare this against management studio connected using an account that does work.

This will give you your answer.

You can capture the execution using profiler, then run the statements back through management studio and compare results.

John

 

[jordanking]

thanks,

it was the sp_executesql funtion. That got me going in the right direction so I could fix the problem.

.....
I'd rather be surfing