I have been doing maintenance on my sql server 7 databases, I have removed some users from the server login area for example user "EMS". I noticed that "EMS" is still in the 'public database role' for my database. How do I remove old unwanted members out the public database role? Are they perminantly stored there? Is there a way to clean them up?
Does anyone know why users would notbe listed while adding them to roles?
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...
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?
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?
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.
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.
On 11/10/01 I posted a question on how to move passwords from one sql2000 box to another. The response to this question worked perfectly.
I also need to copy over the user or database access information from one box to another. I tried to use dts but it only copied over the users and the database roles but did not copy over the Database user properties which allows the user access and places the user into security groups.
Any help would be appreciated.
Thanks again.
Steve
After "copying" a SQL Srv DB from one server to the next, we have found that the permissions within the developer-created database roles did not transfer. How can we insert these permissions without doing it manually? Or can we create some type of DTS package that will bring the roles' permissions for us? Or how do you move a SQL database from one server to the next and get everything within it to come along??
View 3 Replies View RelatedI'm looking for advice/caveats about how to convert/export information on AS database / cube roles. The reason why is because we have to move the server into different windows domain and all the roles reference windows accounts in the old domain. Using the MS SQL 2000 version.
Thanks in advance.
-Mike:confused:
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
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 RelatedHow can I get all database roles from a specific database?
View 4 Replies View RelatedWe 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 searched hi and low for hours within the product and online - I cannot find how to have SQL 2005 produce a script to automatically repeat the creation of a database role I have tedious manually created.
I have tried "script object as, create to,<>" - single-liners are produced - no details. More single line results from "<Database>, All Tasks, Generate Scripts".
Am I missing something or is this a bug?
€¦also to note after review blogs and other sources the use of the SQL2005 methods related to security and new schema objects - I feel€¦ dreadfully wrong - what is claimed feels like hype, unsupported by examples and usage cases feels like we better just make all automated processes sysadmin; I know learning curves can be strong but I am not this green or that stupid - where are the DOCS/training materials?
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!
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