We maintain a few applications that query multiple databases on our server. We also have groups of users that multitask using different applications.
I've always created a Role in each database for every application.
Wondering if someone is in two Roles at once will we have security conflicts?
I have an application that segregates data into two differentdatabases. Database A has stored procs that perform joins betweentables in database A and database B. I am thinking that I have reachedthe limits of Application Roles, but correct me if I am wrong.My application creates a connection to database A as 'testuser' withread only access, then executes sp_setapprole to gain read writepermissions. Even then the only way 'testuser' can get data out of thedatabases is via stored procs or views, no access to tables directly.Anyone know of a solution? Here is the error I get:Server: Msg 916, Level 14, State 1, Procedure pr_GetLocationInfo, Line38Server user 'testuser' is not a valid user in database 'DatabaseB'The system user is in fact in database A and B.thanksJason Schaitel
View 4 Replies View Relatedi know that db.owner.tablename works in the query analyzer, but what i really appreciate from anyone is how to apply this in vb6 code since the recordset is opened only from one db using the following syntax:
rs.open "select ...", dbname, ..., adopendynamic, adlockoptimistic ...
thanks
This may be a newbie question but...
Say for example DatabaseA has failed over to the mirror. Then you call a stored procedure in that database that queries a table on DatabaseB that is on the principle (has not failed). What happens? Is mirroring smart enough to know that? If not, are there workarounds? Perphaps manually failing over the other databases?
Thanks
Hi all,
Could you please suggest workaround for systems that have cross database queries and want to use mirroring.
Thanks,
Avi
Can you write a stored procedure to add a user to your DB and set the roles the user belongs to?
I want to write a stored proc. to add users and set roles so it can be used in code instead of doing it manually.
After the user has been added and their roles set, can you write another stored proc. to give you what roles they belong to?
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
unless I am doing something wrong
Please help
Thanks
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
Any ideas? Thanks!
Hello,
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.
Happy coding...
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?
View 3 Replies View RelatedAfter 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?
Fazlul Haq writes "i m new to sql server2000.my question is
" I CREATE A TABLE IN WHICH THREE COLUMS EXIST i.e SNO(NUMERIC),NAME(CHAR),FNAME(CHAR).I WANT TO ENTER VALUE IN SNO FIELD STARTING FROM ZERO(0)e.g 011,021,022 etc and when enter value without starting from zero an error messag is appered on the screen.I WANT THAT THIS CAN BE DONE BY CREATING A "FUNCTION" OR "RULE" OR "STORED PROCEDURE". PLZ HELP ME ""
Hi
I have a question about user define function in sql.
How can I use Exec in UDF?
What I mean is that I made a string in a UDF, And I need to
execute the string in function. but I think it's illegal.
so tell me how can I use it?
P.S : forget about useing stored procedure instead of function.
thanx.
This is driving me crazy! The SQL Statement refenced is shown at the end of this email.
When I try and run the statement, an error is raised saying that Undrawn_GT5MIL_LE365Days is invalid (likewise for Undrawn_LE5MIL_LE365Days). From what I can gather, it is saying that I cannot include a User Defined variable in another argument. This is unlike Access. Any suggestions?
SQL View.......
SELECT TOP 100 PERCENT QRY_FacNew_Term.Category, QRY_FacNew_Term.Fac_No, QRY_FacNew_Term.Client_Number, QRY_FacNew_Term.Client_Name,
Undrawn_GT5MIL_LE365Days = CASE WHEN Undrawn_CDN >= 5000000 AND Term <= 365 THEN Undrawn_CDN ELSE 0 END,
Undrawn_GT5MIL_GT365Days = CASE WHEN (Undrawn_CDN >= 5000000 AND Term > 365) OR
(Cr_Limit_CDN IN (0, 1)) THEN Undrawn_CDN ELSE 0 END, [Undrawn_GT5MIL_LE365Days]+[Undrawn_GT5MIL_GT365Days] AS Total
FROM dbo.QRY_FacNew_Term
WHERE (Exclude <> 'Y')
ORDER BY Category, Client_Name
Hi everyone,
How to define a Input variable in a Execute Sql Task??
I've defined a User::Inicio variable which contains 4 as value.
In Parameter Mappins it has been defined. Then, I've gone to General->Sql Statement and allocated the following SQL Statement:
UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [Inicio]
or
UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [User::Inicio]
Anyway, I'm stuck, both did not work
Thanks in advance for your comments
How to find the list of queries currently running in the Database with User Login Information.
Since my database application is running slow, to find the slow queries.
Hi all,
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)
AS
DECLARE @Msg varchar(255)
declare @SQL varchar(4000)
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?
View 3 Replies View RelatedHi,
Can you please tell how to backup the users and their access permission from a database before restoring it.
Balavenkatesh.
Hi, SQL experts.
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?
Thanks for any help, ideas and suggestions.
Alex
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.
Thomas Schoch
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
Thanks in advance.
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'
View 2 Replies View RelatedHi,
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.
Any help in how to resolve this issue.
Cheers,
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)
I have some confusion on crossjoin function within MDx.while I try to crossjoin the different level sets of same Hierarchy. It shows error as
For example.
‘The Customer Geography hierarchy is used more than once in the Crossjoin function.’
select {
{[Customer].[Customer Geography].[Country].&[United States]}*
{[Customer].[Customer Geography].[State-Province].members}}
on 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
Cannot we Cross joins across user defined hierarchies ,or they aren't supported .?Coz I really need to implement as above MDx within my real Cube.I try to implement by making as another Hierarchy Member but it doesn’t gives the value result as what we want/need.with
member [Customer].[Country].[United States ]as [Customer].[Customer Geography].[Country].&[United States]
select {
{[Customer].[Country].[United States ]}*
{[Customer].[Customer Geography].[State-Province].members}}
on 0
FROM [Adventure Works]
WHERE Measures.[Internet Sales Amount]
What are the differences between the database permissions, which can be granted in the database properties permissions tab (create table, create procedure etc.) and the predefined role db_ddladmin? It seems that the database properties permissions tab includes more permissions than the predefined role db_ddladmin.
Does anybody know the difference in terms of permission?
Apologies if my post does not fit into this forum. I initially tried the SQL Server Data Access forum but I now think my question is more security related.
Is it possible for a web user who has been successfully authenticated with forms authentication to be authorised to use a SQL Server 2000 role depending on a particular ASP.NET 2.0 role that they have been authorised to use? I understand that that I can assign a SQL Server 2000 role to the ASPNET or NETWORK SERVICE account but this will grant access to anonymous web users to the database role. I can ensure that I only call stored procedures which access sensitive data in web pages that are in restricted by ASP.NET roles. However, it would be nice to also restrict stored procedures via the ASP.NET 2.0 Forms Authentication roles.
If this is not possible have you got any bright ideas how I could restrict access to stored procedures who are anonymous web users.
Many thanks,
Mark
I'm developing an ASP.NET2.0 application which accesses a SQL Server 2005 Express database. I plan to use integrated security for access to the database.
I'm confused about the relationships between Windows groups, the ASP.NET web.config file <allow roles=.../> and SQL Server roles.
I would like to create a Windows group to which I can assign multiple users and grant that group access to a Web Site using windows authentication and also grant that windows group access to the database my web application uses.
I have gotten the combination of Windows Authentication to the web site and to the database to work for a specific windows user but I am having trouble determining the combination of database security entities I must create to allow access to my database by members of the windows group.
For a Windows user:
1. Create Windows user
In SQL Express
2. CREATE LOGIN FROM WINDOWS WITH DEFAULT_DATABASE =
3. CREATE USER FOR LOGIN
4. CREATE ROLE
5. EXEC sp_addrolemember <role-name> <user-name>
For a Windows group, what would be the equivalent commands necessary to grant a windows group access to my database? Specifying the Windows Group name in sp_addrolemember does not appear to be sufficient even though the documentation states that a windows group name is a valid value for the member name argument.
Using SQL Server Express 2005, I have two databases. AppDB - The main application database.GeoDB - A somewhat static ZIP code / states / other geographic stuff databaseI need to have some foreign key columns in tables in AppDB reference columns in the GeoDB database tables. Eventually other application database besides AppDB will be doing the same thing in our infrastructure. After googling and reading for days, here is what I
think I know:You cannot create foreign keys that reference tables in another database in SQL Server.You
cannot create foreign keys that reference columns in a view, and you definitely cannot make an index on a view that has base tables in another database.You can create a trigger that references tables in another database, but this can be flaky? (nested/recursive trigger problem).SQLServer
2005 supports multiple schemas within the same database. Maybe I should logically separate my databases this way? Seems like it would be a tough solution to manage since I already have some databases live in production that will eventually use this 'static' GeoDB. Also, seems like it
wouldn't be as portable as keeping the GeoDB info in its own database,
but maybe I'm being too software engineer-ish here - afraid of low
cohesion, high coupling.I will greatly appreciate any advice I can get, or any more options I am missing. Thanks,Adam Nofsingerucnmedia.com
I am not sure if this is the right place for this question or not but here it goes...
I want to add a new user to the database with read-only rights. I know public is selected by default and can not be changed. I also added the user to db_datareader and db_denydatawriter. But just as I was about to save I noticed Read Only. I have done some searching but have not found anything about this role. I assume it does just what it says but I am confused as to why it would be there when I can set the other settings I just did. Is there any benefit to using this role instead of the ones I chose? Should I add this role in addition to the two I have already picked? Any help is greatly appreciated.
thank you,
Kevin
Hi All
I have the following questions regrading T-SQL
1. How to assign database role "db_owner" to model database using T-SQL?
2. How to grant a window login public access to master database
Thanks.