IS_MEMBER Not Working With User Defined Database Roles
Mar 1, 2007
I am not sure why this is not working it clearly states in the MSDN that it should "
'role'
Is the name of the SQL Server role being checked. role is sysname and can include the database fixed roles or user-defined roles but not server roles.
"
I have seen many questions revolving around this issue on this site and on the net about this but know one can answer it
I have created a new user defined database role called testrole with any owner
then created a new sql login and user (Sql Authentication)
add the user to the database role testrole
check IS_MEMBER and it returns 0
try this with a fixed database role and I get the desired result of 1
this is simple and should not be such a problem for every one
As part of our security project, I've done the following when logged in as 'sa':
Created database roles 'dbrole1' within dbAccount
Created login and user 'user1' and added user to be a member of 'dbrole1'
Granted execute permissions on sp1 and sp2 to 'dbrole1'
However, I didn't see the above permissions listed in SQL Server Management Studio - Database - Security - Roles - Database Roles - 'dbrole1' properties - securables
I am trying to get a decimal value from this simple user defined function, but it is not working. CREATE FUNCTION dbo.GETANSWERRETURNS decimal(6,2)ASBEGIN DECLARE @Result decimal(6,2) SELECT @Result = 10 / 4 RETURN @Result ENDThe real answer is 2.5, but I keep getting 2. Also, if I replace 10 / 4 with 3 / 4, I get the answer 0. I tried several things like changing decimal to numeric and still get the same results.
SELECT DATEDIFF(n , LAG(CAST(Date AS DATETIME) + CAST(Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ), CAST(Date AS DATETIME) + CAST(Time AS DATETIME)) FROM [DataGapTest]
Gives the right output:
NULL 1 1 3548 0
However, when I put the statement in a function, I get only zeros as the output. It's as if the lag and current value are always the same (but they are not of course).
CREATE FUNCTION dbo.GetTimeInterval(@DATE date, @TIME time) RETURNS INT AS BEGIN DECLARE @timeInterval INT SELECT @timeInterval = DATEDIFF(n , LAG(CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME), 1) OVER ( ORDER BY Date, Time ), CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME)) FROM dbo.[DataGapTest] RETURN @timeInterval END
) ================================================= In my VB 2005 Express, I created a project "KimmelCallNWspWithAdoNet" that had the following code: --Form_Kimmel.vb-- Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Kimmel
Public Sub InsertCustomer()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
End Class ==============================================
I executed the Form_Kimmel.vb and I got no errors. But I did not get the new values insterted in the table "Custermers" of Northwind database. Please help and tell me what I did wrong and how to correct this problem.
Hi, I am working with the followings: jdk5 in eclipse3.3 and Microsoft sql server. For the jdbc application, i used to connect the database with the following code: Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); con=DriverManager.getConnection("jdbc:microsoftqlserver://NODE1:1433","sa"," "); The above code woks fine and I able to connect the database.
Here is my problem: The above code only connect the master database. But I want to connect the user defined databse(that is., other than master database). Can anyone tell me the solution to solve the issue.
I have defined a data type "OC_BUN_NAME" in database "CVPRO". I want to create a table tempdb.dbo.CVPRO in SQL SERVER 2005 system Database tempdb. But SQL SERVER 2005 DBMS gives a Error Messages:"Can not find the data type OC_BUN_NAME".
How can I do? How to use data types in the other database?
I am new user of SQL Server. I have some problems with these words. I want to make my database works in my specified permissions. I will specify permissions with schemas and these schema wants an owner. I want this owner should be my user. When creating a user it needs a valid login. I am selecting my login and it occurs and error says this login has an different user. I am specifying permissions with roles. But i can't make association all of them. I hope i told my problem to you as well. If you explain these words to me and tell me how can i do my database's works with my own schemas, users and roles i'll be grateful. Thanks for advices.
Hi all, In my SQL Server Management Studio Express, I have the following Database "ChemAveRpd", Table "dbo.LabTests", and Content of the Table: dbo.LabTests Column_name Type Length Prec Scale AnalyteID int 4 10 0 (Primary Key) AnalyteName nvarch 510 CasNumber nvarch 510 Result numeric 5 8 2 Unit nvarch 510 SampleID int 4 10 0 (Foreign Key)
I work on a copy of SQL Server Express on my desktop. After modifying and creating views and user defined functions, I would like to copy and paste them into the working database. Is there a method programmatically of doing this or must I copy and paste the t-sql language from the existing view to the new database--then save the new view on the working database?
I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?
After reading Books Online, I am still confused with Database Role vs Application role.
My intention is to control the end users' authority on the database, where the end users will access through Winforms client application. With proper assignment of schema and database roles to an user, I believe this will enough to control the permisison of an user.
If this is the case, why Application role exists? When and why should I use Application Role? How is it different from Fixed Database Role?
I just found that the content of my Database "ssmsExpressDB" is gone, but the name "ssmsExpressDB" remains in the Object Explorer of SQL Server Management Studio Express. If I delected the name "ssmsExpressDB" and executed the following .sql:
Unable to open the physical file "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatassmsExpressDB.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
And I have closed all my projects and I do not know what " The process cannot access the file because it is being used by another process" is all about!? Please help and tell me how I can re-attach the content of my "ssmsExpressDB" in the Object Explorer of SQL Server Management Studio Express.
I found the "ssmsExpressDB" is being used by my VB 2005 Express project "Hello-SQLCLR-1": in the Database Explorer, Data Connections place. How can I put it back to the Object Explorer of SQL Server Management Studio Express? Please help and advise.
Hello Everyone, I am trying to use a custom role provider, the main purpose so that I don't have to use a database file. My web.config file is posted below. My problem is that I think I have followed all the steps to create a custom role provider using both articles on MSDN and some written by Scott Gu. After Modifying my web.config file I went to the asp.net configuration to test the connection. Users are created in the remote database but roles are being picked up and created in the App_Data folder. Can anyone give me a hint at what I am doing wrong? thanks in advance. <?xml version="1.0"?> <!-- Note: As an alternative to hand editing this file you can use the web admin tool to configure settings for your application. Use the Website->Asp.Net Configuration option in Visual Studio. A full list of settings and comments can be found in machine.config.comments usually located in WindowsMicrosoft.NetFrameworkv2.xConfig --> <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"> <appSettings/> <connectionStrings> <add name="MyConnectionString" connectionString="Data Source=10.10.10.10;Database=MyDataBase;Persist Security Info=True;User ID=user;Password=password" providerName="System.Data.SqlClient"/> </connectionStrings> <system.web> <!-- Set compilation debug="true" to insert debugging symbols into the compiled page. Because this affects performance, set this value to true only during development. --> <roleManager enabled="true"/> <membership defaultProvider="CustomSQLRoleManager"> <providers> <clear/> <add name="CustomSQLRoleManager" type="System.Web.Security.SqlMembershipProvider" connectionStringName="MyConnectionString" applicationName="MyAppllicationName" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" requiresQuestionAndAnswer="false" requiresUniqueEmail="true"/> </providers> </membership> <compilation debug="true"> <assemblies> <add assembly="System.Design, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/> <add assembly="System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/></assemblies></compilation> <!-- The <authentication> section enables configuration of the security authentication mode used by ASP.NET to identify an incoming user. --> <authentication mode="Forms"/> <!-- The <customErrors> section enables configuration of what to do if/when an unhandled error occurs during the execution of a request. Specifically, it enables developers to configure html error pages to be displayed in place of a error stack trace. <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm"> <error statusCode="403" redirect="NoAccess.htm" /> <error statusCode="404" redirect="FileNotFound.htm" /> </customErrors> --> </system.web> </configuration>
I have 5 cubes, and hierachy defined for all cubes. for example:geography database with 5 continents as cubes and contries as dimensions.Now when i am doing security restrictions on my dimension ex: In USA dimension if i want only to give access to texas region then i should be able to see only texas cities. But i cansee all the states under USA even after selecting only Texas region under Dimension data tab inside ROles section in SSMS.I have tried security at database ,cube level as well as dimension level.But still not working.is that because of some wrong design of cubes or something related to database design.? I am not able to undersand that except roles everything in my cubes or datawarehouse is working fine without and defect in data.
Our server has integrated security setup, upon startup of the server, we have a continuous flow of error msg: Login Failed: Reason: User '_' not defined as a valid user of trusted connection. The timing of these messages only around 30 seconds apart The only incident from Technet I can find is Q186314, but we don't have replication setup, anyone knows where I can look into ? I recycle the server but didn't help. I appreciate any help I can get .. Thanks.
I've created a training database, and added a user to it. Now I am trying to figure out what database roles I need to give him. Can I get away with public only, or he will need the db_owner role? Thanks.
I have a sp that my users need access to run and currently their roles are set up as users with db_datareader, db_datawriter. I'm having trouble getting them access just adding permissions to the tables being used here. What is the best method to allow them access to only the neccessary roles to run this sp?
ALTER PROCEDURE dbo.spi_CallList (@strAgent nchar(4), @MEA int)
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qs_CallList1') DROP VIEW qs_CallList1
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qs_CallList2') DROP VIEW qs_CallList2
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'qs_CallList3') DROP VIEW qs_CallList3
SET @SQL = 'Create view qs_CallList1 as SELECT top 100 percent tsd_Claim.clinsnum AS [Ins#], Sum(tsd_Claim.cloutstandingamt) AS OutstndByIns FROM (tsd_Claim LEFT JOIN [qs_SQLClaimStatusLastEntryCL] ON (tsd_Claim.clnum = [qs_SQLClaimStatusLastEntryCL].Claim) AND (tsd_Claim.clpid = [qs_SQLClaimStatusLastEntryCL].Pat)) LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID WHERE (((tsd_Patient.PAGENT)=''' + ltrim(rtrim(@strAgent)) + ''') AND (([qs_SQLClaimStatusLastEntryCL].Pat) Is Null) AND ((tsd_Claim.clfromdos)<GetDate()- ' + cast(@mea as varchar(12)) + ')) GROUP BY tsd_Claim.clinsnum ORDER BY Sum(tsd_Claim.cloutstandingamt) DESC' EXECUTE(@sql)
SET @SQL = 'Create view qs_CallList2 as SELECT TOP 100 percent tsd_Claim.clinsnum AS [Ins#], tsd_Claim.clpid AS [Account#], Sum(tsd_Claim.cloutstandingamt) AS OutstndByAcct FROM (tsd_Claim LEFT JOIN [qs_SQLClaimStatusLastEntryCL] ON (tsd_Claim.clnum = [qs_SQLClaimStatusLastEntryCL].Claim) AND (tsd_Claim.clpid = [qs_SQLClaimStatusLastEntryCL].Pat)) LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID WHERE (((tsd_Patient.PAGENT)=''' + ltrim(rtrim(@strAgent)) + ''') AND (([qs_SQLClaimStatusLastEntryCL].Pat) Is Null) AND ((tsd_Claim.clfromdos)<GETDATE()-' + cast(@mea as varchar(12)) + ')) GROUP BY tsd_Claim.clinsnum, tsd_Claim.clpid ORDER BY Sum(tsd_Claim.cloutstandingamt) DESC' EXECUTE(@SQL)
SET @SQL = 'UPDATE tsd_Patient SET tsd_Patient.PPriority = Null WHERE (((tsd_Patient.PAGENT)=''' + ltrim(rtrim(@strAgent)) + '''))' EXECUTE(@SQL)
set @SQL = 'truncate table priority' EXECUTE(@SQL)
set @SQL = 'INSERT INTO Priority ( Agent, [Account#] ) SELECT tsd_Patient.PAGENT, tsd_Claim.clpid AS [Account#] FROM ((tsd_Claim LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID) INNER JOIN [qs_CallList1] ON tsd_Claim.clinsnum = [qs_CallList1].[Ins#]) INNER JOIN [qs_CallList2] ON (tsd_Claim.clpid = [qs_CallList2].[Account#]) AND (tsd_Claim.clinsnum = [qs_CallList2].[Ins#]) WHERE (((tsd_Claim.cloutstandingamt)>0.99)) GROUP BY tsd_Patient.PAGENT, tsd_Claim.clpid, [qs_CallList1].[Ins#], [qs_CallList1].OutstndByIns, [qs_CallList2].OutstndByAcct ORDER BY tsd_Patient.PAGENT, [qs_CallList1].OutstndByIns DESC , [qs_CallList2].OutstndByAcct DESC' EXECUTE(@SQL)
set @SQL = 'Create view qs_CallList3 as SELECT TOP 100 Percent tsd_Patient.PAGENT, tsd_Claim.clpid AS [Account#], tsd_Claim.clticketnum AS Claim, tsd_Claim.clfromdos AS DOS, tsd_Claim.cloutstandingamt AS Outstnd, tsd_Claim.clins AS Insurance, tsd_Claim.clinsph AS Phone, tsd_Patient.PGLNAME, tsd_Patient.PFNAME AS [First], tsd_Patient.PLNAME AS [Last], tsd_Patient.PSSN, tsd_Patient.PDOB, [qs_CallList1].OutstndByIns, [qs_CallList2].OutstndByAcct, [qs_SQLClaimStatusLastEntryCL].Pat
FROM (((tsd_Claim LEFT JOIN tsd_Patient ON tsd_Claim.clpid = tsd_Patient.PID)
INNER JOIN [qs_CallList1] ON tsd_Claim.clinsnum = [qs_CallList1].[Ins#]) INNER JOIN [qs_CallList2] ON (tsd_Claim.clpid = [qs_CallList2].[Account#]) AND (tsd_Claim.clinsnum = [qs_CallList2].[Ins#])) LEFT JOIN [qs_SQLClaimStatusLastEntryCL] ON (tsd_Claim.clnum = [qs_SQLClaimStatusLastEntryCL].Claim) AND (tsd_Claim.clpid = [qs_SQLClaimStatusLastEntryCL].Pat)
WHERE (((tsd_Claim.clfromdos)<GetDate()-' + cast(@mea as varchar(12)) + ') AND ((tsd_Claim.cloutstandingamt)>0.99) AND (([qs_SQLClaimStatusLastEntryCL].Pat) Is Null Or ([qs_SQLClaimStatusLastEntryCL].Pat)=''0''))
ORDER BY tsd_Patient.PAGENT, [qs_CallList1].OutstndByIns DESC , [qs_CallList2].OutstndByAcct DESC , tsd_Claim.clinsnum DESC, tsd_Claim.clpid, tsd_Claim.cloutstandingamt DESC' EXECUTE(@SQL)
set @SQL = 'update tsd_patient set tsd_patient.ppriority = priority.priority from priority where tsd_patient.pid= priority.[account#]' EXECUTE(@SQL)
I saw an article I believe on this site, now I can't find on HOW TO MOVE USERS AND ROLES FROM ONE SERVER TO ANOTHER. DOES ANYONE KNOWS WHAT IS THE BEST WAY TO DO that?
I'm new in the SQL Server Express Configuring Stuff and I've a problem or more exactly I don't have any idea about configuring an secure and hacking save user for a SQL Express DB.
What server roles/server permissions/endpoint permissions/database permissions are neccessary to have an rw access SQLE user? Any articles available? Any suggestions?
Scenario is a Personal Webpage for my Family & Friends located in my house
- ASP.Net Starter Website: Personal Website
- 2 MDF DB's: ASPNETDB.MDF & PERSONAL.MDF (included with the Starter Website)
- Development on DEV PC; Deploy on SERVER PC
HW: - SERVER PC with Win2k (fully patched) - DEV PC with Win XP Sp2 (fully patched) - LAN 100Mbit w NAT FW - WAN Cable Modem 128/1500
SW: - APACHE 2.0.55 with latest aspnet_mod - ASP.NET 2.0 - SQL Server Express - SQL Manager 2005 Lite - MS Web Developer Express
What I've done so far: - APACHE: configured & working - aspnet_mode: configured & working - SQLExpress: configured & working - SQLBrowser: configured & working - TCP/IP Protocol for SQLE: configured & working - Mixed authentication for SQLE: configured & working - ASPNETDB.MDF: attached, configured & working - PERSONAL.MDF: attached, configured & working - SQL Manager 2005 Lite: configured & working - SQLE users: created one for ASPNETDB and one for PERSONAL (with SQL Manager Lite; Sorry MS!!! your SQL Server Mgmt Studio Express sucks!) - WEBSITE: changed connection string from local to remote
(local doesn't work, because the LocalSystem User on Win2K, which is used by the SQLServer, has no User Profile. Also you cannot change the Service User, so it won't work with the local connection string, but with an attached and remote accessible DB it works just fine) - WEBSITE: runs locally and remotely on DEV PC with remote DB's and custom users.
But I'm concern about the two users I've created, to access the two DB's. I've no idea, what rights/roles aso they need to use the DB for remote (for development) and local (for production) access and to be secure?
I have several roles on a database and want to find out to which roles a certain user belongs to. What's the SP to get this information?
Background: I have a table where I store some application specific permissions for the different roles. I use the same grant/deny/revoke logic like in SQL server. These are permissions that cannot be mapped to a SP or a table. In a query I want to get all the permission records for the role that a user belongs to.
I am wondering is there anyway I can backup only Login name, Users and Roles, Not database, stored procedure, Views and Trigger. If some one can suggest there is a way that will be really great
What I'm looking for is a list of roles a particular user is a memberof.the closest I've found so far is sp_helprolemember without anyarguements. but this gives me all the roles and all the users. I wantthis same list filtered on a specific user.something like sp_??? 'user'
I have created a simple cube in BI Studio against an Oracle Relational Data Warehouse. In this cube I have created a perspective in which I have selected (included) only a small subset of measures and dimension. When I view this perspective on the Browse Tab of BI Studio, only the entities that were included in the perspective were available for report construction as was expected.
Next, I generated a model for this cube in Report Manager. Now here is the problem, I then opended the model in Report Builder and selected the same perspective from above, but all of the Entities in the cube were displayed including the fields I explicitely did not include in the perspective. I then looked at the .smdl file describing the Model and it looked like in the Perspective description section all of the cube's entities were included, even the ones that should not have been included. It seems as if the problem is ocurring durring the model generation. I also tried generating the model in Management Studio and it seems to be doing the same thing.
Any ideas on how to fix this? Could I be doing something wrong(probably)? I have to give a presentation soon and this is a big deal for the Project Stakeholders.
By the way, I am using the 180 Day Evaluation of Sql Server 2005 with SP2 CTP installed.
I would like to create the folders Sales, Orders and Credit under the Home folder. The Sales folder contains a set of reports that should be accessible to a group of users, similarly Orders folder contains a set of reports that should be accessible to a group of users. I do not have facility to create user groups in SQL Reporting Service. I create a user group in my machine (from Control Panel) but Reporting service is not able to view this group. I am able to add individual users to a folder, but I would like to check if I can create a group and then add users to this group.
I have created an web reference called ReportingSerivce 2005. i am trying to set an user to have browser rights without going into Report Manager.
But I can't get the SetPolicies function to work correctly. Any ideas?
Code written in VB.
Dim rs As ReportingService2005 rs.Url = "https://wa.hrconnect.treas.gov/reportserver/reportservice2005.asmx"
Dim Item As String = "/" Dim Policies() As Policy Policies(0) = New Policy Policies(0).GroupUserName = TxtUser.ToString Policies(0).Roles = New Role(0) {} Policies(0).Roles(0) = New Role Policies(0).Roles(0).Name = "Browser" Policies(0).Roles(0).Description = "May view folders and reports." rs.SetPolicies(Item, Policies)
