If 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
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...
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?
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.
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?
Hi! 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.
I 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
Hi,
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
I 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.
I 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
I 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
Does anybody know how to set up a role that can only set up jobs in Sql7.0.
TIA - Philip
I 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 RelatedIs there a way in 7.0 to allow users with the "Public" role truncate tables without giving them sysadm rights?
Thanks,
Kevin
I 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
Hi,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 RelatedHi,
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
Hi 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
i'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 RelatedHello 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
Hi 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
Hi 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
Guys,
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
Does 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
I 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??
Is 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 RelatedIs 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 RelatedHi!
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.
Our 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?
Hi,
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?
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.
Hi,
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