Nested Roles
Mar 20, 2002Guys,
Has anybody out there successfully nested roles?
SQL Server seems to let you do it, but then no permissions are carried up the hierarchy.
Regards,
ChrisH
ADVERTISEMENT
Schemas, Users, Logins, Database Roles, Application Roles
Mar 5, 2006Hello,
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...
Add User/Set Roles In Code And Read Roles
Jan 28, 2004Can 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?
SQL Server Roles && ASP.NET 2.0 Forms Authentication Roles
Jun 16, 2006Apologies 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
SQL Server Roles, Windows Groups && ASP.NET Allow Roles
May 6, 2007I'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.
Fixed Database Roles Vs Application Roles
Aug 24, 2006After 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?
Oracle Predefined Roles Vs Ms Sql Roles
May 3, 2005Hi! Can anyone say which ms sql server predefined roles are similar to the following oracle predefined roles: dba, connect, resource. I already know that sysadmin in MS SQL Server is the same as DBA in Oracle but what about the rest?
Thanks a lot.
How To Decide On Server Roles And DB Roles
Mar 2, 2006I am in the process of locking down the SQL Server in an environment that is considered to be in production (pilot stages) and there is no staging or test environment that mirrors it. I need assistance in determining the server and database roles to assign to existing logins, most of which currently have sa and dbowner rights. Because it is not a development environment, I need to be sure that downgrading the server and/or database level permissions will not break any functionality.
I'm starting with the logins that have the SA fixed server role. These logins need to be able to install applications that require the use of a backend database, which will be stored on SQL Server. In addition, through the installation process a new login/password for the newly created database(s) is normally created. For the existing logins with the SA fixed server role, will downgrading to the securityadmin and dbcreator roles be sufficient to facilitate those needs, or are those too much/ too little? And should any user account ever be granted the SA role? If so, what questions could I ask to determine this need?
Since these install process for these applications usually prompt to install using SA or local system account to authenticate to SQL to create the new database(s), that account should have securityadmin and dbcreator roles to create the database and its tables, as well as add a new login to that database.
Please address this question, keeping in mind that the logins will only be performing the described actions, installing apps using SQL Server as the backend database and adding a login to that database (which may or may not be done during the installation process).
Thank you,
nu_dba
Db Roles / Server Roles
Apr 15, 2007Hi,
I'm looking for some guidance/help regarding setting up a sa - lite account in SQL 2005. I need to give another admin rights to create/monitor maintenance plans, backup and restore databases, monitor performance/logins, but NOT be able to have any rights on several tables (and of course not being able to set user permissions).
I've tried using server and db roles but haven't been able to determine how to give someone w/o full sa rights access to maintenance plans.
If you can think of soemthing, please let m eknow.
Jenn
Assigning Roles To Roles
Mar 10, 2004I have MS SQL Server 2000 DB.
I have created a User and created some tables for the same.
I created a Role named A and granted Select Permissions for few tables to that roles.
When I created another Role named B and added this role (A) to B, the permissions are not being xferred to B. Bcos of which, if i assign an User to Role B, he is not able to select the tables for which permissions have been given thru role A.
Note : If i give assign directly the user to Role A, it is working. But i want to assign User to role A only thru B.
Roles
Sep 21, 2001I need to grant select/viewing on a information_schema for a programmer. how do I grant this without granting server role "System Administrators".
Thanks,
Jason
Roles
Sep 21, 2001I need to grant select/viewing on a information_schema for a programmer. how do I grant this without granting server role "System Administrators".
Thanks,
Jason
Roles
Oct 25, 2000Does anybody know how to set up a role that can only set up jobs in Sql7.0.
TIA - Philip
Roles
Jun 10, 1999I might be missing something. I have 'upsized' an Access database to SQL 7.0. I then created new users on the server. I then added them to the database and gave them the role db_datawriter. When they try to connect, they can't. When I look at the permissions tab for the tables, I see their ID's, but none of the boxes are marked. Did I forget to do something?
View 3 Replies View RelatedDB Roles
Apr 5, 2001Is there a way in 7.0 to allow users with the "Public" role truncate tables without giving them sysadm rights?
Thanks,
Kevin
DB Roles
Sep 28, 2004I am creating a new user. I would like to give read only access just for the tables in a database. I had assigned only public and db_Datareader roles to this user. With these roles the user could able to see the script of the SPs and also the DTS packages. Also with the above roles the user could able to create new DTS packages and SPs. Is it possible to deny the user to look at the sps and ability to open the DTS packages created by some other users.
What I need to do is create a role with just table data read access so that they could just select the data only nothing more than that.
Also another role with dataread and ability to create the DTS packages from other servers by accessing this data. Anotherthing we need is With this role the users could create Database schema.
This is an urgent request. Please advise me ASAP.
Thanks
Roles
May 9, 2008Hi,can anybody tell me the script for how to find out for a particular login n for a particular database ,what are the database roles they have??
View 10 Replies View RelatedRoles
Jan 19, 2007Hi,
I have added two roles to sql server. One called Officeusers. The other AdminUsers
Added the appropriate logins to these roles. For example; james, john, ahmad to OfficeUsers and Mat, Nick to AdminUsers.
How can these roles be now used in a connectionstring? I can use each user login and his relevant password in a connectionstring to connect to sql server but not sure how/where/when the Roles come in to development.
i.e. do I need to use the role in the connectionstring? if so then what happens to the password.
Not quite clear about all these.
Thanks
Roles
Jul 5, 2007Hi all,
I have developed a website that and configured a role for my users. I also want to write a windows application, but how can I let my windows application use the role based I have on the same database where the website runs? Thanks
Using Members And Roles With ONE Db
Jan 8, 2007i'm not sure to put this in data or security, so i'll put it in both and put on my flame suit.....I'd like to setup the security to use the one single DB that i've setup to use for my inventory, instead of the ASPNETDB.MDF that accompanies the normal setup.If i need to include more info, please ask.
View 1 Replies View RelatedLogin With Different Roles
Nov 26, 2007Hello readers
I have a problem with the following:
I made a login page in Visual Web Developer 2005 and I used the ASP.net Configationtool in the Website menu. Made some couple test accounts to login and made a role: "Beheerders". Made 1 of my test accounts a "Administrators".
So my problem is when I log in with the account with the "Beheerders" role, i go to the page of the "normal user". I tried to look in many sites, I could find some nice aid but not 100% the one that could solve my problem.
This is my code i programmed in the seperatefile of Login.aspx
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports LoginPartial Class Login
Inherits System.Web.UI.PageProtected Sub Login1_LoggedIn(ByVal sender As Object, ByVal e As System.EventArgs) Handles Login1.LoggedIn
Dim userinfo As MembershipUser = Membership.GetUser(Login1.UserName)
Dim UserRol() As String
UserRol = Roles.GetRolesForUser(userinfo.UserName)
If (Roles.IsUserInRole("Beheerders") = False) ThenResponse.Redirect("~Index.aspx")
End If
End SubProtected Sub Login1_LoggedIn()
End SubProtected Sub Login1_LoginError(ByVal sender As Object, ByVal e As System.EventArgs) Handles Login1.LoginError
'Parameters instellen voor InvalidCredentialsLogDataSourceInvalidCredentialsLogDataSource.InsertParameters("ApplicationName").DefaultValue = Membership.ApplicationName
InvalidCredentialsLogDataSource.InsertParameters("UserName").DefaultValue = Login1.UserNameInvalidCredentialsLogDataSource.InsertParameters("IPAddress").DefaultValue = Request.UserHostAddress
InvalidCredentialsLogDataSource.InsertParameters("Password").DefaultValue = Login1.Password
'Er was een probleem bij het aanmelden
'Nakijken of gebruiker bestaat in de databankDim userInfo As MembershipUser = Membership.GetUser(Login1.UserName)
If userInfo Is Nothing Then
'Ongeldige gebruikersnaam...
LoginErrorDetails.Text = "Geen gebruiker met naam """ & Login1.UserName & """ in de databank !"
Else
'Nakijken of gebruiker Lockedout of Approved is
If Not userInfo.IsApproved Then
LoginErrorDetails.Text = "Uw account is nog niet goedgekeurd!"
ElseIf userInfo.IsLockedOut Then
LoginErrorDetails.Text = "Uw account is geblokkeerd wegens te veel mislukte aanmeldpogingen!"
Else
'Het wachtwoord was verkeerd
LoginErrorDetails.Text = "Verkeerd wachtwoord - """ & Login1.Password & """"
End If
End If
'Record wegschrijven naar datasource
InvalidCredentialsLogDataSource.Insert()End Sub
End Class
Thanks for your help.
Regardings
Griffin1987
SQL Server Roles
Jul 13, 2004Hi all,
I am facing some trouble in my asp.net application. We have decided to add some more security at the DB. Every user gets his own login in SQL-server. (I know for connection-pooling it is better to use the exact same connectionstring, but security is the most important fact in our project).
What I want to do is add sql-server roles to new created sql-server users. I can create sql-server users from my code and I can GRANT or DENY rigths to a specific table, but I don't know how to give a user a role.
Any ideas?
Thx,
BKT
Using SQL Application Roles From ASP.NET
Nov 1, 2004Hi there
Can anyone point me to a good tutorial or give me a run down on using Sql Application roles from my asp.net application. Books Online only give a run down on how to set it up - not how to implement or use it from my code. MSDN no help. Google - same thing.
Any help greatly appreciated.
Thanks in advance
Roles Still Needed
May 25, 2000If you have an all MS NT shop and can use NT Groups as logins, wouldn't it be just as easy to just assign groups as users in a DB and assign permissions directly to the group?
We have a combo of both roles and nt groups as db users and they both work. Does anyone see a special need for the roles anymore
Managing Job Roles?
Feb 7, 2001Does anyone know if it's possible to grant a user the ability to manage jobs in server agent besides giving them SA rights? I none of the server roles beside SA seem to be able to work.
Thanks
ROLES Vs Alias
Feb 16, 1999I am just getting started with SQL Sever 7.0, most things are must easier and simpler.
I do have one mind set problem
With SQL 6.5 I created a logon(JAWS) , aliased the developers to the logon(JAWS)
and all the tables and other objects were owned by ((JAWS). The developers would then grant
access to the tables. And the users would open JAWS.tablename.
In the SQL 7.0 test are I take an NT group that have the devlopers in them and allow SQL SErver
logon for that group. Then I allow the group access to the database and give the group
database roles of db_owner, db_ddladmin, db_datareader and db_datawriter.
The developers can create objects but the objects are not owned by the role but are
owned by the indivudual NT accounts that are in the NT group.
Any hint of what to change to have the ownership show up as the NT group??
App Roles Connecting To More Than 1 Db
Jul 1, 2004Is a connection utilizing an application role limited to connecting to one database at a time. For example, my approle A lets me talk to database test. But from test I need to exec an sp which is on database orders?
View 1 Replies View RelatedRoles/users
Oct 4, 2004Is there a script or can I make a script to find out what roles and what users are assigned to the roles are?
View 4 Replies View RelatedRestore And Roles
May 14, 1999Hi!
I have a doubt. I hope can you help me.
I use Microsoft SQL Server 6.5 . Can I assign roles like in Sybase? How? According to documents, there is a stored procedure (sp_addrole) permit this. But I donīt find it. Exist other way? I wish create a login with role "operator" that just backup and restore.
Beforehand, thanks.
NT Authentication, Roles With SQL 7
Jul 2, 2001Our group develops in-house VB applications that access a SQL 7.0 Database. We are trying to set up a security model that looks something like this:
1) Employees are organized based on the required level of database access (View, User, Admin).
2) Appropriately named Global Groups are set up in NT (example sqlView, sqlUser, sqlAdmin) and employees are added to these groups.
3) A Login/user is created in the SQL database for each of these NT groups.
4) Roles are created -- named for each security level available to a specific application (example TimeCardView. TimeCardUser, TimeCardAdmin).
5) Appropriate NT Groups (now users) are added as members of these roles.
5) Roles are then given execute permission for needed stored procedures (example: TimeCardAdmin can execute the "sp_DeleteTimeCard" but TimeCardView cannot).
6) When a user launches a VB application, their "membership" in a NT group is first established and then the Application logs onto the Database using a "TrustedConnection".
The Problem: It appears that once a trusted connection is established, a user can access a stored procedure even if execute permission has not been given to the role to which their NT group belongs.
The Question: How can we set up our security so that users (as members of NT groups) can logon to the database and gain access (or be denied access) to stored procedures?
SQL Server Roles
Jun 18, 2004Hi,
I want a user login to have permissions to view sql server jobs that they are not the owner of without giving them system administrator permissions,
Is there any way to do this?
User Roles
Oct 20, 2005Hi 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.
Roles-security
Jan 19, 2007Hi,
I have added two roles to sql server. One called Officeusers. The other AdminUsers
Added the appropriate logins to these roles. For example; james, john, ahmad to OfficeUsers and Mat, Nick to AdminUsers.
How can these roles be now used in a connectionstring? I can use each user login and his relevant password in a connectionstring to connect to sql server but not sure how/where/when the Roles come in to development.
i.e. do I need to use the role in the connectionstring? if so then what happens to the password.
Not quite clear about all these.
Thanks