I wish to create a user that can backup any or all databases in our SQL
Server 2000 Instance. I thought there would be a server role for this
function, however I can only find that after I grant access of a
database to the user, then I can choose ds_backupoperator.
I want to create a user that will have the ability to backup all the
databases. I dont wish to have to come back to the server after a new
table is created and add the backup user to that table.
I want SA w/o the full privilage...am I crazy?
Any Suggestions?
TIA
Rob
Backgroup: We currently have about 10 SQL servers, and adding more in
the future. I am using SQLBackup from Idera along with HP Surestore
Tape library (60 slots,2- DLT8000 drives with 40/80 GB capacity) with
ArcServe from Computer Associates. I want to have this automated to
backup to file then tape, regardless of what databases get created.
I have a user with DBCREATOR Server role only. That user is able to create database but create table permission denied. how would I set permission on this user, so that this user can create databases and automatically becomes the DBOWNER of that database and can do any action on that database.
I would like to determine if a particular user has sysadmin serverrole. Is there a way to do this via the connection string? Currentlyour code checks if a login is valid using SQLDriverConnect, however weneed to be certain that the user can login and modify the schema.Is it possible to fetch a user's server role to determine if it has asysadmin server role?
SQL Server 7 i did a restore of a database, then tried to add the User login to it, but when i select database access, i get the followinf error :- "Error 15023, user or role already exists !
the user did exist on the Database, but when i select Database,Users or Database,Roles the User doesn't exist !! so i can't drop it !
I had thought that I had posted this yesterday but since I cannot findit here we go again.I have searched the archives but either the code I want is not postedor I missed it.I have a user create role in a database that I need to reproduce inanother version of the database. Does anyone out there have the SQLor T-SQL necessary to generate all the grants to a role?-- Mark D Powell --
I am having a problem with creating databases in MS SQL 2005 Server. When I attempt to assign an User Mapping for my SQL user account to the newly created database, the "dbo" user and schema is already assigned. When I try to drop this user in favor of my own, I receive an error message: Cannot alter the user "dbo" (Microsoft SQL Server, Error: 15150). I am connected to my database engine through the "sa" account.
An old website I inherited uses sa to connect to SQL SessionState and had the details in the web.config. This is bad for security.The session state database is of -sstype "t" which is defined as:Temporary. Session state data is stored in the SQL Server tempdb database. Stored procedures for managing session state are installed in the SQL Server ASPState database. Data is not persisted if you restart SQL. This is the default.What kind of WIndows user, SQL Login, role and permissions do I need to create to make Session State secure? (Windows Server 2012 and SQL Server 2012 mixed mode authentication, Webfarm).
We have been working on an application that will be using a forms-authenticated report server (RS2005) as a reporting back-end. Using the reporting services web service I have been able to assign permissions to objects in reporting services no problem. The issue is that each user needs to be added to the System User role to be able to use the report builder properly. I can't seem to find a way to do this programmatically. Any idea?
I try to add a db_role or a user to all my databases with one script. Although parsing doesn't report any problem I get a Syntax error during execution. I first select the database names into a #temp table which has two columns, ID and dbname. After that I use the following code:
The end users are using VB Applications, there they will be entering datas. Those datas will be stored in the SMS Database.
My Problem is through which Roles (Fixed Server Roles or Database Roles) I should attach these end users. If it is a Fixed Server Roles, Other than sysadmin role in which role I should attach this end user. Like that other than DB_Owner in which role I should attach these end users to the Database Roles.
NOTE: I am talking about roles in my sql server - NOT in asp.net. I need to create a stored procedure that retrieves the roles that the currently logged in sql user has for a different database. I have the code that gets the roles for the user, but it only works if the user is in the database. I want to be in one database, and get the roles for a different database. I have tried using USE DATABASE, but this is not allowed in a store procedure.
Hi, I have got a problem. When I try to access my database table Users, I get the following error:
SELECT permission denied on object 'Users', database 'Users', owner 'dbo'.
So I tried to grand this select command in MS Web Data Administration, but it doesnt work. When I try to grand db_datareader role to dbo, I get the following error
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use the reserved user or role name 'db_datareader'.
Does someone have an idea where could be a problem?
I'm rather new to the MS SQL Server development in general and especially to its data security architecture and features - I'd like to know if it is possible for end-user to retrieve/update(!?) the data using a SP which executes on a table for which she/he doesn't have any privileges.
Is there a System stored procedure that gives me the Role in which a user is in. For example I execute this procedure, give the user as parameter an that gives me back the Role the user is in. It has to be said that this is a user defined role, I got three of them, HR, Employee, Approver.
I have a user in SQL Server 2000 with public, datareader and datawriter roles on several databases. I need to select all those databases, how can I do that. I have tried sp_databases but I get ALL databases. I also tried sp_MShasdbaccess but I still get all databases.
I am trying to export all of the user and role permissions out of several databases for auditing purposes. I see the Users and Roles listed under the Security tree view when I log into the database, but I do not see an option to export or query the permissions. In addition, we do not have any tables that reference user permissions in our databases. So, how would one go about exporting or querying this information?
I've seen similar topics where they recommend querying sys tables to gather the info, but I don't see those tables either. Any help would be greatly appreciated.
All my thanks!
- Isaac
Edit: I should add in that I am connecting to 7 and 2k DBs using 2k5 SMS. Not sure if that makes a difference...
Hi, I hv an application which is using ASP.net. The connectionstring in web.config is <appSettings> <add key = "constring" value = "Initial Catalog=mydatabase;Data Source=mypc-pc;User ID=User1; Password=password1"/> </appSettings>"
Then, i hv created a user in SQL Server 2000 which is User1. What should i put for the database role? db_owner or just db_datareader and db_datawriter?
Can anybody explain what database user with a prefix of "" in the public role indicates. I have the same two users in every database and cannot remove them?
Hi! I've restored a backup from server A in server B. In server A I used to have a db_owner, called sitebase. After restoring the backup in server B, I created the user sitebase again and tried to grant him db_owner through Server Manager but I got the error: user or role already exists in the current database. How can I avoid this situation and grant db_owner to him? Thanks, Fábio
I get the following errors associated with trying to create an SP.
Server: Msg 170, Level 15, State 1, Procedure AddFortuneUser, Line 8 Line 8: Incorrect syntax near '@newuser'. Server: Msg 137, Level 15, State 2, Line 1 Must declare the variable '@newuser'.
Can anyone explain why I have to do a declare. I suspect I have to issue "declare @newuser sysname" somewhere but I'm not sure why.
The following is the code I'm trying to run. My intent would be to create a form for the Admin Clerk that would call this SP. That way they can create a generic login. They have an application that allows them to change the password after the fact. /* Created for Admin person to allow them to add a basic SQL Login Account forcing the user to be a member of a specific role 'helmsman' in a specific database 'Fortune' */ CREATE PROCEDURE AddFortuneUser @newuser char(128) AS EXEC master..sp_addlogin @loginame=@newuser, @passwd =substring(@newuser,1,8), @defdb =Fortune GO if not exists (select * from dbo.sysusers where name = @newuser and uid < 16382) EXEC sp_grantdbaccess @loginame=@newuser, @name_in_db=@newuser GO exec master..sp_addrolemember @rolename ='helmsman', @membername =@newuser GO
I need a stored procedure to find out if the current user is a member of a certain role. I want to pass the role name and return a bit to tell whether he is a member or not. I have this code to list the groups the current user is a member of but I don't know how to search or do a "SELECT FROM" on the results.DECLARE @usr varchar(32) SET @usr = USER EXEC sp_helpuser @usr But if the current user is a member of more than one role it returns multiple rows. So if I could do something like: DECLARE @grpName varchar(32) SELECT * FROM (EXEC sp_helpuser @usr) WHERE GroupName=@grpNameIF rowcount > 0 THENRETURN 1ELSERETURN 0END IF I know that doesn't work so how can I do this?
The role I have added to the database is an Application Role. It has been added to the Database permissions with Grant checked for "Select" and "Authenticate".
If I test this with query analyzer, it returns expected results (if I remove Grant from 'Select', it fails)
sp_setapprole 'SearchAppRole', 'password'
select * from recipe
If I edit my connection string (for testing purposes) to use the sa account, the application can connect and run the Select statement:
However, I cannot get the application to successfully logon and run the select statement when using the user id and password of the Application Role. I get error:
System.Data.SqlClient.SqlException: Login failed for user 'SearchAppRole'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj
I can't find much information on Application Role...I just want one basic permission for the application as a whole. Any help is appreciated. Thanks.
How do I get a particular user to be a choice under the db_owner role for a particular database? The user is listed under logins and even shows to be the db_owner for the database under the database access tab of the login properties. This is SQL 2000. Thanks, David P.
I have created reports using SSRS 2005 and deployed in ReportServer.
I calling these reports form my web application.Users have different roles based on their login into web application.I need check these user roles and display the reports based on their roles. There could be 10 reports in total, but for this user i should display only 4 out of them. Its pretty urgent. Can somebhelp on this?
in SQL server 2005, Database User's permission will be overriden by the database Role's permission or ottherwise? For example, a userA is owner of table AA so it has all permisions on table AA but the user is a member of GroupB but group B has no permission to access to Table AA. What happen on User A?. has it permission to access to table BB or not? How can I find document or example about this? Please help me, thanks so much
I'd appreciate your thoughts on this. Not done too much DB admin. Usually I do development work and the admins to the admin.
The database is behind an API of stored procedures to manipulate the data, and views to select the data.
The database needs to be accessed remotely by multiple clients.
How best to keep the database secure?
Create a new user and login on the database which is made known to all client applications. Then grant execute permission on the stored procs and grant select on the views?
There is probably a better way than one login for all? Should I be looking at roles and groups etc? If so, how best to set that up?
to determine if a user is a member of a predetermined role, but how do I ask what the role name of the querying user is instead? Basically I want to do something like:
SELECT * FROM CityTable WHERE City IN (SELECT City FROM CitySecurity WHERE SelectGroup = ROLE_NAME)
I'm doing this because the "CitySecurity" table contains security information from another system that has been imported into SQL Server, where the 'SelectGroup', as part of process, will be named the same as the SQL Server role.
I am trying to add Login user 'NT AUTHORITYNetwork Service' to role 'aspnet_Membership_FullAccess' using the following statements. I have attached database ASPNET.mdf to SSMS. -- Create a SQL Server login for the Network Service accountsp_grantlogin 'NT AUTHORITYNetwork Service'-- Grant the login access to the membership databaseUSE aspnetdbGOsp_grantdbaccess 'NT AUTHORITYNetwork Service', 'Network Service'-- Add user to database roleUSE aspnetdbGOsp_addrolemember 'aspnet_Membership_FullAccess', 'Network Service' I am getting the following error message Msg 911, Level 16, State 1, Line 5 Could not locate entry in sysdatabases for database 'ASPNETDB'. No entry found with that name. Make sure that the name is entered correctly. Msg 911, Level 16, State 1, Line 4 Could not locate entry in sysdatabases for database 'ASPNETDB'. No entry found with that name. Make sure that the name is entered correctly. Msg 15014, Level 16, State 1, Procedure sp_addrolemember, Line 36 The role 'aspnet_Membership_FullAccess' does not exist in the current database.
On displaying the contents of view sys.databases, I am able to see row for ASPNET. Please let me know what the problem is and how I could fix it. Thanks, Anita
